如何在sql中將周資料轉換為月資料？

我有一個每周作業小時數的表，我需要每月報告資料。對于大多數情況，周開始日期和結束日期在同一個月，這些記錄很容易匯總，但是當周開始日期和結束日期不在同一個月時，我需要打破該周的記錄成 2 行。一個以實際開始日期和月末作為結束日期，另一個記錄為下個月 1 日作為開始日期和實際結束日期作為結束日期。對于小時計算，我需要計算第一個月的天數，然后將總小時除以 5，然后乘以天數。對于第二條記錄，總小時數乘以 5，然后乘以（5 天數）

SELECT [USERNAME]
      ,[EMPLOYEE_NAME]
      ,[EFFORT_HRS]
      ,[TS_START_DATE]
      ,[TS_END_DATE]
FROM [dbo].[Source]

輸出：

預期結果：

uj5u.com熱心網友回復：

您沒有提供的是與假期相關的某種日歷，例如 2021 年 1 月 1 日（星期五）和 2021 年 1 月 2 日（星期六），這些日歷似乎沒有支付。話雖如此，我會讓你要么提供額外的資訊來排除這些日期，要么弄清楚如何調整查詢。

我還建議每天完成一個更好的時間存盤解決方案，以便于查詢和包含/排除，例如假期。但是，根據給出的內容，我有下面的示例來根據您的示例資料創建和填充表。

create table Source
( UserName nvarchar(5),
  Employee_Name nvarchar(5),
  Task nvarchar(10),
  Effort_Hrs numeric( 5, 2),
  TS_Start_Date datetime,
  TS_End_Date datetime )

insert into Source 
( UserName,
  Employee_Name,
  Task,
  Effort_Hrs,
  TS_Start_Date,
  TS_End_Date)
values
( 'mk', 'xyz', 'abcdefg', 40, '2021-12-27', '2022-01-02' ),
( 'mk',' xyz', 'defgh', 33.5, '2022-01-31', '2022-02-06' ),
( 'mk',' xyz', 'abcdefg', 6, '2022-04-25', '2022-05-01' )

現在是查詢本身。無論您根據現有資料做什么，都需要一個 UNION。基本上，在每個查詢中選擇相同的資料列。首先，獲取所有記錄一次，但如果開始日期和結束日期不同的月份，則將結束日期截斷到該月的最后一天。

UNION 部分將僅考慮開始日期和結束日期為不同月份的那些記錄。因此，如果您有 1 月 5 日至 1 月 11 日的作業周，您只會在沒有拆分的單個記錄中看到它，但在跨月的其他示例中，您會得到兩條記錄。第一個月一個，第二個月一個。

我已經根據您對作業日的反饋重寫了查詢，而忽略了周末的反饋。因此，在計算所賺取的工資時，我必須應用一個案例/當 PayDays = 0 時，只回傳 0 小時，否則你會得到除以零的錯誤

我正在添加額外的列（您可以將其洗掉），因此您可以看到這些部分是如何/在何處發揮作用的。現在，對于所有記錄，開始日期是最終輸出中開始日期的真實基礎。在第一個查詢中，它直截了當，它是開始日期。但是，在 UNION 部分查詢中，開始日期是一個月的第一天，但??同樣，只有在第二個查詢中月份不同。因此，為此，我將日期加（凈減）1 比結束日期的月份的日期小。

我還更改為使用 EOMONTH() 呼叫來計算給定日期的月末，例如在聯合的第一部分中，結束日期跨入下個月與最初使用的 dateadd() 相比。

對于查詢的 UNION 部分中的發薪日，僅表示跨入下個月的條目，PayDays 是結束日期 Days。所以 2 月 6 日是 6 天。

            NOT month( ts_start_Date ) = month( ts_end_Date ) 

這是一個計算您在給定時間段開始/結束日期內的作業日的函式，該函式強制回圈通過每一天以確定周一至周五。

CREATE Function dbo.WorkDaysInWeek
(   @pStartDate as datetime,
    @pEndDate as datetime)
RETURNS int
AS
BEGIN
    -- if bad start/end date because they passed in end date first, swap them
    if( @pStartDate > @pEndDate )
    begin
        declare @holdDate as DateTime
        set @holdDate = @pStartDate
        set @pStartDate = @pEndDate
        set @pEndDate = @holdDate
    end

    -- convert to just date to prevent false calculations on time consideration
    set @pStartDate = convert( date, @pStartDate)
    set @pEndDate = convert( date, @pEndDate )

    declare @workDays as int
    set @workDays = 0
    WHILE ( @pStartDate <= @pEndDate)
    BEGIN
        -- is the current day being tested a week day vs weekend.  Only count Mon-Fri
        if( datepart( weekday, @pStartDate) in ( 2, 3, 4, 5, 6 ))
            set @workDays = @workDays   1

        set @pStartDate = dateadd( day, 1, @pStartDate )
    END

    RETURN @workDays
end
GO

現在，您無需以天為單位計算 datediff，而是使用開始日期和結束日期呼叫該函式，該函式將分別回圈遍歷每一天以確定僅計算周一至周五

這是最后的查詢。

select
        AllWork.*,
        case when AllWork.PayDays = 0 
            then 0.0
            else ( AllWork.EFFORT_HRS / ( 1.0 * dbo.WorkDaysInWeek( AllWork.TS_Start_Date, AllWork.TS_End_Date))) * AllWork.PayDays end HoursInPayPeriod
    from
        (SELECT 
                USERNAME,
                EMPLOYEE_NAME,
                EFFORT_HRS,
                TS_START_DATE,
                TS_END_DATE,
                TS_START_DATE as RealStart,
                case when month( ts_start_Date ) = month( ts_end_Date )
                    then ts_end_date 
                    -- simplified date add to use build-in function EOMONTH (End of Month)
                    else EOMONTH( ts_start_Date, 0) end RealEnd,
                dbo.WorkDaysInWeek( TS_START_DATE, 
                                case when month( ts_start_Date ) = month( ts_end_Date )
                                    then ts_end_date 
                                    -- simplified date add to use build-in function EOMONTH (End of Month)
                                    else EOMONTH( ts_start_Date, 0) end ) PayDays
            FROM
                Source
        UNION
        -- union to get all entries where the end date is a new month from the start
        SELECT 
                USERNAME,
                EMPLOYEE_NAME,
                EFFORT_HRS,
                TS_START_DATE,
                TS_END_DATE,
                dateadd( day, 1 - datepart( day, ts_end_Date ), ts_end_Date ) RealStart,
                ts_end_date RealEnd,
                dbo.WorkDaysInWeek( dateadd( day, 1 - datepart( day, ts_end_Date ), ts_end_Date ),
                    ts_end_date ) PayDays
            FROM 
                Source
            where
                -- only care about those entries where the start and end date are different months
                NOT month( ts_start_Date ) = month( ts_end_Date ) 
        ) AllWork

標籤：sql sql服务器 sql-server-2008 sql-server-2012 sql-server-2008-r2

上一篇：使用setValueAt為單元格設定值似乎不起作用

下一篇：在Ubuntu上安裝大資料模塊的順序