sunny-j
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
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
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
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
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
Since you have some times with minutes, but you are counting hours, how do you need to handle partial hours?
-GDG
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.
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_minute s_definiti on_here>
END AS NightNetMinutes
) As assign_alias_names
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_minute
END AS NightNetMinutes
) As assign_alias_names
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.
If this runs often or on large data sets, you might consider the option of creating a time table and a calendar table.
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
If this runs often or on large data sets, you might consider the option of creating a time table and a calendar table.
Sunny,
I just wanted to check back with you to see if you got what you needed.
-GDG_DBA
I just wanted to check back with you to see if you got what you needed.
-GDG_DBA
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
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
ASKER
by the way, I am very IMPRESSED with the way you have taken the time to construct this query for me..
Have you created the function, and is it working as you expect it to?
Some notes/questions:
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]
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]
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You mentioned "merging the code" into your view.
if you need a view:
then:
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
then:
select
[DAY], [TimeIn], [TimeOut], [Duration], [HOURS], [LOCATION], [NOTES], [DayNet], [NightNet], [SatNet], [SunNet]
from myView
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
ASKER
Such amazing response and a true IT Professional. The effort and detail that went into my question was quite simply astonishing.
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
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
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
select
[DAY], [TimeIn], [TimeOut], [Duration], [HOURS], [LOCATION], [NOTES], [DayNet], [NightNet], [SatNet], [SunNet]
from myView
If you would like to give me a little sample data to show what you are working with, I can help.
-gdg_dba