On/Off Time report - Can't figure out the SQL

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 2000 Forums

Transact-SQL (2000)

On/Off Time report - Can't figure out the SQL

Author

Topic

k420
Starting Member

32 Posts

Posted - 2006-03-02 : 11:54:22

Hi,

I've been asked to create a report based on some on and off times for some machines out in the field and I am really struggling with the SQL to produce what I want. Hell, I'm not even sure whether its possible to do what I want but I'm hoping someone here will show me that it is.

The data I have is in the form you see below and I can get this data sometimes daily, every few days and sometimes weekly.


Machine ID 	On Timestamp		Off Timestamp
1		01-Feb-2006 08:30:00	03-Feb-2006 17:10:00
1		04-Feb-2006 08:45:00	04-Feb-2006 17:20:00
1		05-Feb-2006 08:55:00	07-Feb-2006 17:23:00

The report I need to produce will detail (on a rolling basis) the on time in hours and minutes to the nearest minute per day for the last 7 days. As such, for the example data above, the report should look like this:


Machine ID	7th-Feb	6th-Feb	5th-Feb	4th-Feb	3rd-Feb	2nd-Feb	1st-Feb
1	        17h 23m	24h 0m	15h 5m	8h 36m	17h 10m	24h 0m	15h 30m

You can get to these figures quite simply in your head and programatically I was thinking that I need to turn the data I have into something like the following and then use simply maths for the answer.


On Timestamp		Off Timestamp
01-Feb-2006 08:30	01-Feb-2006 23:59
02-Feb-2006 00:00	02-Feb-2006 23:59
03-Feb-2006 00:00	03-Feb-2006 17:10
04-Feb-2006 08:45	04-Feb-2006 17:20
05-Feb-2006 08:55	05-Feb-2006 23:59
06-Feb-2006 00:00	06-Feb-2006 23:59
07-Feb-2006 00:00	07-Feb-2006 17:23

I've been battering my head over this all afternoon and unfortunatly my SQL skills aren't good enough to get me there (if that is even the best way) and I'm really hoping that one of you guys can help me.

Thanks for your time in advance

Keith

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-02 : 14:17:42

This should do it:


select
	[Hours] = datepart(hh,Elapsed_Time),
	[Minutes] = datepart(mi,Elapsed_Time),
	Elapsed = right(convert(varchar(16),Elapsed_Time,121),5),
	[On_Timestamp] = convert(varchar(16),[On_Timestamp],121),
	[Off_Timestamp] = convert(varchar(16),[Off_Timestamp],121)
from
(
select
	Elapsed_Time =
	dateadd(ss,datediff(ss,[On_Timestamp],[Off_Timestamp]),0),
	[On_Timestamp],
	[Off_Timestamp]
from
(
select 
	[On_Timestamp] = convert(datetime,'01-Feb-2006 08:30'),
	[Off_Timestamp]= convert(datetime,'01-Feb-2006 23:59') union all
select '02-Feb-2006 00:00','02-Feb-2006 23:59' union all
select '03-Feb-2006 00:00','03-Feb-2006 17:10' union all
select '04-Feb-2006 08:45','04-Feb-2006 17:20' union all
select '05-Feb-2006 08:55','05-Feb-2006 23:59' union all
select '06-Feb-2006 00:00','06-Feb-2006 23:59' union all
select '07-Feb-2006 00:00','07-Feb-2006 17:23'
) a
)aa
order by
	[On_Timestamp]

Results:



Hours       Minutes     Elapsed On_Timestamp     Off_Timestamp    
----------- ----------- ------- ---------------- ---------------- 
15          29          15:29   2006-02-01 08:30 2006-02-01 23:59
23          59          23:59   2006-02-02 00:00 2006-02-02 23:59
17          10          17:10   2006-02-03 00:00 2006-02-03 17:10
8           35          08:35   2006-02-04 08:45 2006-02-04 17:20
15          4           15:04   2006-02-05 08:55 2006-02-05 23:59
23          59          23:59   2006-02-06 00:00 2006-02-06 23:59
17          23          17:23   2006-02-07 00:00 2006-02-07 17:23

(7 row(s) affected)

CODO ERGO SUM

k420
Starting Member

32 Posts

Posted - 2006-03-03 : 03:10:49

Michael,

Thanks for the response but I think I may have confused things by saying that I think I need to turn the data into this:


On Timestamp		Off Timestamp
01-Feb-2006 08:30	01-Feb-2006 23:59
02-Feb-2006 00:00	02-Feb-2006 23:59
03-Feb-2006 00:00	03-Feb-2006 17:10
04-Feb-2006 08:45	04-Feb-2006 17:20
05-Feb-2006 08:55	05-Feb-2006 23:59
06-Feb-2006 00:00	06-Feb-2006 23:59
07-Feb-2006 00:00	07-Feb-2006 17:23

I don't actually have the data above or know how to generate it from the data that I have.

The data that I have is just this:


Machine ID 	On Timestamp		Off Timestamp
1		01-Feb-2006 08:30:00	03-Feb-2006 17:10:00
1		04-Feb-2006 08:45:00	04-Feb-2006 17:20:00
1		05-Feb-2006 08:55:00	07-Feb-2006 17:23:00

So the end result that you gave me is correct but I need to be able to get it from the dataset above. If we take this further, a fuller dataset would be:


Machine ID 	On Timestamp		Off Timestamp
1		01-Feb-2006 08:30:00	03-Feb-2006 17:10:00
1		04-Feb-2006 08:45:00	04-Feb-2006 17:20:00
1		05-Feb-2006 08:55:00	07-Feb-2006 17:23:00
2		01-Feb-2006 08:29:00	09-Feb-2006 19:34:00
1		07-Feb-2006 19:23:00	10-Feb-2006 18:02:00
1		11-Feb-2006 08:43:00	12-Feb-2006 08:26:00
1		12-Feb-2006 08:27:00	14-Feb-2006 17:40:00
2		11-Feb-2006 08:50:00	15-Feb-2006 18:04:00

From this I would want to report on both machines 1 and 2 at the same time (in a matrix with machines down the left and days across the top) for any 7 day period where a user could simply request a report starting on the 3rd going back 7 days or the 10th going back 7 days and so on.

I hope this clears up my predicament and thanks again for your time in advance.

Cheers

Keith

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-03 : 03:54:57

quote:
Machine ID On Timestamp Off Timestamp
1 01-Feb-2006 08:30:00 03-Feb-2006 17:10:00
1 04-Feb-2006 08:45:00 04-Feb-2006 17:20:00
1 05-Feb-2006 08:55:00 07-Feb-2006 17:23:00
2 01-Feb-2006 08:29:00 09-Feb-2006 19:34:00
1 07-Feb-2006 19:23:00 10-Feb-2006 18:02:00
1 11-Feb-2006 08:43:00 12-Feb-2006 08:26:00
1 12-Feb-2006 08:27:00 14-Feb-2006 17:40:00
2 11-Feb-2006 08:50:00 15-Feb-2006 18:04:00

So if the above is your dataset. So what is the expected result ?

----------------------------------
'KH'

k420
Starting Member

32 Posts

Posted - 2006-03-03 : 04:00:53

If the user requested to view a report from the 7th and back with the 7th being included the results would be something like this:


Machine ID	7th-Feb	6th-Feb	5th-Feb	4th-Feb	3rd-Feb	2nd-Feb	1st-Feb
1	        17h 23m	24h 0m	15h 5m	8h 36m	17h 10m	24h 0m	15h 30m
2		24h 0m	24h 0m	24h 0m	24h 0m	24h 0m	24h 0m	15h 31m

Cheers

Keith

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-03 : 04:42:38

quote:
Machine ID On Timestamp Off Timestamp
1 01-Feb-2006 08:30:00 03-Feb-2006 17:10:00
1 04-Feb-2006 08:45:00 04-Feb-2006 17:20:00
1 05-Feb-2006 08:55:00 07-Feb-2006 17:23:00
2 01-Feb-2006 08:29:00 09-Feb-2006 19:34:00
1 07-Feb-2006 19:23:00 10-Feb-2006 18:02:00
1 11-Feb-2006 08:43:00 12-Feb-2006 08:26:00
1 12-Feb-2006 08:27:00 14-Feb-2006 17:40:00
2 11-Feb-2006 08:50:00 15-Feb-2006 18:04:00

Machine ID	7th-Feb	6th-Feb	5th-Feb	4th-Feb	3rd-Feb	2nd-Feb	1st-Feb
1	        22h 00m	24h 0m	15h 5m	8h 36m	17h 10m	24h 0m	15h 30m
2		24h 0m	24h 0m	24h 0m	24h 0m	24h 0m	24h 0m	15h 31m

The time for Machine 1 on 7th-Feb should be 22h 00m right ?

select	mins / 60, mins % 60
from
(
select	datediff(minute, '2006-02-07 00:00', '2006-02-07 17:23') + 
	datediff(minute, '2006-02-07 19:23', '2006-02-08 00:00') as mins
) a

result = 22 , 0

----------------------------------
'KH'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-03 : 05:01:35

Here you go. Below is the test script

create table #data
(
	Machine_ID	int,
	On_Timestamp	datetime,	
	Off_Timestamp	datetime
)

insert into #data
select	1,	'01-Feb-2006 08:30:00',	'03-Feb-2006 17:10:00'	union all
select	1,	'04-Feb-2006 08:45:00',	'04-Feb-2006 17:20:00'	union all
select	1,	'05-Feb-2006 08:55:00',	'07-Feb-2006 17:23:00'	union all
select	2,	'01-Feb-2006 08:29:00',	'09-Feb-2006 19:34:00'	union all
select	1,	'07-Feb-2006 19:23:00',	'10-Feb-2006 18:02:00'	union all
select	1,	'11-Feb-2006 08:43:00',	'12-Feb-2006 08:26:00'	union all
select	1,	'12-Feb-2006 08:27:00',	'14-Feb-2006 17:40:00'	union all
select	2,	'11-Feb-2006 08:50:00',	'15-Feb-2006 18:04:00'	union all
select	3,	'03-Feb-2006 01:10:00', '05-Feb-2006 19:01:00'

-----------------------------------------------------------------------------------------

declare
	@rpt_date	datetime
select	@rpt_date	= '2006-02-07'

select	Machine_ID,
	max(case when dte = dateadd(day,  0, @rpt_date) then convert(varchar(5), tot_mins / 60) + 'h ' + convert(varchar(5), tot_mins % 60) + 'm' else '' end) as [Day 1],
	max(case when dte = dateadd(day, -1, @rpt_date) then convert(varchar(5), tot_mins / 60) + 'h ' + convert(varchar(5), tot_mins % 60) + 'm' else '' end) as [Day 2],
	max(case when dte = dateadd(day, -2, @rpt_date) then convert(varchar(5), tot_mins / 60) + 'h ' + convert(varchar(5), tot_mins % 60) + 'm' else '' end) as [Day 3],
	max(case when dte = dateadd(day, -3, @rpt_date) then convert(varchar(5), tot_mins / 60) + 'h ' + convert(varchar(5), tot_mins % 60) + 'm' else '' end) as [Day 4],
	max(case when dte = dateadd(day, -4, @rpt_date) then convert(varchar(5), tot_mins / 60) + 'h ' + convert(varchar(5), tot_mins % 60) + 'm' else '' end) as [Day 5],
	max(case when dte = dateadd(day, -5, @rpt_date) then convert(varchar(5), tot_mins / 60) + 'h ' + convert(varchar(5), tot_mins % 60) + 'm' else '' end) as [Day 6],
	max(case when dte = dateadd(day, -6, @rpt_date) then convert(varchar(5), tot_mins / 60) + 'h ' + convert(varchar(5), tot_mins % 60) + 'm' else '' end) as [Day 7]
from
(
	-- Group it by Machine_ID and date
	select	Machine_ID, dte, sum(time_mins) as tot_mins
	from
	(
		-- Compute the time spent
		select	m.Machine_ID, dte, t.On_Timestamp, t.Off_Timestamp,
			time_mins = 
			case 	when 	dte = dateadd(day, datediff(day, 0, On_Timestamp), 0) 
				and	dte <> dateadd(day, datediff(day, 0, Off_Timestamp), 0) then
					datediff(minute, On_Timestamp, dateadd(day, 1, dte))
				when	dte <> dateadd(day, datediff(day, 0, On_Timestamp), 0) 
				and	dte <> dateadd(day, datediff(day, 0, Off_Timestamp), 0) then
					24 * 60
				when	dte <> dateadd(day, datediff(day, 0, On_Timestamp), 0) 
				and	dte = dateadd(day, datediff(day, 0, Off_Timestamp), 0) then
					datediff(minute, dte, Off_Timestamp)
				else
					datediff(minute, On_Timestamp, Off_Timestamp)
				end
		from
		(	-- Get Machine ID valid for selection
			select	Machine_ID
			from	#data
			where	On_Timestamp	>= dateadd(day, -7, @rpt_date)
			and	On_Timestamp	<= dateadd(day, 1, @rpt_date)
			group by Machine_ID
		) as m
		cross join
		(	-- Generate the dates for past 7 days
			select	dateadd(day,  0, @rpt_date)	as dte	union all
			select	dateadd(day, -1, @rpt_date)	as dte	union all
			select	dateadd(day, -2, @rpt_date)	as dte	union all
			select	dateadd(day, -3, @rpt_date)	as dte	union all
			select	dateadd(day, -4, @rpt_date)	as dte	union all
			select	dateadd(day, -5, @rpt_date)	as dte	union all
			select	dateadd(day, -6, @rpt_date)	as dte
		) as d
		inner join #data t
		on	m.Machine_ID	= t.Machine_ID
		and	d.dte		>= dateadd(day, datediff(day, 0, t.On_Timestamp), 0)
		and	d.dte		<= dateadd(day, datediff(day, 0, t.Off_Timestamp), 0)
	) as r
	group by Machine_ID, dte
) as p
group by Machine_ID
order by Machine_ID

Result
======
Machine_ID  Day 1         Day 2         Day 3         Day 4         Day 5         Day 6         Day 7         
----------- ------------- ------------- ------------- ------------- ------------- ------------- ------------- 
1           22h 0m        24h 0m        15h 5m        8h 35m        17h 10m       24h 0m        15h 30m
2           24h 0m        24h 0m        24h 0m        24h 0m        24h 0m        24h 0m        15h 31m
3                                       19h 1m        24h 0m        22h 50m                     

(3 row(s) affected)

With this, i think i have used up all my brain juice for today.

----------------------------------
'KH'

k420
Starting Member

32 Posts

Posted - 2006-03-03 : 06:17:18

Thank you so much - you are a star!

I've converted the code to work with my actual database table and its all working just fine.

I'm currently not too sure on how it all works so I'm gonna see if I can figure that out. Hope its ok to ask about bits if I don't manage to figure it all out myself.

Thanks again

Keith

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-03 : 07:17:29

"I'm currently not too sure on how it all works so I'm gonna see if I can figure that out. Hope its ok to ask about bits if I don't manage to figure it all out myself."
Sure. Just start running from the inner query to the outer one

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources