asked on

query dates

In a select statement like below within SQL Server:-
Select
[Day]
,[TimeIn]
, [TimeOut]
, [Duration]
,[Duration] / '60' as [Hours]
From etc.,

is it possible to split the duration hours on the basis of start and finish times. For example, if timein is after 0800 but before 2100 and the day is a weekend, can a new field be created with the calculation of the hours between these times, say WeekDayNet.

If hours over 2100, can these be added to say a NightNet column in the view.

Just looking for ideas on how I should go about this and appreciate any guidance. Thanks. SJ

G Godwin

Sure.

If you would like to give me a little sample data to show what you are working with, I can help.

-gdg_dba

sunny-j

ASKER

thank you, will this be ok,

In terms of time periods.
Day is 0800 to 2000
Night is 2000 to 0800 and Sat and Sunday run from midnight to midnight.
The first line therefore should show 11 hours in NightNet and 1 in DayNet.
Thanks for your help and swift response.

Day      TimeIn      TimeOut      Duration      Hours      DayNet      NightNet      SatNet      SunNet
2015-01-05      2015-01-05 21:00:00      2015-01-06 09:00:00      720      12      NULL      NULL      NULL      NULL
2015-01-06      2015-01-06 21:00:00      2015-01-07 09:00:00      720      12      NULL      NULL      NULL      NULL
2015-01-07      2015-01-07 21:00:00      2015-01-08 09:00:00      720      12      NULL      NULL      NULL      NULL
2014-03-15      2014-03-15 00:00:00      2014-03-15 23:59:00      1439      23      NULL      NULL      NULL      NULL
2014-03-16      2014-03-16 00:00:00      2014-03-16 23:59:00      1439      23      NULL      NULL      NULL      NULL

G Godwin

This begs the question:

Since you have some times with minutes, but you are counting hours, how do you need to handle partial hours?

-GDG

sunny-j

ASKER

I understand, the target database will handle them as such 11 hours and 15 minutes will be 11.25. We can also ignore this column (hours) for now and calculate on minutes. Thanks again.

Scott Pletcher

I haven't finished the Night minutes definition, but hopefully this gets you close enough to get there:

Select
[Day]
,[TimeIn]
,[TimeOut]
,CAST(DayNetMinutes / 60.0 AS decimal(4, 2)) AS DayNetDuration,
,CAST(NightNetMinutes / 60.0 AS decimal(4, 2)) AS NightNetDuration
From etc
Cross Apply (
SELECT CASE
WHEN TimeIn >= 2100 THEN 0
ELSE DATEDIFF(MINUTE, CASE WHEN TimeIn < 0800 THEN 0800 ELSE TimeIn END,
CASE WHEN TimeOut >= 2100 THEN 2100 ELSE TimeOut END)
END AS DayNetMinutes,
CASE WHEN TimeIn < 0800 THEN
--...<rest_of_night_minutes_definition_here>
END AS NightNetMinutes
) As assign_alias_names

G Godwin

You will need to iterate through the time slices identifying the status of each slice.

Done in advance, this could be stored in a permanent table or two for times and dates. This option would speed performance.

Barring that... Here's one way this could be handled using a function.

-- drop table #TMP

select convert(datetime, TimeIn) TimeIn, convert(datetime, Timeout) TimeOut into #TMP 
from (values 
 ('20150601 01:00', '20150605 023:00')
,('20150602 09:00', '20150605 020:00')
,('20150603 07:00', '20150605 020:45')
,('20150604 09:00', '20150607 020:15')
,('20150605 10:00', '20150605 020:30')

) t1 (TimeIn, TimeOut)

--drop function TIME_TABLE
create function TIME_TABLE (@TimeIn Datetime, @TimeOut datetime, @StartOfDay time , @EndOfDay time)
returns 
 @TimeTable table (
		 -- id int identity(1,1)	, 
		  Dt datetime
		, DAY_Hours int
		, Night_Hours int
		, WD_Hours int
		, WE_Hours int )

as
BEGIN 
	
	if @StartOfDay is null 
		set @StartOfDay = '08:00'

	if @EndOfDay is null 
		set @EndOfDay = '20:00'

	while @timein < @timeout 
		begin
			insert into @TimeTable (dt, DAY_Hours, Night_Hours, WD_Hours, WE_Hours) 
			values (@TimeIn
					, case when datepart(hour, @TimeIn) between datepart(hour,@StartOfDay) and datepart(hour,@EndOfDay) then 1 else 0 end 
					, case when datepart(hour, @TimeIn) between datepart(hour,@StartOfDay) and datepart(hour,@EndOfDay) then 0 else 1 end 
					, case when datepart(dw, @TimeIn) between 2 and 6 then 1 else 0 end 
					, case when datepart(dw, @TimeIn) between 2 and 6 then 0 else 1 end 
					)
			set @TimeIn = dateadd(minute, 1, @timein)
		end

		return --@Time_Table
END 

-- using default day times...
select *  
, (select sum(day_hours)	/ 60.0 from dbo.time_table(timein, timeout, NULL, NULL )) as DAY_HOURS
, (select sum(night_hours)	/ 60.0 from dbo.time_table(timein, timeout, NULL, NULL )) as NIGHT_HOURS
, (select sum(WD_hours)		/ 60.0 from dbo.time_table(timein, timeout, NULL, NULL )) AS WEEKDAY_HOURS
, (select sum(WE_hours)		/ 60.0 from dbo.time_table(timein, timeout, NULL, NULL )) AS WEEKEND_HOURS 
from #TMP

-- using custom day times...
select *  
, (select sum(day_hours)	/ 60.0 from dbo.time_table(timein, timeout, '09:00', '17:00' )) as DAY_HOURS
, (select sum(night_hours)	/ 60.0 from dbo.time_table(timein, timeout, '09:00', '17:00' )) as NIGHT_HOURS
, (select sum(WD_hours)		/ 60.0 from dbo.time_table(timein, timeout, '09:00', '17:00' )) AS WEEKDAY_HOURS
, (select sum(WE_hours)		/ 60.0 from dbo.time_table(timein, timeout, '09:00', '17:00' )) AS WEEKEND_HOURS 
from #TMP

Open in new window

If this runs often or on large data sets, you might consider the option of creating a time table and a calendar table.

G Godwin

Sunny,

I just wanted to check back with you to see if you got what you needed.

-GDG_DBA

sunny-j

ASKER

Thank you both. GDG, would you kindly go back a step and explain how I merge your code in the my view - I am quite new to creating sql queries and very keen to use functions like this.

this is the current view SELECT [TableA]
,[Day]
,[TimeIn]
,[TimeOut]
,[Duration]
,[Hours]
,[Location]

,
,[Notes]
,[DayNet]
,[NightNet]
,[SatNet]
,[SunNet]

FROM [TableA]

Thank you so much... Kind regards. SJ

sunny-j

ASKER

by the way, I am very IMPRESSED with the way you have taken the time to construct this query for me..

G Godwin

Have you created the function, and is it working as you expect it to?

Some notes/questions:

The original question mentions "weekend" vs weekday", not Saturday and Sunday vs weekday. Thus, the answer is written for that. It could be reworked to handle Saturday and Sunday separately, but that wasn't in the original question. Which one do you need?

I will need you to describe for me what each column is in the view you want to create. Otherwise, I have to guess, and I would get it wrong.

e.g.
SELECT
,[Day] -- Day of the week for TimeIn
,[TimeIn] -- TimeIn Column (formatting?)
,[TimeOut] -- TimeOut Column (formatting?)
,[Duration] -- In hours, minutes, etc...?
,[Hours] -- Total hours?
,[Location] -- ...
,[Notes] -- ...
,[DayNet] -- Decimal Hours (2 decimal places)?
,[NightNet] -- Decimal Hours (2 decimal places)?
,[SatNet] -- Decimal Hours (2 decimal places)?
,[SunNet] -- Decimal Hours (2 decimal places)?

FROM [TableA]

sunny-j

ASKER

Hi GDG,

Yes, I have created the function and it is working as expected - thank you.

Answer to notes/questions:

The exact times are as follows.
Day - 0800 to 2000
Night 2000 to 0800
Sat 0000 to 0000
Sun 0000 to 0000

•I will need you to describe for me what each column is in the view you want to create. Otherwise, I have to guess, and I would get it wrong.

Please see below.

e.g.
SELECT
,[Day] -- Day of the week for TimeIn correct. 2015-04-11
,[TimeIn] -- TimeIn Column (formatting? )2015-04-11 20:30:00
,[TimeOut] -- TimeOut Column (formatting?) 2015-04-11 20:30:00
,[Duration] -- In hours, minutes, etc...? minutes
,[Hours] -- Total hours? yes2 decimal places.
,[Location] -- ...
,[Notes] -- ...
,[DayNet] -- Decimal Hours (2 decimal places)? Minutes
,[NightNet] -- Decimal Hours (2 decimal places)? Minutes
,[SatNet] -- Decimal Hours (2 decimal places)? Minutes
,[SunNet] -- Decimal Hours (2 decimal places)? Minutes

FROM [TableA] thank you again.

ASKER CERTIFIED SOLUTION

G Godwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

G Godwin

You mentioned "merging the code" into your view.

if you need a view:

CREATE VIEW myVIEW as 
select 
  convert(varchar(10), TimeIn , 121) as 'DAY'  
, convert(varchar(19),TimeIn , 121) 'TimeIn' 
, convert(varchar(19),TimeOut, 121) 'TimeOut'
, datediff(minute, timein, timeout) 'Duration'
, convert(dec(10,2), datediff(minute, timein, timeout)/60.0) 'HOURS' 
, '...' 'LOCATION'
, '...' 'NOTES'
, (select sum(DAY_MIN)		from dbo.time_table(timein, timeout, '08:00', '17:00' )) as DayNet
, (select sum(night_MIN)	from dbo.time_table(timein, timeout, '08:00', '17:00' )) as NightNet
--, (select sum(WEEKDAY_MIN)	from dbo.time_table(timein, timeout, '08:00', '17:00' )) AS WeekdayNet
, (select sum(SAT_MIN)		from dbo.time_table(timein, timeout, '08:00', '17:00' )) AS SatNet 
, (select sum(SUN_MIN)		from dbo.time_table(timein, timeout, '08:00', '17:00' )) AS SunNet
--select * 
from TableA

Open in new window

then:

select 
[DAY], [TimeIn], [TimeOut], [Duration], [HOURS], [LOCATION], [NOTES], [DayNet], [NightNet], [SatNet], [SunNet]
from myView

Open in new window

sunny-j

ASKER

THIS is absolutely amazing. I categorically have never had such an exact and comprehensive reply such as this on EE or indeed any other forum. I will try this out in the morning, I have leant a lot from this. Thank you so much. Kind regards. SJ

sunny-j

ASKER

Such amazing response and a true IT Professional. The effort and detail that went into my question was quite simply astonishing.

G Godwin

Fine tuning it:

ALTER function TIME_TABLE (@TimeIn Datetime, @TimeOut datetime, @StartOfDay time , @EndOfDay time)
returns 
 @TimeTable table (
              Dt datetime
            , DAY_MIN int
            , NIGHT_MIN int
            , WEEKDAY_MIN int
            , SAT_MIN int 
            , SUN_MIN int )

as
BEGIN 
      
      
      if @StartOfDay is null 
            set @StartOfDay = '08:00'

      if @EndOfDay is null 
            set @EndOfDay = '19:59:59.997'
      
      while @timein < @timeout 
            begin
                  insert into @TimeTable (dt, DAY_MIN, NIGHT_MIN, WEEKDAY_MIN, SAT_MIN, SUN_MIN ) 
                  values (@TimeIn
                              , case when convert(time, @TimeIn) between @StartOfDay and @EndOfDay then 1 else 0 end 
                              , case when convert(time, @TimeIn) between @StartOfDay and @EndOfDay then 0 else 1 end 
                              , case when datepart(dw, @TimeIn) between 2 and 6 then 1 else 0 end 
                              , case when datepart(dw, @TimeIn) = 7 then 1 else 0 end 
                              , case when datepart(dw, @TimeIn) = 1 then 1 else 0 end 
                              )
                  set @TimeIn = dateadd(minute, 1, @timein)
            end

            return --@Time_Table
END

Open in new window

select 
  convert(varchar(10), TimeIn , 121) as 'DAY'  
, convert(varchar(19),TimeIn , 121) 'TimeIn' 
, convert(varchar(19),TimeOut, 121) 'TimeOut'
, datediff(minute, timein, timeout) 'Duration'
, convert(dec(10,2), datediff(minute, timein, timeout)/60.0) 'HOURS' 
--, '...' 'LOCATION'
--, '...' 'NOTES'
, (select sum(DAY_MIN)		from dbo.time_table(timein, timeout, null, null )) as DayNet
, (select sum(night_MIN)	from dbo.time_table(timein, timeout, null, null )) as NightNet
--, (select sum(WEEKDAY_MIN)	from dbo.time_table(timein, timeout, null, null )) AS WeekdayNet
, (select sum(SAT_MIN)		from dbo.time_table(timein, timeout, null, null )) AS SatNet 
, (select sum(SUN_MIN)		from dbo.time_table(timein, timeout, null, null )) AS SunNet
--select * 
from TableA

Open in new window

ALTER VIEW myVIEW as 
select 
  convert(varchar(10), TimeIn , 121) as 'DAY'  
, convert(varchar(19),TimeIn , 121) 'TimeIn' 
, convert(varchar(19),TimeOut, 121) 'TimeOut'
, datediff(minute, timein, timeout) 'Duration'
, convert(dec(10,2), datediff(minute, timein, timeout)/60.0) 'HOURS' 
, '...' 'LOCATION'
, '...' 'NOTES'
, (select sum(DAY_MIN)		from dbo.time_table(timein, timeout, null, null )) as DayNet
, (select sum(night_MIN)	from dbo.time_table(timein, timeout, null, null )) as NightNet
--, (select sum(WEEKDAY_MIN)	from dbo.time_table(timein, timeout, '08:00', '17:00' )) AS WeekdayNet
, (select sum(SAT_MIN)		from dbo.time_table(timein, timeout, null, null )) AS SatNet 
, (select sum(SUN_MIN)		from dbo.time_table(timein, timeout, null, null )) AS SunNet
from TableA

Open in new window

select 
[DAY], [TimeIn], [TimeOut], [Duration], [HOURS], [LOCATION], [NOTES], [DayNet], [NightNet], [SatNet], [SunNet]
from myView

Open in new window