Hello,
I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!
I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:
I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example: If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.
Can anyone help me get started in tackling this problem or provide any examples?
Code: CREATE TABLE [dbo].[DigiTracker]( [DigitrackID] [int] IDENTITY(1,1) NOT NULL, [contact_id] [int] NOT NULL,
[Code]....
-- not accurate b/c it returns 0 for all TimePerPageSec SELECT DATEDIFF(second,min(createDt),max(createDt)) AS TimePerPageSec FROM DigiTracker GROUP BY contact_id, ZinePageNumber time spent (in seconds) between each record
Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process. I have a table that consists of: Order#, Processes, Time_In, Time_Out. Order# can be 1, 2, 3, 4, 5. While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping. Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process. For each process we will have recorded time when the order# comes in and when it finishes with each process. I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.
Hi,I have a table called Bookings which has two important columns;Booking_Start_Time and Booking_End_Time. These columns are both of typeDATETIME. Given any day how can I calculate how many hours are availablebetween the hours of 09.00 and 17.30 so a user can see at a glance how manyhours they have unbooked on a particular day (i.e. 8.5 hours less the timeof any bookings on that day), can this be done with a queryor do I have to work it out in my code?Thanks for your help
Hi guys, I am having difficulty calculating the time duration between receiving process to shipping process. I have a table that consists of: Order#, Processes, Time_In, Time_Out. Order# can be 1, 2, 3, 4, 5. While at the same time Order# 1 can go through more than one process, i.e.: Receiving, VisualTest, MechanicalTest, ..., Shipping. Every Order# does not necessarily goes through all processes, but surely they will go through receiving process and shipping process. For each process we will have recorded time when the order# comes in and when it finishes with each process. I need to calculate the length of time from Time_In from Receiving to Time_Out in Shipping.
Thanks for setting up such a great site and forum.
Here is my problem:
I have a table like the following in SQL Server 2005:
order | taskid | main_person | temp_person | start_assign_date 1 | 3 | John | John | 2008-01-01 10:20:22 2 | 3 | John | Joe | 2008-02-05 15:20:22 3 | 3 | John | John | 2008-02-07 20:25:20 4 | 6 | Joe | Joe | 2008-01-01 10:20:22 5 | 6 | Joe | Mike | 2008-02-01 10:20:22 6 | 10 | Doug | Doug | 2008-01-01 10:20:22 7 | 7 | Russ | Russ | 2008-02-01 11:20:22 8 | 7 | Russ | Mike | 2008-02-08 12:20:22 9 | 7 | Russ | John | 2008-02-10 20:05:12
It was made to record who was in charge of a specific task at a specific time. Each task has its own main responsible person and some substitutes for that person as Temporary Persons (who did the task while main person was away). The Main Person's name is in the temp_person column when he is doing the task by himself.
I'd like to generate a report that shows: - in a specific time period - which persons were in charge of a specific task and - for how long
Something like this:
From 2008-##-## to 2008-##-## Task 3 - John - 15 days Task 3 - Joe - 5 days Task 6 - Joe - 18 days Task 6 - Mike - 2 days Task 10 - Doug - 20 days
I have some ideas to do that when there are both start and end dates for every record but I couldn't find a way to use the next assignment start date for each task, as the end date for its previous record (in that task group) to calculate the duration for that record.
I can group the tasks and users and put them in the chronological order but I can't indicate the next start date as the end date for the previous record (in specific task group) to use the date difference functions.
Hi all As you know when you run a piece of TSQL scrypt in Query Analizer at the bottom of the page , sql-server will show you the Time of completion of your code....is there any way to capture this time from SQL-Server environment and use it in the Front-End Application to inform the user?
I have a data set like so:UTC_TIME Timestamp NodeID Message FlagLineStation11/19/2005 10:45:07 1132397107.91 1 3 5 1028103411/3/2005 21:05:35 1131051935.20 2 3 5 1009104311/25/2005 21:12:16 1132953136.59 3 3 5 10371049I added the UTC_TIME column in as aconversion of the unix timestamp inthe TIMESTAMP column.Keeping things simple and straightforward, I need to be able tocalculate the difference from one record to the next (ordered byTIMESTAMP or UTC_TIME) and output the result into another column in thetable.NODEID is the unique id.First, what is the function to do so if, say, I only wanted tocalculate the difference between 2 records as just a basic SELECTstatement. That way I can answer quick question based on any one or twoNODEID's.Second, how would I further that to continually calculate (as statedabove)?WOuld this be a stored procedure? A trigger? A cursor?I am learning as I go here. Any help is greatly appreciated.R.
select TOP 10 rec_id,trans_id,number_id,card_no,message_id,trans_datetimefrom [dbo].[trans_log] order by trans_datetime desc101, 1,34343, 99999, 200, 2015-11-23 12:27:25.710101,2,34343,99999,210,2015-11-23 12:27:26.710102,3,43434,88888,200,2015-11-23 12:28:26.714102,4,43434,88888,233,2015-11-23 12:28:27.710expected result:34343,99999,datediff(ss,'2015-11-23 12:27:26.710','2015-11-23 12:27:25.710') --difference between row 2 and row 143434,88888,datediff(ss,'2015-11-23 12:28:26.714','2015-11-23 12:28:27.710') --
difference between row 4 and row 3difference between row 6 and row 5...In the above query, I always want to find the difference in transaction date time between second and first row in a moving window.I have the unique id as rec_id to compare the next row with the previous row.
I am using MS SQL Server 2000. I have 4 text fields 1. event begin date 2. event begin time 3. event end date 4. event end time I have to find the day and time difference in months days hours and minutes between the event begin date & time and the event end date & time – The event begin date and event end date are char(8) & event begin time and event end time are char(4) – with military time. I would need to combine the event begin date & event begin time to get the event begin date & time and the same for the event end date & time. If the values of the 4 text fields are event begin date = ‘20070207’, event begin time = ‘2015’, event end date = ‘20070208’, event end time = ‘0105’, the difference should be 0 month 0 day 4 hours 50 minutes. Can somebody please help with MS SQL Server 2000 syntax?
I have a requirement to calculate the total outage time, based on logged fault tickets, of network nodes. Basically, multiple tickets may be raised for a single node and those tickets could overlap or sequence over a given period; the task here is to calculate the total time (hh:mm) of the outage in the period.
Ex:
3 tickets raised for a node outage over, say, a 48 hour period. Ticket 1 (spanning a total of 5 hours) overlaps with ticket 2 (spans 3 hours) by 1 hour; ticket 3 starts 5 hours after ticket 2 and spans 1 hour. Total outage time on the tickets is 7hrs + 1hr (T1+T2 minus the 1hr overlap) and the full time of T3.
In summary, it's calculating the total ticket time, allowing for overlaps of tickets, etc.
I have a table that contains an employee id and dates signifying time periods that those employees were working. I need to calculate anniversaries, such as 20 year, which are the sum of all periods spend working projected out to 20 years. For example,
The periods in which the employees were inactive (the time period between active ranges) would push back their anniversary date, obviously. I'm only concerned with employees that are currently active (ie. the most recent record has a NULL end date). I thought about trying to use datediff to calculate the time between active periods, but I'm not sure how to go about doing it.
I am setting up a monitor to alert me if an SQL job has failed in the "last 20 minutes". This should run 24 hours a day, 7 days a week. My query looks something like this.
select * from TALMAIN.msdb.dbo.sysjobhistory where job_id = '7139D5D1-CD88-46E8-8324-5D5A0D8D3A27' and run_status <> 1 and DATEPART(YYYY,GETDATE()) = substring(convert(char(8),run_date),1,4)and DATEPART(MM,GETDATE()) = substring(convert(char(8),run_date),5,2) and DATEPART(DD,GETDATE()) = substring(convert(char(8),run_date),7,2)and DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)) <= 20.
The run_date and run_time columns in msdb..sysjobhistory are stored as integers. Tried a couple of things, but I am unable to convert both of them to datetime data type. The last conditions in the above logic hold true for only "2 digit" hour and minute values.
What about time values like 00:05 AM and single digit time values like 1:00 AM and 9:05 AM, for example?. I pasted some sample run_date and run_time values from sysjobhistory below.
run_date run_time
2006122821510 -- 02:15:10 AM (how to get the minute count?) 2006122821510 -- 02:15:10 AM (same as above) 20061227233014 -- 23:30:14 PM (this is strt forward) 20061227233014 -- 23:30:14 PM (same as above) 200612273016 -- 00:30:16 AM (how to get minute count?) 200612273015 -- 00:30:15 AM (how to get minute count?)
Is there a simpler logic to achieve this? Hope I was clear, else let me know. Please advise. Thank you.
I'm trying to find a solution to for a report i am building and was hoping that some of you will share your expertise with me. Basically, I need to work out how long an Incident Ticket was suspended (ie it's status is "on hold").
The data is stored something similar to the following...
Ticket Date Status ------ ---- ------ 333 14/03/2005 10:24:19 "to on hold" 333 14/03/2005 15:23:01 "from on hold" 334 14/03/2005 11:14:11 "to on hold" 334 14/03/2005 16:26:15 "from on hold" 335 15/03/2005 10:10:15 "to on hold" 335 15/03/2005 11:15:35 "from on hold" 335 15/03/2005 13:26:10 "to on hold" 335 15/03/2005 14:30:59 "from on hold" 335 16/03/2005 14:00:05 "to on hold" 335 16/03/2005 16:45:15 "from on hold" 336 16/03/2005 10:10:15 "to on hold" 336 16/03/2005 12:45:12 "from on hold"
We are maintaining 3 Shifts in our database. Problem in maintaining date and time for 3rd Shift. For example, today date is 13th July and third shift timing is 11 PM - 7 AM. Then I have to display the beginning date as 13/07/2015 11 PM and end date as 14/07/2015 7 AM. Please find the data(in seconds) available in database which I need to use for my calculation.
Date(Fcreacion) Start time in Seconds(Hcreacion) End time in seconds(Hcerrar) turno(Shift)
We have a proc that runs nightly which updates stats. For each database it will run dbcc updateusage on each table, then then sp_updatestats on the database itself.
This process normally takes about an hour, but recently the time has gone up dramatically.
I am looking at ways of making the run faster. One suggestion was to split the work and have it run in parrallel, with multiple procs all doing the same job but on different databases. So for example, I could have a proc that would determine which databases would take the longest to process, and then call 3 different copies of our update proc, each getting a different set of databases to process.
If I went this route, how could I determine which databases would take the longest to complete this process? I'm investigating determining this from sysindexes' rowmodctr, or maybe just database size?
Any suggestions on how I could get an estimate of which dbs would be the most costly to update stats? This is for SQL Server 2000.
I have a requirement to be able to calculate the transit time between international locations when I have the flight departure time and flight arrival times and departure and arrival locations.
We have a problem though, in that the datetime stored are local times, and the journeys can involve crossing the international date line in either direction, so it is possible for the arrival time to be earlier than the departure time!
To give a famous example, if you flew Concorde from London to Washington, you could depart at 10am on the 1st of June (London time) and arrive at 8.00am on 1st June (Washington time)
Even worse, you could leave Brisbane at 2.00 am on the 1st June, cross the International Date Line and arrive at San Francisco at 23:00 on 31st May!
I want to sum up a column, but only summing up the top 25 quantites and then I want to subtract the sum of all quantities from the total of the top 25...how would this be done with t-sql?
Hi, What the best way to calculate the age given two datetimes. I tried using the DATEDIFF(yyy,startdate,enddate) but that seems to only compare the year and not look at the specific day.
I am looking for something that would return the following output
birthday= 12/6/1973 EndDate = 11/30/2001 then the age should be 27
If birthday = 12/6/1973 enddate = 12/11/1973 then the age should be 28
I'm in the process of developing a Kimball-method CRM data warehouse and I'd like to have Recency, Frequency, Monetary "scores" at the customer level. Am I better off creating these in the relational database and feeding them to SSAS or have SSAS do the aggregations and assign the "scores"?
Also, has anyone located any SSIS transform scripts that would calculated RFM scores? This is probably a pretty easy task in VB.net but I haven't gone down that road yet.
When users register with my site they give me their DoB, how can I work out an age from it. I was thinkin about doing it from the year, but that isnt very accurate. Can anyone help? thanks si! ps Id only want to work out how many years old they are. si!
I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this? Thanks for any help you provide.
Hi, First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this. Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later. How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me. Skills Table
I'm fairly new to MS SQL, but I'm trying to query the time elapse between two dates. Example below...
Select dbo.res.book_date, dbo.res.arrive_date, Cast (dbo.translog.systemdate as datetime) From dbo.res Where dbo.res.resid > '500'
The third line is where I need help with a statement. I need the elapse time. Ex. if the book date is 20050820100000 and the arrive date is 20050820120000, I would like the third Select statement to return 2:00 hours or something similar. Thanks.
Hi All, I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this;
Here is my SQL, hope someone can modify it and thanks in advance.
Code:
SELECT DISTINCT instance, exec_time AS median FROM (SELECT instance, exec_time FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance FROM (SELECT TOP 50 PERCENT exec_time, instance FROM llserverlogs ORDER BY exec_time) sub_a ORDER BY 1 DESC) sub_1 UNION ALL SELECT instance, exec_time FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance FROM (SELECT TOP 50 PERCENT exec_time, instance FROM llserverlogs ORDER BY exec_time DESC) sub_b ORDER BY 1) sub_2)
I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.
the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.
For Example
If the Finalsuit is 12/01/2000 then the renewal would be 12/31/2002 the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?
