sql how to split a row into multiple rows

问题:

We have a table and I want to split a row in that table into multiple rows. If you see below. That is one row from my table. We are calculating resource loading and I want to split this row below into the result table also shown in the image below.

I will take care of calculating the week start date and hours, just tell me how can I split a row into multiple rows in sql server 2005.

Here in this example David worked from march 3rd to march 19 for 25 hours.

Which means if we divide this by number of weeks worked, comes to 2 weeks.

From week starting march 7 to week ending march 11, and then week starting march 14th to week end march 18th.

The hours are split based on number of weeks worked in ratio of number of days worked.

回答1:

What you can do is have a table with week entries like

CREATE TABLE WeeKDays ( WeekStartDate date, WeekEndDate date);

Now JOIN your table with this one like

SELECT 
 Task_ID,Name, WeekStartDate, WeekEndDate
-- Add logic for splitting hours
, (hours/workdays)* 
 CASE 
     WHEN  (T.StartDate >= W.WeekStartDate AND T.StartDate <= W.WeekEndDate) THEN DATEDIFF(d,T.StartDate,W.WeekEndDate)
     WHEN  (T.EndDate >= W.WeekStartDate AND T.EndDate <= W.WeekEndDate) THEN DATEDIFF(d,W.WeekStartDate,T.StartDate)
     WHEN  (T.StartDate <W.WeekStartDate AND T.EndDate>W.WeeKEndDate) THEN 7
--assuming 7 working days
END as Hours

FROM Tasks T LEFT JOIN WeekDays W ON 
(T.StartDate >= W.WeekStartDate AND T.StartDate <= W.WeekEndDate)  
 OR (T.EndDate >= W.WeekStartDate AND T.EndDate <= W.WeekEndDate) 
 OR (T.StartDate <W.WeekStartDate AND T.EndDate>W.WeeKEndDate)