SQL填充每个月的总工作日减去本财政年度的银行假期 [英] SQL populate total working days per month minus bank holidays for current financial year

问题描述

我看完后会看起来像我的第一张照片，但右手栏填充而不是空白。逻辑如下：

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:

数据必须是当前财务期间。 4月以前将是2011年和3月将是2012年等等。

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:

总工作天数（Monday-星期五）减去任何落入该特定月份的银行假期（在特定财政年度（我们在表中保存 - 见第二张图片）。

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

假日表从左到右： holidaytypeid ，名称， holstart ， holend 。
表名称：可放假

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

要计算累积月份可用天数，将这是一个月来已经填充的数据的总和。例如四月至五月将是四月和五月的数据SUMMED等等。

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.

我需要完美格式的SQL查询，以便直接粘贴到将工作（即使用正确的列名称和表名称）

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)

感谢您的查找。

推荐答案

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

如果你这样做超过1年，你需要改变这一行：

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.

编辑

我刚刚来到我这个老的答案，真的不喜欢它，有很多事情我现在认为不好的做法，所以我要纠正所有的问题：

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. 我没有正确地终止具有半个冒号的语句


2. 使用递归CTE生成日期列表
   
   
   
   • 生成没有循环的集合或序列 - 第1部分
   
   
   • 生成没有循环的集合或序列 - 第2部分
   
   
   • 生成无循环的集合或序列 - 第3部分
   
   

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

这些都是小事，但它们是事情我会批评（至少在我的头，如果不是大声）审查别人的查询，所以不能真的不能纠正我自己的工作！重写查询将给出。

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.

这篇关于SQL填充每个月的总工作日减去本财政年度的银行假期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！