November 8, 2010 at 2:45 pm
Hi,
Is there an easier way to select number of hours worked between 23:00 on Fridays and 07:00 Mondays over the last 12 months from a table?
November 8, 2010 at 3:01 pm
Answer: Yes.
Next Question: How?
Next Answer: Please post DDL and sample data. See the first link in my signature for more help if you need it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2010 at 4:47 pm
@Craig: +1
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 9, 2010 at 1:49 pm
This is the DDL, but didn't know where to go after that with the below code:
CREATE TABLE #EETime
(TimeID INT
,PersonName VARCHAR(200)
,PersonID VARCHAR(10)
,EEDatedatetime
,StartTimedatetime
,EndTimedatetime
,Duration float
,code VARCHAR(100))
INSERT INTO #EETime
Select 40263,'David Smith','ds2563','2010-02-06 00:00','2010-02-06 23:00','2010-02-07 03:15',4.25,NULL UNION
Select 40395,'David Smith','ds2563','2/6/2010 12:00 AM','2/7/2010 3:45 AM','2/7/2010 8:00 AM',4.25,NULL UNION
Select 41994,'David Smith','ds2563','2/7/2010 12:00 AM','2/7/2010 11:00 PM','2/8/2010 4:15 AM',5.25,NULL UNION
Select 42155,'David Smith','ds2563','2/7/2010 12:00 AM','2/8/2010 4:30 AM','2/8/2010 8:45 AM',4.25,NULL UNION
Select 46067,'David Smith','ds2563','2/8/2010 12:00 AM','2/8/2010 11:00 PM','2/9/2010 2:30 AM',3.50,NULL UNION
Select 46173,'David Smith','ds2563','2/8/2010 12:00 AM','2/9/2010 3:00 AM','2/9/2010 8:15 AM',5.25,NULL UNION
Select 54921,'David Smith','ds2563','2/10/2010 12:00 AM','2/10/2010 11:00 PM','2/11/2010 4:00 AM',5.00,NULL UNION
Select 55108,'David Smith','ds2563','2/10/2010 12:00 AM','2/11/2010 4:15 AM','2/11/2010 8:30 AM',4.25,NULL UNION
Select 58977,'David Smith','ds2563','2/11/2010 12:00 AM','2/11/2010 11:15 PM','2/12/2010 2:30 AM',3.25,NULL UNION
Select 58978,'David Smith','ds2563','2/11/2010 12:00 AM','2/12/2010 3:00 AM','2/12/2010 8:00 AM',5.00,NULL UNION
Select 61873,'David Smith','ds2563','2/12/2010 12:00 AM','2/12/2010 11:15 PM','2/13/2010 1:45 AM',2.50,NULL UNION
Select 61946,'David Smith','ds2563','2/12/2010 12:00 AM','2/13/2010 2:00 AM','2/13/2010 8:15 AM',6.25,NULL UNION
Select 68529,'David Smith','ds2563','2/13/2010 12:00 AM',NULL, NULL ,8.00,'Sick' UNION
Select 74765,'David Smith','ds2563','2/15/2010 12:00 AM','2/15/2010 11:00 PM','2/16/2010 4:00 AM',5.00,NULL UNION
Select 74766,'David Smith','ds2563','2/15/2010 12:00 AM','2/16/2010 4:15 AM','2/16/2010 8:30 AM',4.25,NULL UNION
Select 75036,'David Smith','ds2563','2/16/2010 12:00 AM','2/16/2010 11:00 PM','2/17/2010 4:15 AM',5.25,NULL UNION
Select 75037,'David Smith','ds2563','2/16/2010 12:00 AM','2/17/2010 4:45 AM','2/17/2010 8:00 AM',3.25,NULL UNION
Select 80013,'David Smith','ds2563','2/18/2010 12:00 AM','2/18/2010 11:00 PM','2/19/2010 1:45 AM',2.75,NULL UNION
Select 80130,'David Smith','ds2563','2/18/2010 12:00 AM','2/19/2010 2:15 AM','2/19/2010 8:30 AM',6.25,NULL UNION
Select 85628,'David Smith','ds2563','2/20/2010 12:00 AM','2/20/2010 11:00 PM','2/21/2010 2:45 AM',3.75,NULL UNION
Select 85767,'David Smith','ds2563','2/20/2010 12:00 AM','2/21/2010 4:00 AM','2/21/2010 7:45 AM',3.75,NULL UNION
Select 87606,'David Smith','ds2563','2/21/2010 12:00 AM','2/21/2010 11:00 PM','2/22/2010 1:30 AM',2.50,NULL UNION
Select 87682,'David Smith','ds2563','2/21/2010 12:00 AM','2/22/2010 2:00 AM','2/22/2010 7:45 AM',5.75,NULL UNION
Select 94115,'David Smith','ds2563','2/22/2010 12:00 AM','2/22/2010 11:00 PM','2/23/2010 4:30 AM',5.50,NULL UNION
Select 94116,'David Smith','ds2563','2/22/2010 12:00 AM','2/23/2010 5:00 AM','2/23/2010 8:45 AM',3.75,NULL UNION
Select 95528,'David Smith','ds2563','2/23/2010 12:00 AM','2/23/2010 11:00 PM','2/24/2010 4:00 AM',5.00,NULL UNION
Select 95616,'David Smith','ds2563','2/23/2010 12:00 AM','2/24/2010 4:30 AM','2/24/2010 8:00 AM',3.50,NULL UNION
Select 100327,'David Smith','ds2563','2/24/2010 12:00 AM','2/24/2010 11:00 PM','2/25/2010 4:30 AM',5.50,NULL UNION
Select 100514,'David Smith','ds2563','2/24/2010 12:00 AM','2/25/2010 5:00 AM','2/25/2010 8:00 AM',3.00,NULL UNION
Select 102551,'David Smith','ds2563','2/24/2010 12:00 AM','2/25/2010 8:45 AM','2/25/2010 8:45 AM',0.00,NULL UNION
Select 104786,'David Smith','ds2563','2/25/2010 12:00 AM','2/25/2010 11:15 PM','2/26/2010 2:15 AM',3.00,NULL UNION
Select 104839,'David Smith','ds2563','2/25/2010 12:00 AM','2/26/2010 2:30 AM','2/26/2010 8:00 AM',5.50,NULL UNION
Select 107990,'David Smith','ds2563','2/26/2010 12:00 AM','2/26/2010 11:00 PM','2/27/2010 1:45 AM',2.75,NULL UNION
Select 108062,'David Smith','ds2563','2/26/2010 12:00 AM','2/27/2010 2:00 AM','2/27/2010 8:00 AM',6.00,NULL UNION
Select 109974,'David Smith','ds2563','2/27/2010 12:00 AM','2/27/2010 11:15 PM','2/28/2010 4:15 AM',5.00,NULL UNION
Select 110130,'David Smith','ds2563','2/27/2010 12:00 AM','2/28/2010 4:30 AM','2/28/2010 7:45 AM',3.25,NULL UNION
Select 39173,'Joy Mike','mj5619','2/6/2010 12:00 AM','2/6/2010 8:00 AM','2/6/2010 3:00 PM',7.00,NULL UNION
Select 39865,'Joy Mike','mj5619','2/6/2010 12:00 AM','2/6/2010 3:30 PM','2/6/2010 7:30 PM',4.00,NULL UNION
Select 40437,'Joy Mike','mj5619','2/7/2010 12:00 AM','2/7/2010 5:30 AM','2/7/2010 1:00 PM',7.50,NULL UNION
Select 41296,'Joy Mike','mj5619','2/7/2010 12:00 AM','2/7/2010 1:30 PM','2/7/2010 5:30 PM',4.00,NULL UNION
Select 46294,'Joy Mike','mj5619','2/9/2010 12:00 AM','2/9/2010 5:30 AM','2/9/2010 12:45 PM',7.25,NULL UNION
Select 48578,'Joy Mike','mj5619','2/9/2010 12:00 AM','2/9/2010 1:15 PM','2/9/2010 3:00 PM',1.75,NULL UNION
Select 50341,'Joy Mike','mj5619','2/10/2010 12:00 AM','2/10/2010 5:30 AM','2/10/2010 12:45 PM',7.25,NULL UNION
Select 52690,'Joy Mike','mj5619','2/10/2010 12:00 AM','2/10/2010 1:15 PM','2/10/2010 2:45 PM',1.50,NULL UNION
Select 60488,'Joy Mike','mj5619','2/12/2010 12:00 AM','2/12/2010 11:45 AM','2/12/2010 3:00 PM',3.25,NULL UNION
Select 61231,'Joy Mike','mj5619','2/12/2010 12:00 AM','2/12/2010 3:30 PM','2/12/2010 8:00 PM',4.50,NULL UNION
Select 68528,'Joy Mike','mj5619','2/13/2010 12:00 AM',NULL ,NULL ,8.00,'Sick' UNION
Select 65863,'Joy Mike','mj5619','2/15/2010 12:00 AM','2/15/2010 5:30 AM','2/15/2010 1:00 PM',7.50,NULL UNION
Select 74785,'Joy Mike','mj5619','2/15/2010 12:00 AM','2/15/2010 1:45 PM','2/15/2010 3:00 PM',1.25,NULL UNION
Select 75055,'Joy Mike','mj5619','2/16/2010 12:00 AM','2/16/2010 4:15 PM','2/16/2010 8:15 PM',4.00,NULL UNION
Select 74786,'Joy Mike','mj5619','2/16/2010 12:00 AM','2/16/2010 12:15 PM','2/16/2010 3:45 PM',3.50,NULL UNION
Select 76373,'Joy Mike','mj5619','2/18/2010 12:00 AM','2/18/2010 5:45 AM','2/18/2010 10:30 AM',4.75,NULL UNION
Select 78158,'Joy Mike','mj5619','2/18/2010 12:00 AM','2/18/2010 11:00 AM','2/18/2010 3:45 PM',4.75,NULL UNION
Select 84480,'Joy Mike','mj5619','2/20/2010 12:00 AM','2/20/2010 8:15 AM','2/20/2010 3:45 PM',7.50,NULL UNION
Select 85258,'Joy Mike','mj5619','2/20/2010 12:00 AM','2/20/2010 4:15 PM','2/20/2010 7:30 PM',3.25,NULL UNION
Select 85803,'Joy Mike','mj5619','2/21/2010 12:00 AM','2/21/2010 5:15 AM','2/21/2010 1:15 PM',8.00,NULL UNION
Select 86787,'Joy Mike','mj5619','2/21/2010 12:00 AM','2/21/2010 1:45 PM','2/21/2010 6:30 PM',4.75,NULL UNION
Select 94069,'Joy Mike','mj5619','2/23/2010 12:00 AM','2/23/2010 5:30 AM','2/23/2010 12:45 PM',7.25,NULL UNION
Select 94391,'Joy Mike','mj5619','2/23/2010 12:00 AM','2/23/2010 1:15 PM','2/23/2010 2:30 PM',1.25,NULL UNION
Select 102768,'Joy Mike','mj5619','2/24/2010 12:00 AM','2/24/2010 6:30 AM','2/24/2010 12:00 PM',5.50,NULL UNION
Select 102769,'Joy Mike','mj5619','2/24/2010 12:00 AM','2/24/2010 12:30 PM','2/24/2010 2:30 PM',2.00,NULL UNION
Select 100524,'Joy Mike','mj5619','2/25/2010 12:00 AM','2/25/2010 5:00 AM','2/25/2010 1:00 PM',8.00,NULL UNION
Select 103647,'Joy Mike','mj5619','2/25/2010 12:00 AM','2/25/2010 1:30 PM','2/25/2010 2:45 PM',1.25,NULL UNION
Select 106227,'Joy Mike','mj5619','2/26/2010 12:00 AM','2/26/2010 9:00 AM','2/26/2010 12:15 PM',3.25,NULL UNION
Select 106873,'Joy Mike','mj5619','2/26/2010 12:00 AM','2/26/2010 12:45 PM','2/26/2010 6:30 PM',5.75,NULL UNION
Select 35943,'Anderson Kaye','ak5285','2/5/2010 12:00 AM','2/5/2010 6:30 AM','2/5/2010 12:30 PM',6.00,NULL UNION
Select 37386,'Anderson Kaye','ak5285','2/5/2010 12:00 AM','2/5/2010 1:00 PM','2/5/2010 2:30 PM',1.50,NULL UNION
Select 43963,'Anderson Kaye','ak5285','2/6/2010 12:00 AM','2/6/2010 6:30 AM','2/6/2010 1:30 PM',7.00,NULL UNION
Select 43964,'Anderson Kaye','ak5285','2/6/2010 12:00 AM','2/6/2010 2:00 PM','2/6/2010 3:00 PM',1.00,NULL UNION
Select 40657,'Anderson Kaye','ak5285','2/7/2010 12:00 AM','2/7/2010 6:45 AM','2/7/2010 1:30 PM',6.75,NULL UNION
Select 41331,'Anderson Kaye','ak5285','2/7/2010 12:00 AM','2/7/2010 2:00 PM','2/7/2010 2:45 PM',0.75,NULL UNION
Select 42503,'Anderson Kaye','ak5285','2/8/2010 12:00 AM','2/8/2010 6:45 AM','2/8/2010 1:00 PM',6.25,NULL UNION
Select 44737,'Anderson Kaye','ak5285','2/8/2010 12:00 AM','2/8/2010 1:30 PM','2/8/2010 2:30 PM',1.00,NULL UNION
Select 46813,'Anderson Kaye','ak5285','2/9/2010 12:00 AM','2/9/2010 7:15 AM','2/9/2010 2:45 PM',7.00,NULL UNION
Select 93455,'Anderson Kaye','ak5285','2/13/2010 12:00 AM',NULL ,NULL ,8.00,'Sick' UNION
Select 93456,'Anderson Kaye','ak5285','2/14/2010 12:00 AM',NULL ,NULL ,8.00,'Sick' UNION
Select 66076,'Anderson Kaye','ak5285','2/15/2010 12:00 AM','2/15/2010 6:45 AM','2/15/2010 1:00 PM',6.25,NULL UNION
Select 74863,'Anderson Kaye','ak5285','2/15/2010 12:00 AM','2/15/2010 1:30 PM','2/15/2010 2:30 PM',1.00,NULL UNION
Select 75132,'Anderson Kaye','ak5285','2/16/2010 12:00 AM','2/16/2010 1:30 PM','2/16/2010 2:45 PM',1.25,NULL UNION
Select 74865,'Anderson Kaye','ak5285','2/16/2010 12:00 AM','2/16/2010 6:45 AM','2/16/2010 1:00 PM',6.25,NULL UNION
Select 93456,'Anderson Kaye','ak5285','2/18/2010 12:00 AM',NULL ,NULL ,8.00,'Sick' UNION
Select 81015,'Anderson Kaye','ak5285','2/19/2010 12:00 AM','2/19/2010 7:15 AM','2/19/2010 2:45 PM',7.00,NULL UNION
Select 84061,'Anderson Kaye','ak5285','2/20/2010 12:00 AM','2/20/2010 6:45 AM','2/20/2010 2:45 PM',7.50,NULL UNION
Select 85961,'Anderson Kaye','ak5285','2/21/2010 12:00 AM','2/21/2010 6:30 AM','2/21/2010 2:45 PM',7.75,NULL UNION
Select 93230,'Anderson Kaye','ak5285','2/22/2010 12:00 AM','2/22/2010 6:45 AM','2/22/2010 2:30 PM',7.25,NULL UNION
Select 94152,'Anderson Kaye','ak5285','2/23/2010 12:00 AM','2/23/2010 6:45 AM','2/23/2010 2:45 PM',7.50,NULL UNION
Select 101023,'Anderson Kaye','ak5285','2/25/2010 12:00 AM','2/25/2010 6:30 AM','2/25/2010 1:30 PM',7.00,NULL UNION
Select 103865,'Anderson Kaye','ak5285','2/25/2010 12:00 AM','2/25/2010 2:15 PM','2/25/2010 2:45 PM',0.50,NULL UNION
Select 105503,'Anderson Kaye','ak5285','2/26/2010 12:00 AM','2/26/2010 7:00 AM','2/26/2010 12:30 PM',5.50,NULL UNION
Select 106931,'Anderson Kaye','ak5285','2/26/2010 12:00 AM','2/26/2010 1:00 PM','2/26/2010 2:45 PM',1.75,NULL
select * from #EETime order by PersonID
select
dateadd(dd,days,starttime), datename(dw,dateadd(dd,days,endtime))
from #EETime,
(select top 365 colorder - 1 as days from master..syscolumns where id = -519536829 order by colorder) x
where datediff(dd,dateadd(dd,days,starttime),endtime) >= 0
November 9, 2010 at 4:46 pm
Alright, this is a bit chewy. This code works under the following conditions:
- Start or end time falls between Friday 23:00 and Monday 7:00.
- Noone works longer than 12 hour shifts. It's not prepared to handle a Thursday start with a Saturday End, or a Sunday start with a Tuesday end. It can be modified to do that but complexity was getting overkill.
SET DATEFIRST 5 --Friday. Don't worry, it's not a permanent change.
;WITH cte AS (
SELECT
*,
CASE DATEPART(dw, starttime)
WHEN 1 THEN CASE WHEN DATEPART( hh, starttime) >= 23 THEN starttime ELSE dateadd( hh, 23, DATEADD( dd, DATEDIFF( dd, 0, starttime) - DATEPART(dw,starttime) +1, 0)) END
WHEN 2 THEN starttime
WHEN 3 THEN starttime
WHEN 4 THEN CASE WHEN DATEPART( hh, starttime) <=7 THEN starttime ELSE dateadd( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, starttime) - DATEPART(dw,starttime) +4, 0)) END
ELSE NULL
END AS ModifiedStartTime,
CASE DATEPART(dw, endtime)
WHEN 1 THEN CASE WHEN DATEPART( hh, endtime) >= 23 THEN endtime ELSE dateadd( hh, 23, DATEADD( dd, DATEDIFF( dd, 0, endtime) - DATEPART(dw,endtime)+ 1, 0)) END
WHEN 2 THEN endtime
WHEN 3 THEN endtime
WHEN 4 THEN CASE WHEN DATEPART( hh, endtime) <=7 THEN endtime ELSE dateadd( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, endtime) - DATEPART(dw,endtime) +4, 0)) END
ELSE NULL
END AS ModifiedEndTime
FROM#EETime
WHERE
-- First, we only get records within our rules.
starttime between
dateadd( hh, 23, DATEADD( dd, DATEDIFF( dd, 0, starttime) - DATEPART(dw,starttime) + 1, 0)) -- Friday at 23:00
AND dateadd( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, starttime) - DATEPART(dw,starttime) +4, 0)) -- Monday at 7:00
OR EndTime between
dateadd( hh, 23, DATEADD( dd, DATEDIFF( dd, 0, EndTime) - DATEPART(dw,EndTime) + 1, 0)) -- Friday at 23:00
AND dateadd( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, EndTime) - DATEPART(dw,EndTime) +4, 0)) -- Monday at 7:00
)
SELECT *, DATEDIFF( n, ModifiedStartTime, ModifiedEndTime) AS WeekendTimeWorkedInMinutes FROM cte
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 11, 2010 at 6:53 am
Hey Craig this was very slick and complex (not sure what you did in the case statement)... thanks.
How would I add the codes in this query. For example, Anderson Kaye has two codes on the weekend which uses EEdate column (2/13/2010 and 2/14/2010).
November 11, 2010 at 11:20 am
ejbatu1 (11/11/2010)
Hey Craig this was very slick and complex (not sure what you did in the case statement)... thanks.How would I add the codes in this query. For example, Anderson Kaye has two codes on the weekend which uses EEdate column (2/13/2010 and 2/14/2010).
EJ,
Not sure what you mean to add the codes. The codes should be returning on a per line basis because of the select * I included.
Did you need to do something else with them, or just get them to output?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 11, 2010 at 12:55 pm
If you do: select * from #EETime order by PersonID the last column is code, which is not part of the StartTime and EndTime.
TimeID PersonName PersonID EEDate StartTime EndTime Duration code
93455Anderson Kaye ak5285 2010-02-13 NULL NULL 8 Sick
93456Anderson Kaye ak5285 2010-02-14 NULL NULL 8 Sick
93456Anderson Kaye ak5285 2010-02-18NULL NULL 8 Sick
Therefor, I would like the script to select the sick code for 2/13/2010 and 2/14/2010 since dates are weekend dates.
November 11, 2010 at 2:10 pm
After this where clause in the cte...
WHERE
-- First, we only get records within our rules.
starttime between
dateadd( hh, 23, DATEADD( dd, DATEDIFF( dd, 0, starttime) - DATEPART(dw,starttime) + 1, 0)) -- Friday at 23:00
AND dateadd( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, starttime) - DATEPART(dw,starttime) +4, 0)) -- Monday at 7:00
OR EndTime between
dateadd( hh, 23, DATEADD( dd, DATEDIFF( dd, 0, EndTime) - DATEPART(dw,EndTime) + 1, 0)) -- Friday at 23:00
AND dateadd( hh, 7, DATEADD( dd, DATEDIFF( dd, 0, EndTime) - DATEPART(dw,EndTime) +4, 0)) -- Monday at 7:00
Add this:
OR DATEPART( dw, EEdate) between 2 and 3
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 11, 2010 at 2:16 pm
Very nice! Thank you.
November 12, 2010 at 9:08 pm
Ejbatiu1,
I would handle this sort of thing with a calendar table and let the table to most of the work. It would make things easier if for some reason someone decided to change the week end to 6 AM on Saturday or something like that.
My hat is off to Craig for coming up with such an elegant query to handle this, but I still prefer using calendar tables for easier maintenance on this type of thing.
Here is a simple calendar table for each week from the start of 2009 through the end of 2010:
CREATE TABLE CalWk
( WkID INT IDENTITY(1, 1)
, WkStart DATETIME
, WkEnd DATETIME
, WkEndStart DATETIME
)
GO
DECLARE
@WkStart DATETIME
, @WkEnd DATETIME
, @WkEndStart DATETIME
-- Week officially starts at 7 AM Mondays
SELECT
@WkStart = '12/29/2008 7:00:00'
, @WkEnd = DATEADD(day, 7, @WkStart) -- Week ends 7 days later
, @WkEndStart = '1/2/2009 23:00:00' -- Weekend starts 11 PM on Friday.
-- 2 years worth of data.
WHILE @WkStart < '1/3/2011'
BEGIN
INSERT INTO CalWk
( WkStart, WkEnd, WkEndStart )
VALUES
( @WkStart, @WkEnd, @WkEndStart )
SELECT @WkStart = DATEADD(day, 7, @WkStart)
, @WkEnd = DATEADD(day, 7, @WkEnd)
, @WkEndStart = DATEADD(day, 7, @WkEndStart)
END
Now here is a simplified set of CTE's to get both detail and weekly summary of the hours worked between 11 PM on Friday to 7 AM on Monday. If the shift started before 11 PM then only the time from 11 PM to the end is counted. Same for the shift end - only the time up to 7 AM Monday is counted. You would have to tweak the CTE's to only include weeks from 52 weeks ago up to the present, which shouldn't be too hard:
; WITH CTEDtl AS
( SELECT CW.WkID, E.TimeID, CW.WkStart, E.PersonID, E.StartTime, E.EndTime
, DATEDIFF(minute,
CASE WHEN E.StartTime < CW.WkEndStart THEN CW.WkEndStart ELSE E.StartTime END
, CASE WHEN E.EndTime > CW.WkEnd THEN CW.WkEnd ELSE E.EndTime END) AS MinutesWorked
FROM #EETime E
INNER JOIN CalWk CW ON
(E.StartTime >= CW.WkEndStart
AND E.StartTime < CW.WkEnd)
OR (E.EndTime > CW.WkEndStart
AND E.EndTime < CW.WkEnd)
)
, CTEWkSum AS
( SELECT WkID, WkStart, SUM(MinutesWorked) AS MinutesWorked
FROM CTEDtl
GROUP BY WkId, WkStart
)
I woke up this morning and realized the first post of the CTE would return duplicates using a UNION ALL. The above should not select anything twice.
Here are 2 queries - one for each CTE - showing detail and then weekly summary:
-- Detail Query
SELECT *, (MinutesWorked * 1.0 / 60) AS HoursWorked
FROM CTEDtl
-- Week Summary query
SELECT *, (MinutesWorked * 1.0 / 60) AS HoursWorked
FROM CTEWkSum
Todd Fifield
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply