I have a table that has weekly effort hrs and I need to report data on a monthly basis. for most of the cases, the week start date and end date lie in the same month and those records are easy to aggregate but when the week start date and end date don't lie in the same month then I need to break that week's records into 2 rows. One with an actual start date and end of the month as the end date and another record as 1st of next month as the start date and actual end date as the end date. For the hrs calculation, I need to calculate the number of days that fall in the first month then divide the total hrs by 5 and then multiply by the number of days. and for the second record, it will be total hrs by 5 and then multiply by (5-number of days)

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

OUTPUT:

Expected Result:

CodePudding user response：

What you are NOT providing is some sort of calendar associated with holidays such as Jan 1, 2021 (Friday), and Jan 2, 2021 (Saturday) which do not appear to be paid out. That being said, I will leave you to either provide additional information to exclude such dates, or figure out how to adjust the query.

I would also suggest a better storage solution for time be done on a daily basis for easier querying and inclusion / exclusion such as holidays. But, from what is given I have below sample to create and populate table per your sample data.

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' )

And now the query itself. No matter what you do based of the existing data, you will need a UNION. Basically, selecting the same columns of data in each query. First, get all records once, but chop-off the end date to the last of the month if the start and end dates are different months.

The UNION part will only consider those records where the start and end dates are DIFFERENT months. So, if you had a work week of Jan 5 - Jan 11, you would only see it in the single record with no split, but in your other examples that cross months, you get TWO records. One for the first month, one for the second.

I have rewritten the query based on your feedback of week days and ignoring that of weekends. As such, when computing the pay earned, I had to apply a case/when if PayDays = 0, to just return 0 hours, otherwise you would get a divide by zero error

I am adding extra columns (which you can remove), so you can see how / where the pieces come into play. Now, for ALL records, the start date IS the real basis of the start date in final output. In the first query, its straight-forward, it IS the start date. However, in the UNION portion query, the start date is the first of the month, but again, only in the second query the months are different. So for that, I am doing date add (net subtract) 1 less than the day of the month of the ending date.

I also changed to use EOMONTH() call to compute the end of month for a given date such as in the first part of union where the end date crosses into following month vs the dateadd() originally used.

For the pay days in the UNION part of the query, which only represents entries that cross into the following month, the PayDays IS the ending date Days. So Feb 6th would be 6 days.

            NOT month( ts_start_Date ) = month( ts_end_Date ) 

Here is a function to compute your work days within a given time period begin/end dates that forces cycle through each individual day to determine Mon-Fri.

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

Now, instead of computing the datediff in days, you call the function with begin and end dates and the function will cycle through each day individually to determine Mon-Fri only to be counted

Here is the final query.

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