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
sunny-jAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

G GodwinDatabase AdministratorCommented:
Sure.

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

-gdg_dba
0
sunny-jAuthor Commented:
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
0
G GodwinDatabase AdministratorCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sunny-jAuthor Commented:
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.
0
Scott PletcherSenior DBACommented:
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
0
G GodwinDatabase AdministratorCommented:
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.
0
G GodwinDatabase AdministratorCommented:
Sunny,

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

-GDG_DBA
0
sunny-jAuthor Commented:
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
0
sunny-jAuthor Commented:
by the way, I am very IMPRESSED with the way you have taken the time to construct this query for me..
0
G GodwinDatabase AdministratorCommented:
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]
0
sunny-jAuthor Commented:
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.
0
G GodwinDatabase AdministratorCommented:
Try this:

First you will need to change the FN to support the SAT/SUN split:
ALTER function TIME_TABLE (@TimeIn Datetime, @TimeOut datetime, @StartOfDay time = '08:00', @EndOfDay time = '20:00' )
returns 
 @TimeTable table (
		 -- id int identity(1,1)	, 
		  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 = '20:00'
	
	while @timein < @timeout 
		begin
			insert into @TimeTable (dt, DAY_MIN, NIGHT_MIN, WEEKDAY_MIN, SAT_MIN, SUN_MIN ) 
			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) = 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


Then you will be able to use this query:

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
G GodwinDatabase AdministratorCommented:
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

0
sunny-jAuthor Commented:
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
0
sunny-jAuthor Commented:
Such amazing response and a true IT Professional. The effort and detail that went into my question was quite simply astonishing.
0
G GodwinDatabase AdministratorCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.