Datediff, this one is too advanced for me :-(

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Datediff, this one is too advanced for me :-(

Author

Topic

jangermann
Starting Member

3 Posts

Posted - 2010-04-06 : 08:34:44

I need help.

I need to calculate the time (Tim) between each ("Job start" and "Job stop") OR the time between ("Job start" and Job afsluttet).

Every time I got a "Job start" I also will have a "job stop" or "job afsluttet".

I need to calculate the total running time between the starts and stops (or afsluttet).

Here is my tabel:

I can get the time between the first job ID, but it can contain more than one start/stop and there is more than one JobID.

Hope anyone here is smarter than me (I guess there is :-)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-06 : 08:59:44

[code]
select JobID, datediff(n, JobStart, JobEnd)
from
(
select JobID,
JobStart = min(case when Event = 'Job start' then Tim end),
JobEnd = max(case when Event in ( 'Job stop' , 'Job afsluttet') then Tim end)
from dbo.UA#RSMLog
group by JobID
) a
[/code]

KH
[spoiler]Time is always against us[/spoiler]

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 09:05:43

quote:

Originally posted by khtan


select JobID, datediff(n, JobStart, JobEnd)
from
(
    select JobID,
           JobStart = min(case when Event = 'Job start' then Tim end),
           JobEnd   = max(case when Event in ( 'Job stop' , 'Job afsluttet') then Tim end)
    from   dbo.UA#RSMLog
    group by JobID
) a

If JobID 1 starts at 1:00 and stops at 1:01, then starts again at 2:00 and stops at 2:01, should the total running time be 1:01, or 0:02? The above query would return 1:01, but I'm thinking the OP wants 0:02.

There are 10 types of people in the world, those that understand binary, and those that don't.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-06 : 09:07:19

oh . . I didn't read the question properly

KH
[spoiler]Time is always against us[/spoiler]

jangermann
Starting Member

3 Posts

Posted - 2010-04-06 : 09:19:52

quote:
Originally posted by DBA in the making

quote:
Originally posted by khtan

select JobID, datediff(n, JobStart, JobEnd)
from
(
select JobID,
JobStart = min(case when Event = 'Job start' then Tim end),
JobEnd = max(case when Event in ( 'Job stop' , 'Job afsluttet') then Tim end)
from dbo.UA#RSMLog
group by JobID
) a

If JobID 1 starts at 1:00 and stops at 1:01, then starts again at 2:00 and stops at 2:01, should the total running time be 1:01, or 0:02? The above query would return 1:01, but I'm thinking the OP wants 0:02.

There are 10 types of people in the world, those that understand binary, and those that don't.

Thats right, in that situation I wants 0:02

For JobID 3 I would like the result to be 5+15+3=23

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-06 : 09:43:57

[code]
DECLARE @UA#RSMLog TABLE
(
[ID] int identity,
JobID int,
Tim datetime,
Event varchar(15)
)

INSERT INTO @UA#RSMLog (JobID, Tim, Event)
SELECT 1, '20100401 11:08', 'Job start' UNION ALL
SELECT 1, '20100401 11:09', 'Job stop' UNION ALL
SELECT 1, '20100401 11:10', 'Job afsluttet' UNION ALL
SELECT 2, '20100401 11:11', 'Job start' UNION ALL
SELECT 2, '20100401 11:12', 'Silo skift' UNION ALL
SELECT 2, '20100401 12:35', 'Job afsluttet' UNION ALL
SELECT 3, '20100401 11:14', 'Job start' UNION ALL
SELECT 3, '20100401 11:19', 'Job stop' UNION ALL
SELECT 4, '20100401 11:16', 'Job start' UNION ALL
SELECT 4, '20100401 11:17', 'Silo skift' UNION ALL
SELECT 4, '20100401 11:18', 'Job afsluttet' UNION ALL
SELECT 3, '20100401 11:23', 'Job start' UNION ALL
SELECT 3, '20100401 11:38', 'Job stop' UNION ALL
SELECT 3, '20100401 11:55', 'Job start' UNION ALL
SELECT 3, '20100401 11:58', 'Job afsluttet' UNION ALL
SELECT 2, '20100401 11:40', 'Job stop' UNION ALL
SELECT 2, '20100401 12:20', 'Silo skift' UNION ALL
SELECT 2, '20100401 12:01', 'Job start'

SELECT s.JobID, Tim = SUM(DATEDIFF(n, s.Tim, e.Tim))
FROM @UA#RSMLog s
CROSS APPLY
(
SELECT Tim = MIN(Tim)
FROM @UA#RSMLog x
WHERE Event IN ('Job stop', 'Job afsluttet')
AND x.JobID = s.JobID
AND x.Tim > s.Tim
) e
WHERE Event = 'Job start'
GROUP BY s.JobID

/*
JobID Tim
----------- -----------
1 1
2 63
3 23
4 2

(4 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 09:57:01

[code]CREATE TABLE #tmp (
ID INT,
JobID INT,
Tim DATETIME,
[Event] VARCHAR(50)
)

INSERT INTO #tmp
SELECT 1, 1, '2010-04-01 11:08', 'Job Start'
UNION ALL SELECT 2, 1, '2010-04-01 11:09', 'Job Stop'
UNION ALL SELECT 3, 1, '2010-04-01 11:10', 'Job Afsluttet'
UNION ALL SELECT 4, 2, '2010-04-01 11:11', 'Job Start'
UNION ALL SELECT 5, 2, '2010-04-01 11:12', 'Silo Skift'
UNION ALL SELECT 6, 2, '2010-04-01 12:35', 'Job Afsluttet'
UNION ALL SELECT 7, 3, '2010-04-01 11:14', 'Job Start'
UNION ALL SELECT 8, 3, '2010-04-01 11:19', 'Job Stop'
UNION ALL SELECT 9, 4, '2010-04-01 11:16', 'Job Start'
UNION ALL SELECT 10, 4, '2010-04-01 11:17', 'Silo Skift'
UNION ALL SELECT 11, 4, '2010-04-01 11:18', 'Job Afsluttet'
UNION ALL SELECT 12, 3, '2010-04-01 11:23', 'Job Start'
UNION ALL SELECT 13, 3, '2010-04-01 11:38', 'Job Stop'
UNION ALL SELECT 14, 3, '2010-04-01 11:55', 'Job Start'
UNION ALL SELECT 15, 3, '2010-04-01 11:58', 'Job Afsluttet'
UNION ALL SELECT 20, 2, '2010-04-01 11:40', 'Job Stop'
UNION ALL SELECT 21, 2, '2010-04-01 12:20', 'Silo Skift'
UNION ALL SELECT 22, 2, '2010-04-01 12:01', 'Job Start'

SELECT JobID, SUM(DATEDIFF(n, StartTime, EndTime)) AS RunningTime
FROM (
SELECT t1.JobID, t1.Tim AS StartTime, z.EndTime
FROM #tmp t1
CROSS APPLY ( SELECT MIN(t2.Tim) AS EndTime
FROM #tmp t2
WHERE t1.JobID = t2.JobID
AND t2.[Event] IN ('Job Afsluttet', 'Job Stop')
AND t2.Tim >= t1.Tim ) z
WHERE t1.[Event] = 'Job Start') x
GROUP BY JobID

DROP TABLE #tmp[/code]

There are 10 types of people in the world, those that understand binary, and those that don't.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 09:59:26

Hey khtan, you're query is virtually identical to mine. It must be right. :)

There are 10 types of people in the world, those that understand binary, and those that don't.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-06 : 10:07:53

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 13:11:28

[code]
SELECT JobID,SUM(TimeDiff)
FROM
(
SELECT JobID,Seq,
DATEDIFF(minute,MAX(CASE WHEN [Event] = 'job start' THEN Tim ELSE NULL END),MIN(CASE WHEN [Event] IN ('Job afsluttet','job stop') THEN Tim ELSE NULL END)) AS TimeDiff
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY JobID, CASE WHEN [Event] IN ('Job afsluttet','job stop') THEN 1 ELSE 0 END ORDER BY Tim) AS Seq, JobID, Tim, [Event]
FROM @UA#RSMLog
WHERE [Event] IN ('job start','Job afsluttet','job stop')
) t
GROUP BY JobID,Seq
)r
GROUP BY JobID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-04-06 : 14:56:46

Damn so ugly


select jobid,sum(diff)as tim from

(
	select datediff(minute,min(tim),max(tim))as diff, jobid  from

(
		select *,rid-(dense_rank()over(order by jobid,rid)-rid) as ridnew from
		(
		
			 select *,row_number()over(partition by jobid,case when event in('Job start')then 1 when event IN ('Job stop', 'Job afsluttet') then 2 end 	
			 order by tim,jobid)as rid from @UA#RSMLog  where Event in ('Job start','Job stop', 'Job afsluttet')

		)t
	
	)t1 group by ridnew,JobID
	
)t2 group by JobID

PBUH

jangermann
Starting Member

3 Posts

Posted - 2010-04-07 : 04:21:45

Thanks a lot!! My problem is solved (in more than one way :-)
I am impressed by the skills I experince here in this topic!!

I am new in this forum and already love it :-)

Thanks again, Great job!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 04:40:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources