問題描述

我們有一個表格，我想將該表格中的一行拆分為多行.如果你看到下面.那是我桌子上的一行.我們正在計算資源加載，我想將下面的這一行拆分到如下圖所示的結果表中.

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.

我將負責計算周開始日期和小時數，只需告訴我如何在 sql server 2005 中將一行拆分為多行.

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.

在這個例子中，大衛從 3 月 3 日到 3 月 19 日工作了 25 個小時.

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

這意味著如果我們將其除以工作周數，則為 2 周.

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

從 3 月 7 日開始的一周到 3 月 11 日結束的一周，然后是 3 月 14 日開始的一周到 3 月 18 日結束的一周.

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.

推薦答案

你可以做的是有一個像

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)

這篇關于sql如何將一行拆分為多行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！