February 11, 2012 at 4:40 pm
I have spent way too much time on this already to not have solved it by now, so I am turning to the experts for help!
Here are two, stripped down, tables I am working with:
CREATE TABLE WorkWeek
(
WrkWk_KeyDECIMAL(15,0),
WrkWk_Emp_KeyDECIMAL(15,0),
WrkWk_DurationINTEGER
)
INSERT INTO WorkWeek VALUES (1420110709, 14, 2400)
INSERT INTO WorkWeek VALUES (1420110723, 14, 460)
INSERT INTO WorkWeek VALUES (1420111015, 14, 2215)
CREATE TABLE WorkWeek_Details
(
WrkWkDtl_KeyDECIMAL(15,0),
WrkWkDtl_Business_DateSMALLDATETIME,
WrkWkDtl_DurationINTEGER
)
INSERT INTO WorkWeek_Details VALUES(1420110709, '2011-07-03', 0)
INSERT INTO WorkWeek_Details VALUES(1420110709, '2011-07-04', 0)
INSERT INTO WorkWeek_Details VALUES(1420110709, '2011-07-05', 0)
INSERT INTO WorkWeek_Details VALUES(1420110709, '2011-07-06', 0)
INSERT INTO WorkWeek_Details VALUES(1420110709, '2011-07-07', 0)
INSERT INTO WorkWeek_Details VALUES(1420110709, '2011-07-08', 0)
INSERT INTO WorkWeek_Details VALUES(1420110709, '2011-07-09', 0)
INSERT INTO WorkWeek_Details VALUES(1420110723, '2011-07-17', 0)
INSERT INTO WorkWeek_Details VALUES(1420110723, '2011-07-18', 0)
INSERT INTO WorkWeek_Details VALUES(1420110723, '2011-07-19', 0)
INSERT INTO WorkWeek_Details VALUES(1420111015, '2011-10-09', 0)
INSERT INTO WorkWeek_Details VALUES(1420111015, '2011-10-10', 0)
INSERT INTO WorkWeek_Details VALUES(1420111015, '2011-10-11', 0)
INSERT INTO WorkWeek_Details VALUES(1420111015, '2011-10-12', 0)
INSERT INTO WorkWeek_Details VALUES(1420111015, '2011-10-13', 0)
INSERT INTO WorkWeek_Details VALUES(1420111015, '2011-10-14', 0)
INSERT INTO WorkWeek_Details VALUES(1420111015, '2011-10-15', 0)
SELECT * FROM WorkWeek
SELECT * FROM WorkWeek_Details
DROP TABLE WorkWeek
DROP TABLE WorkWeek_Details
I want to take the WrkWk_Duration and spread it out for the days in the WorkWeek_Details table. I want to insert (up to) 480 minutes into each row of WorkWeek_Details until the sum of the details row for a given key adds up to the WrkWk_Duration.
I would like the output to look as follows:
[p]
1420110709 2011-07-03 480
1420110709 2011-07-04 480
1420110709 2011-07-05 480
1420110709 2011-07-06 480
1420110709 2011-07-07 480
1420110723 2011-07-17 460
1420111015 2011-10-09 480
1420111015 2011-10-10 480
1420111015 2011-10-11 480
1420111015 2011-10-12 480
1420111015 2011-10-13 295
[/p]
Inclusion of rows where WrkWkDtl_Duration = 0 is acceptable too.
Here is where I've gotten to so far and have finally thrown up my hands in defeat:
;WITH CTE (Business_Date, Natural_Key, Regular_Duration)
AS (
SELECT
TBL.WrkWkDtl_Business_Date AS Business_Date,
TBL.WrkWkDtl_Natural_Key AS Natural_Key,
TBL.WrkWkDtl_Regular_Duration AS Regular_Duration
FROM #WrkWk_Details TBL
INNER JOIN #WrkWk TBL1
ON TBL1.WrkWk_Natural_Key = TBL.WrkWkDtl_Natural_Key
UNION ALL
SELECT
TBL.WrkWkDtl_Business_Date AS Business_Date,
TBL.WrkWkDtl_Natural_Key AS Natural_Key,
Regular_Duration + 480 AS Regular_Duration -- Add 480 minutes
FROM CTE
INNER JOIN #WrkWk_Details TBL
ON TBL.WrkWkDtl_Natural_Key = CTE.Natural_Key
AND TBL.WrkWkDtl_Business_Date = CTE.Business_Date
INNER JOIN #WrkWk TBL1
ON TBL1.WrkWk_Natural_Key = TBL.WrkWkDtl_Natural_Key
WHERE CTE.Regular_Duration < TBL1.WrkWk_Wk_Duration
)
select * from CTE
Thank you for your help in advance!
February 11, 2012 at 5:07 pm
Are you looking for something along those lines?
;
WITH cte AS
(
SELECT
wd.*,
ww.WrkWk_Duration,
ROW_NUMBER() OVER(PARTITION BY WrkWk_Key ORDER BY WrkWkDtl_Business_Date ) row
FROM WorkWeek_Details wd
INNER JOIN WorkWeek ww ON wd.WrkWkDtl_Key=ww.WrkWk_Key
)
SELECT
*,
CASE
WHEN (WrkWk_Duration -(row * 480))<0 AND (WrkWk_Duration -(row * 480))>-480 THEN WrkWk_Duration % 480
WHEN (WrkWk_Duration -(row * 480))<=-480 THEN 0
ELSE 480
END
FROM cte
February 12, 2012 at 7:55 am
Thank you both for the replies. I'll have to try this on Monday when I get back to my desk.
@Celcko: I am working on a stored proc that gets called from a parent stored proc and am passed a #Table with all of the information I need for MY proc.
Some further details:
WrkWk_Duration will generally be some multiple of 480. ~0 to 3360 where 3360 represents seven 8 hour days in a work week.
There may already be WorkWeek_Details with time allotted and I strip those out and reduce the WrkWk_Duration by those amounts.
For instance, someone could have worked 540 minutes on the first day of a 3360 minute week, leaving 2820 for the WrkWk_Duration amount to be distributed.
The amount I am left with in the WrkWk_Duration column needs to be spread out over the days in WorkWeek_Details.
I didn't add the full seven days for each WrkWkDtl_Key just out of laziness but normally they would be there.
Thank you again!
February 12, 2012 at 10:13 am
Joe, can you please clarify how the check constraint
CHECK (week_date LIKE '[12][0-9][0-9][0-9]-W[0-5][0-9]-[1-7]'),
would ever work on the data type specified (week_date CHAR(1)) ?
I expect that's just a typo and is supposed to be CHAR(10)...
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply