SQL populate total working days per month minus ba

问题:

I am after a view which will look like my first attached picture however with right hand column populated and not blank. The logic is as follows:

The data must be for current financial period. Therfore April will be 2011 and March will be 2012 and so on.

The calculation for Days Available for the single months will be:

Total number of working days (Monday-Friday) minus any bank holidays that fall into that particular month, for that particular financial year (Which we have saved in a table - see second image).

Column names for holiday table left to right: holidaytypeid, name, holstart, holend. Table name: holidaytable

To work out the cumulative months 'Days Available' it will be a case of summing already populated data for the single months. E.g April-May will be April and May's data SUMMED and so on and so forth.

I need the SQL query in perfect format so that this can be pasted straight in and will work (i.e with the correct column names and table names)

Thanks for looking.

回答1:

DECLARE @StartDate DATETIME, @EndDate DATETIME

SELECT  @StartDate = '01/04/2011',
        @EndDate = '31/03/2012'

CREATE TABLE #Data (FirstDay DATETIME NOT NULL PRIMARY KEY, WorkingDays INT NOT NULL)

;WITH DaysCTE ([Date]) AS
(   SELECT  @StartDate
    UNION ALL
    SELECT  DATEADD(DAY, 1, [Date])
    FROM    DaysCTE
    WHERE   [Date] <= @Enddate
)

INSERT INTO #Data
SELECT  MIN([Date]),
        COUNT(*) [Day]
FROM    DaysCTE
        LEFT JOIN HolidayTable
            ON [Date] BETWEEN HolStart AND HolEnd
WHERE   HolidayTypeID IS NULL
AND     DATENAME(WEEKDAY, [Date]) NOT IN ('Saturday', 'Sunday')
GROUP BY DATEPART(MONTH, [Date]), DATEPART(YEAR, [Date])
OPTION (MAXRECURSION 366)

DECLARE @Date DATETIME
SET @Date = (SELECT MIN(FirstDay) FROM #Data)

SELECT  Period,
        WorkingDays [Days Available (Minus the Holidays)]
FROM    (   SELECT  DATENAME(MONTH, Firstday) [Period],
                    WorkingDays,
                    0 [SortField],
                    FirstDay
            FROM    #Data
            UNION
            SELECT  DATENAME(MONTH, @Date) + ' - ' + DATENAME(MONTH, Firstday),
                    (   SELECT  SUM(WorkingDays)
                        FROM    #Data b
                        WHERE   b.FirstDay <= a.FirstDay
                    ) [WorkingDays],
                    1 [SortField],
                    FirstDay 
            FROM    #Data a
            WHERE   FirstDay > @Date
        ) data
ORDER BY SortField, FirstDay

DROP TABLE #Data

If you do this for more than 1 year you will need to change the line:

OPTION (MAXRECURSION 366)

Otherwise you'll get an error - The number needs to be higher than the number of days you are querying.

---

EDIT

I have just come accross this old answer of mine and really don't like it, there are so many things that I now consider bad practise, so am going to correct all the issues:

1. I did not terminate statements with a semi colon properly
2. Used a recursive CTE to generate a list of dates
   • Generate a set or sequence without loops – part 1
   • Generate a set or sequence without loops – part 2
   • Generate a set or sequence without loops – part 3
3. Did not include the column list for an insert
4. Used DATENAME to elimiate weekends, which is language specific, much better to explicitly set DATEFIRST and use DATEPART
5. Used LEFT JOIN/IS NULL instead of NOT EXISTS to elimiate records from the holiday table. In SQL Server LEFT JOIN/IS NULL is less efficient than NOT EXISTS

These are all minor things, but they are things I would critique (at least in my head if not outloud) when reviewing someone else's query, so can't really not correct my own work! Rewriting the query would give.

SET DATEFIRST 1;

DECLARE @StartDate DATETIME = '20110401',
        @EndDate DATETIME = '20120331';

CREATE TABLE #Data (FirstDay DATETIME NOT NULL PRIMARY KEY, WorkingDays INT NOT NULL);

WITH DaysCTE ([Date]) AS
(   SELECT  TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
            DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @StartDate)
    FROM    sys.all_objects a
)
INSERT INTO #Data (FirstDay, WorkingDays)
SELECT  FirstDay =  MIN([Date]),
        WorkingDays = COUNT(*) 
FROM    DaysCTE d
WHERE   DATEPART(WEEKDAY, [Date]) NOT IN (6, 7)
AND     NOT EXISTS
        (   SELECT  1
            FROM    dbo.HolidayTable ht
            WHERE   d.[Date] BETWEEN ht.HolStart AND ht.HolEnd
        )
GROUP BY DATEPART(MONTH, [Date]), DATEPART(YEAR, [Date]);

DECLARE @Date DATETIME = (SELECT MIN(FirstDay) FROM #Data);

SELECT  Period,
        [Days Available (Minus the Holidays)] = WorkingDays 
FROM    (   SELECT  DATENAME(MONTH, Firstday) [Period],
                    WorkingDays,
                    0 [SortField],
                    FirstDay
            FROM    #Data
            UNION
            SELECT  DATENAME(MONTH, @Date) + ' - ' + DATENAME(MONTH, Firstday),
                    (   SELECT  SUM(WorkingDays)
                        FROM    #Data b
                        WHERE   b.FirstDay <= a.FirstDay
                    ) [WorkingDays],
                    1 [SortField],
                    FirstDay 
            FROM    #Data a
            WHERE   FirstDay > @Date
        ) data
ORDER BY SortField, FirstDay;

DROP TABLE #Data;

As a final point, this query becomes much simpler with a calendar table that stores all dates, and has flags for working days, holidays etc, rather than using a holiday table that just stores holidays.

回答2:

Let me add few cents to this post. Just got assignment to calculate difference between planned hours and actual hour. The code below was converted to a function. So far no issue with the logic:

declare @date datetime = '11/07/2012'
declare @t table (HolidayID int IDENTITY(1,1) primary key,
    HolidayYear int,
    HolidayName varchar(50),
    HolidayDate datetime)

INSERT @t
VALUES(2012, 'New Years Day', '01/02/2012'),
(2012,'Martin Luther King Day', '01/16/2012'),
(2012,'Presidents Day', '02/20/2012'),
(2012,'Memorial Day', '05/28/2012'),
(2012,'Independence Day', '07/04/2012'),
(2012,'Labor Day', '09/03/2012'),
(2012,'Thanksgiving Day', '11/22/2012'),
(2012,'Day After Thanksgiving', '11/23/2012'),
(2012,'Christmas Eve', '12/24/2012'),
(2012,'Christmas Day', '12/25/2012'),
(2013, 'New Years Day', '01/01/2013'),
(2013,'Martin Luther King Day', '01/21/2013'),
(2013,'Presidents Day', '02/18/2013'),
(2013,'Good Friday', '03/29/2013'),
(2013,'Memorial Day', '05/27/2013'),
(2013,'Independence Day', '07/04/2013'),
(2013,'Day After Independence Day', '07/05/2013'),
(2013,'Labor Day', '09/02/2013'),
(2013,'Thanksgiving Day', '11/28/2013'),
(2013,'Day After Thanksgiving', '11/29/2013'),
(2013,'Christmas Eve', NULL),
(2013,'Christmas Day', '12/25/2013')


DECLARE @START_DATE DATETIME, 
    @END_DATE DATETIME, 
    @Days int

SELECT  @START_DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0) 

SELECT  @END_DATE = DATEADD(month, 1,@START_DATE)

;WITH CTE AS
(
    SELECT DATEADD(DAY, number, (DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0) )) CDate
    FROM master.dbo.spt_values where type = 'p' and number between 0 and 365
        EXCEPT
    SELECT HolidayDate FROM @t WHERE HolidayYear = YEAR(@START_DATE) 
)
SELECT @Days = COUNT(CDate) --, datepart(dw, CDate) WDay
FROM CTE 
WHERE (CDate >=@START_DATE and CDate < @END_DATE) AND DATEPART(dw, CDate) NOT IN(1,7)

SELECT @Days