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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply