Time difference between multiple rows
-
Hi there,
We have a Job and each job has activities. Each activity has a start and end date. What I need to know is the "NET" time spent in activities for a job.
i.e.
Activity 1 - Start 02/03/2010 14:59:00 & End 02/03/2010 22:59:00
Activity 2 - Start 02/03/2010 15:59:00 & End 02/03/2010 23:59:00
Activity 3 - Start 05/03/2010 09:10:00 & End 05/03/2010 09:20:00
What I now need is NET time by this I mean
Difference (A) between Activity 1 Start and Activity 2 End - This is because Activity 2 overlaps Activity 1 hence this calculation. If Activity 2 start and ended before Activity 1 finished then we can use the time difference between Activity 1 Start and End time.
Difference (B) between Activity 3 Start and Activity 3 End - This is because Activity 3 doesn't overlap any other Activities.
A + B = NET Time
This is just an example of a job with 3 activities, we normally get jobs with 100+ activities.
Thanks,
Kewal
-
This shouldn't be too bad, but it helps a lot to see the schema and sample data... especially in this case, as we can't easily see the grouping mechanisms.
Can you take a look at the first link in my sig and post those for us? You'll get better help and tested T-SQL as a bonus.
Also, to confirm, you did mean to post in the SQL 2k forums, right? It'll change some of the methods that are available.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Hi Craig,
Sorry about that, below sample code.
Also this for 2008 R2
IF OBJECT_ID('TempDB..#mytable', 'U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
JobNumber INT ,
JobState VARCHAR(10) ,
JobSubject VARCHAR(50) ,
JobCreate DATETIME ,
JobClose DATETIME
)
--===== Insert the test data into the test table
INSERT INTO #mytable
( JobNumber ,JobState ,JobSubject ,JobCreate ,JobClose)
SELECT 6097365 ,'Closed','Review Assignment' ,'2010-07-29 14:18:53' ,'2010-07-29 14:21:23'
UNION ALL
SELECT 6097365 ,'Closed' ,'Action document - rapid' ,'2010-07-29 14:20:28' ,'2010-07-29 14:23:15'
UNION ALL
SELECT 6097365 ,'Closed' ,'Awaiting invoice' ,'2010-07-29 14:23:28' ,'2010-08-10 10:14:52'
UNION ALL
SELECT 6097365 ,'Closed' ,'Action document - rapid' ,'2010-08-06 09:01:43' ,'2010-08-10 10:14:44'
UNION ALL
SELECT 6097365 ,'Closed' ,'Review and approve' ,'2010-08-10 10:32:24' ,'2010-08-10 10:45:51'
-
If more data/information is required please do let me know.
Thanks heaps!
K
-
kadeshara (3/6/2011)
Hi Craig,Sorry about that, below sample code.
Perfect, and don't worry about it. You're new, we won't keelhaul ya for it. 🙂
I figured you weren't in 2k, glad I asked. This will take a bit of manipulation, I'll get back here in a bit, didn't want to leave you hanging too long though.
This may be a job for Quirky Update, but I'm not convinced yet, I think I can manipulate it with data islands. Let me goof off with it a bit. If you're curious about what I'm talking about you can read these in the meanwhile:
http://www.sqlservercentral.com/articles/T-SQL/68467/
http://www.sqlservercentral.com/articles/T-SQL/71550/
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Okay, figured out one way to do it, using neither of the methods I mentioned. Maybe someone else will have better luck with that. However, here's a solution for it:
EDIT: <Use Code Found Below, more robust method.>
note this entry:
SELECT 6097365 ,'Closed' ,'Awaiting invoice' ,'2010-07-29 14:23:28' ,'2010-08-10 10:14:52'
...skews the results all out of whack, everything else is in minutes, this one's in days. I have no idea what timing metric you'd want to use here.
The CTE's are Common Table Expressions. Think of them as nothing more then one shot views. You can insert them directly inline in the code. It's just an easier way to walk you through the logic process.
Let me know what questions you have about this. 😉 😎
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
The one other thing that you really should provide is the expected results based on the sample data provided. This actually makes testing of code easier as we know what you are expected.
-
Good Point Lynn. I had to doublecheck myself about 5 times to make sure I didn't screw up the results. 😉 (Comment edited because it made no sense out of context after removing what was below.)
EDIT: Removed no longer apt comments on previously built code. See below.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Had a rock in my craw I was doing this wrong, and I was. It certainly wasn't robust enough to handle interleaving the dates properly. I was sorting in one direction then reversing the value set in the other. Needless to say that left holes. This should be relatively bulletproof.
IF OBJECT_ID('TempDB..#mytable', 'U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
JobNumber INT ,
JobState VARCHAR(10) ,
JobSubject VARCHAR(50) ,
JobCreate DATETIME ,
JobClose DATETIME
)
--===== Insert the test data into the test table
INSERT INTO #mytable
( JobNumber ,JobState ,JobSubject ,JobCreate ,JobClose)
SELECT 6097365 ,'Closed','Review Assignment' ,'2010-07-29 14:18:53' ,'2010-07-29 14:21:23'
UNION ALL
SELECT 6097365 ,'Closed' ,'Action document - rapid' ,'2010-07-29 14:20:28' ,'2010-07-29 14:23:15'
UNION ALL
SELECT 6097365 ,'Closed' ,'Awaiting invoice' ,'2010-07-29 14:23:28' ,'2010-08-10 10:14:52'
UNION ALL
SELECT 6097365 ,'Closed' ,'Action document - rapid' ,'2010-08-06 09:01:43' ,'2010-08-10 10:14:44'
UNION ALL
SELECT 6097365 ,'Closed' ,'Review and approve' ,'2010-08-10 10:32:24' ,'2010-08-10 10:45:51'
;WITH cte AS
(
SELECT
JobNumber,
JobCreate,
JobClose,
ROW_NUMBER() OVER ( PARTITION BY JobNumber ORDER BY JobCreate) AS RowNum
FROM
#MyTable
)
, MaxClose AS
(SELECT JobNumber, MAX( JobClose) AS MaxClose
FROM #MyTable
GROUP BY JobNumber
)
, cte2 AS
(SELECT
CurRow.JobNumber,
CurRow.JobCreate,
CASE WHEN (NextRow.JobCreate IS NULL)
--This is to pickup any extra time missed due to interleaving values
-- Because we're forcing any JobClose to the next start time if it overlaps,
-- we want the last record to have the last JobClose for the jobnumber.
THEN MD.MaxClose
ELSE
CASE WHEN NextRow.JobCreate < CurRow.JobClose
THEN NextRow.JobCreate
ELSE CurRow.JobClose
END
END AS ModJobClose
FROM
cte AS CurRow
JOIN
MaxClose AS MD
ONCurRow.JobNumber = MD.JobNumber
LEFT JOIN
cte AS NextRow
ON CurRow.JobNumber = NextRow.JobNumber
AND CurRow.RowNum + 1 = NextRow.RowNum
)
SELECT
JobNumber,
SUM( DATEDIFF( s, JobCreate, ModJobClose)) AS NetTimeInSeconds,
SUM( DATEDIFF( mi, JobCreate, ModJobClose)) AS NetTimeInMinutes,
SUM( DATEDIFF( mi, JobCreate, ModJobClose) / 60.0) AS NetTimeInHours --Don't use the 'h' here, it's not accurate enough.
FROM
cte2
GROUP BY
JobNumber
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Thanks Craig,
I've tried the code out it works as expected but the results get a bit screwed when I add other columns, e.g. from the data example above if I want to group the results by JobNumber and JobSubject then the calculated time calculated is incorrect e.g for JobSubject "Review and approve" gets negative value (NetTimeInSecounds, NetTimeInMinutes & NetTimeInHours
This is the code I used
IF OBJECT_ID('TempDB..#mytable', 'U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
JobNumber INT ,
JobState VARCHAR(10) ,
JobSubject VARCHAR(50) ,
JobCreate DATETIME ,
JobClose DATETIME
)
--===== Insert the test data into the test table
INSERT INTO #mytable
( JobNumber ,
JobState ,
JobSubject ,
JobCreate ,
JobClose
)
SELECT 6097365 ,
'Closed' ,
'Review Assignment' ,
'2010-07-29 14:18:53' ,
'2010-07-29 14:21:23'
UNION ALL
SELECT 6097365 ,
'Closed' ,
'Action document - rapid' ,
'2010-07-29 14:20:28' ,
'2010-07-29 14:23:15'
UNION ALL
SELECT 6097365 ,
'Closed' ,
'Awaiting invoice' ,
'2010-07-29 14:23:28' ,
'2010-08-10 10:14:52'
UNION ALL
SELECT 6097365 ,
'Closed' ,
'Action document - rapid' ,
'2010-08-06 09:01:43' ,
'2010-08-10 10:14:44'
UNION ALL
SELECT 6097365 ,
'Closed' ,
'Review and approve' ,
'2010-08-10 10:32:24' ,
'2010-08-10 10:45:51' ;
WITH cte
AS ( SELECT JobNumber ,
JobCreate ,
JobClose ,
JobSubject,
ROW_NUMBER() OVER ( PARTITION BY JobNumber ORDER BY JobCreate ) AS RowNum
FROM #MyTable
),
MaxClose
AS ( SELECT JobNumber ,
JobSubject,
MAX(JobClose) AS MaxClose
FROM #MyTable
GROUP BY JobNumber,JobSubject
),
cte2
AS ( SELECT CurRow.JobNumber ,
CurRow.JobCreate ,
CurRow.JobSubject,
CASE WHEN ( NextRow.JobCreate IS NULL ) --This is to pickup any extra time missed due to interleaving values
-- Because we're forcing any JobClose to the next start time if it overlaps,
-- we want the last record to have the last JobClose for the jobnumber.
THEN MD.MaxClose
ELSE CASE WHEN NextRow.JobCreate < CurRow.JobClose
THEN NextRow.JobCreate
ELSE CurRow.JobClose
END
END AS ModJobClose
FROM cte AS CurRow
JOIN MaxClose AS MD ON CurRow.JobNumber = MD.JobNumber
LEFT JOIN cte AS NextRow ON CurRow.JobNumber = NextRow.JobNumber
AND CurRow.RowNum + 1 = NextRow.RowNum
)
SELECT JobNumber ,
JobSubject,
SUM(DATEDIFF(s, JobCreate, ModJobClose)) AS NetTimeInSeconds ,
SUM(DATEDIFF(mi, JobCreate, ModJobClose)) AS NetTimeInMinutes ,
SUM(DATEDIFF(mi, JobCreate, ModJobClose) / 60.0) AS NetTimeInHours --Don't use the 'h' here, it's not accurate enough.
FROM cte2
GROUP BY JobNumber,JobSubject
I am sure there must be something silly that I am doing :crying:
Thanks Lynn - I'll try and give a description of the desired outcome.
The business requirement for this is to basically show NET time spent to fulfill a job, the business would like the results in a higher level as generated by Craig's code's results earlier OR they can break down the results by various business parameters, e.g. Job Subject, Business Type, Job Person (who did the job).
Sample data
INSERT INTO #mytable
( JobNumber , JobState , JobSubject , JobCreate , JobClose, JobPerson, BusinessType )
SELECT 6097365 , 'Closed' , 'Review Assignment' , '2010-07-29 14:18:53' , '2010-07-29 14:21:23', 'John Brown', 'Motor'
UNION ALL
SELECT 6097365 , 'Closed' , 'Action document - rapid' , '2010-07-29 14:20:28' , '2010-07-29 14:23:15', 'John Grey', 'Motor'
UNION ALL
SELECT 6097365 , 'Closed' , 'Awaiting invoice' , '2010-07-29 14:23:28' , '2010-08-10 10:14:52', 'John White', 'Motor'
UNION ALL
SELECT 6097365 , 'Closed' , 'Action document - rapid' , '2010-08-06 09:01:43' , '2010-08-10 10:14:44', 'John Black','Motor'
UNION ALL
SELECT 6097365 , 'Closed' , 'Review and approve' , '2010-08-10 10:32:24' , '2010-08-10 10:45:51' , 'John Black', 'Motor';
-
Note in cte that there's the ROW_NUMBER() component. Inside there, is the PARTITION BY. That Partitioning will control how it organizes the data. Take a look at simply a select * from cte.
You will have to modify that PARTITION BY component for each grouping difference you want to make.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Craig Farrell (3/7/2011)
Note in cte that there's the ROW_NUMBER() component. Inside there, is the PARTITION BY. That Partitioning will control how it organizes the data. Take a look at simply a select * from cte.You will have to modify that PARTITION BY component for each grouping difference you want to make.
Hi Craig,
Sorry I did this but it doesn't work, I may not be doing it correctly. :-(.
The end result that I get using the sample data provided earlier I get the below
See attachment "Query 1"
Using the below query I get the below results. If you sum the NetTimeInMinutes it doesn't sum up to 17049
See attachment "Query 2"
This is what I've done. Can you please guide me, thanks heaps.
;WITH cte AS
(
SELECT
JobNumber,
JobCreate,
JobClose,
JobSubject, -- Had to add this so I can use it later in the query to display JobSubject
ROW_NUMBER() OVER ( PARTITION BY JobNumber,JobSubject ORDER BY JobCreate) AS RowNum
FROM
#MyTable
)
, MaxClose AS
(SELECT JobNumber, MAX( JobClose) AS MaxClose
FROM #MyTable
GROUP BY JobNumber
)
, cte2 AS
(SELECT
CurRow.JobNumber,
CurRow.JobCreate,
CurRow.JobSubject, -- Had to add this so I can use it later in the query to display JobSubject
CASE WHEN (NextRow.JobCreate IS NULL)
--This is to pickup any extra time missed due to interleaving values
-- Because we're forcing any JobClose to the next start time if it overlaps,
-- we want the last record to have the last JobClose for the jobnumber.
THEN MD.MaxClose
ELSE
CASE WHEN NextRow.JobCreate < CurRow.JobClose
THEN NextRow.JobCreate
ELSE CurRow.JobClose
END
END AS ModJobClose
FROM
cte AS CurRow
JOIN
MaxClose AS MD
ON CurRow.JobNumber = MD.JobNumber
LEFT JOIN
cte AS NextRow
ON CurRow.JobNumber = NextRow.JobNumber
AND CurRow.RowNum + 1 = NextRow.RowNum
)
SELECT
JobNumber,
JobSubject,
SUM( DATEDIFF( s, JobCreate, ModJobClose)) AS NetTimeInSeconds,
SUM( DATEDIFF( mi, JobCreate, ModJobClose)) AS NetTimeInMinutes,
SUM( DATEDIFF( mi, JobCreate, ModJobClose) / 60.0) AS NetTimeInHours --Don't use the 'h' here, it's not accurate enough.
FROM
cte2
GROUP BY
JobNumber,JobSubject
Thanks heaps!
K
-
Note, when you changed the grouping, you didn't change all of it:
, MaxClose AS
(SELECT JobNumber, JobSubject, MAX( JobClose) AS MaxClose
FROM #MyTable
GROUP BY JobNumber, JobSubject
)
and change this:
JOIN
MaxClose AS MD
ON CurRow.JobNumber = MD.JobNumber
CurRow.JobSubject = md.JobSubject
This isn't meant to sound mean, but did you understand the code in its first iteration before you went to modify it? You need to grasp the moving parts here before you push this to production, or it's going to be very buggy and it will difficult, if not impossible, for you to support.
Break it down, looking at each CTE in order, and walkthrough the logic that's happening. Once you do that, all the different grouping changes will make a lot more sense.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Thanks Craig,
I've hardly had any experience with CTE's before but I am learning thanks to helpful people like yourself and few good examples/articles on the internet. Thanks for pointing out my mistake and I think I've understood and corrected (hopefully) code now.
Thanks heaps,
K
-- For people who read this post in the future, "I think" the below works!.
IF OBJECT_ID('TempDB..#mytable', 'U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
JobNumber INT ,
JobState VARCHAR(10) ,
JobSubject VARCHAR(50) ,
JobCreate DATETIME ,
JobClose DATETIME
)
--===== Insert the test data into the test table
INSERT INTO #mytable
( JobNumber ,
JobState ,
JobSubject ,
JobCreate ,
JobClose
)
SELECT 6097365 ,
'Closed' ,
'Review Assignment' ,
'2010-07-29 14:18:53' ,
'2010-07-29 14:21:23'
UNION ALL
SELECT 6097365 ,
'Closed' ,
'Action document - rapid' ,
'2010-07-29 14:20:28' ,
'2010-07-29 14:23:15'
UNION ALL
SELECT 6097365 ,
'Closed' ,
'Action document - rapid' ,
'2010-08-06 09:01:43' ,
'2010-08-10 10:14:44'
UNION ALL
SELECT 6097365 ,
'Closed' ,
'Review and approve' ,
'2010-08-10 10:32:24' ,
'2010-08-10 10:45:51'
UNION ALL
SELECT 6097365 ,
'Closed' ,
'Awaiting invoice' ,
'2010-07-29 14:23:28' ,
'2010-08-10 10:14:52' ;
WITH cte
AS ( SELECT JobNumber ,
JobCreate ,
JobClose ,
JobSubject ,
ROW_NUMBER() OVER ( PARTITION BY JobNumber, JobSubject ORDER BY JobCreate ) AS RowNum
FROM #MyTable
),
MaxClose
AS ( SELECT JobNumber ,
JobSubject ,
MAX(JobClose) AS MaxClose
FROM #MyTable
GROUP BY JobNumber ,
JobSubject
),
cte2
AS ( SELECT CurRow.JobNumber ,
CurRow.JobCreate ,
CurRow.JobSubject ,
CASE WHEN ( NextRow.JobCreate IS NULL )
THEN MD.MaxClose
ELSE CASE WHEN NextRow.JobCreate < CurRow.JobClose
THEN NextRow.JobCreate
ELSE CurRow.JobClose
END
END AS ModJobClose
FROM cte AS CurRow
JOIN MaxClose AS MD ON CurRow.JobNumber = MD.JobNumber
AND CurRow.JobSubject = MD.JobSubject
LEFT JOIN cte AS NextRow ON CurRow.JobNumber = NextRow.JobNumber
AND CurRow.RowNum + 1 = NextRow.RowNum
AND CurRow.JobSubject = NextRow.JobSubject
)
SELECT JobNumber ,
JobSubject ,
SUM(DATEDIFF(s, JobCreate, ModJobClose)) AS NetTimeInSeconds ,
SUM(DATEDIFF(mi, JobCreate, ModJobClose)) AS NetTimeInMinutes ,
SUM(DATEDIFF(mi, JobCreate, ModJobClose) / 60.0) AS NetTimeInHours --Don't use the 'h' here, it's not accurate enough.
FROM cte2
GROUP BY JobNumber ,
JobSubject
-
kadeshara (3/8/2011)
Thanks Craig,I've hardly had any experience with CTE's before but I am learning thanks to helpful people like yourself and few good examples/articles on the internet. Thanks for pointing out my mistake and I think I've understood and corrected (hopefully) code now.
Thanks heaps,
K
My pleasure. I just wanted to make sure you understood what you were looking at before firing both barrels at your own feet. Unfortunately there's no 'nice' way to ask that question.
Understanding CTEs just requires a little bit of extrapolation. It's like writing a one shot view, or just separating out subqueries for reuse. Here's a good example (if contrived and useless).
SELECT
a.ColA,
b.ColB,
c.ColB
FROm
tbl AS a
JOIN
(SELECT ColA, 2+1+3+4+ColF AS ColB from tbl2
) AS b
ONa.ColA = b.ColA
JOIN
(SELECT ColA, 2+1+3+4+ColF AS ColB from tbl2
) AS c
ONa.ColD = c.ColA
-- CTE way
;WITH cte AS
(SELECT ColA, 2+1+3+4+ColF AS ColB from tbl2)
SELECT
a.ColA,
b.ColB,
c.ColB
FROm
tbl AS a
JOIN
cte AS b
ONa.ColA = b.ColA
JOIN
cte AS c
ONa.ColD = c.ColA
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply