假日可用性日历-连续几天的剩余可用天数之和

Question

我需要的是BaseDate的最小值和最大值，其中可供销售的数量=1，并且还有3天或更多的连续天数可供销售。但是，如果属性变更日与BaseDate在同一天开始，则需要排除总和，因为我们只对由于转换限制而无法出售的缺口感兴趣。数据将必须按代码分组，因为我们有1000多个属性。BaseDates适用于2015和2016年。

注意:有些属性有超过1个changeoverDay &当前在一列中使用逗号分隔，例如周六、周日

示例数据：-

DECLARE @sampleData TABLE (
  Code VARCHAR(5) NOT NULL
, BaseDate DATE  NOT NULL
, DayName VARCHAR(9) NOT NULL
, ChangeoverDay VARCHAR(8) NOT NULL
, AvailabletoSell BIT  NOT NULL
);

INSERT INTO @sampleData VALUES 
('PERCH','2015-05-06','Wednesday','Saturday',0),
('PERCH','2015-05-07','Thursday','Saturday',0),
('PERCH','2015-05-08','Friday','Saturday',0),
('PERCH','2015-05-09','Saturday','Saturday',1), -- Not this one as changeover day is the same as the BaseDate

('PERCH','2015-05-10','Sunday','Saturday',1),
('PERCH','2015-05-11','Monday','Saturday',1),
('PERCH','2015-05-12','Tuesday','Saturday',0),
('PERCH','2015-05-13','Wednesday','Saturday',0),
('PERCH','2015-05-14','Thursday','Saturday',1), -- This one = 3

('PERCH','2015-05-15','Friday','Saturday',1),
('PERCH','2015-05-16','Saturday','Saturday',1),
('PERCH','2015-05-17','Sunday','Saturday',0),
('PERCH','2015-05-18','Monday','Saturday',1), -- This one = 4

('PERCH','2015-05-19','Tuesday','Saturday',1),
('PERCH','2015-05-20','Wednesday','Saturday',1),
('PERCH','2015-05-21','Thursday','Saturday',1),
('PERCH','2015-05-22','Friday','Saturday',0),
('PERCH','2015-05-23','Saturday','Saturday',0),
('PERCH','2015-05-24','Sunday','Saturday',0),
('PERCH','2015-05-25','Monday','Saturday',0),
('PERCH','2015-05-26','Tuesday','Saturday',0),
('PERCH','2015-05-27','Wednesday','Saturday',1), -- Not this one, as only 2 consecutive days
('PERCH','2015-05-28','Thursday','Saturday',1),
('PERCH','2015-05-29','Friday','Saturday',0),
('PERCH','2015-05-30','Saturday','Saturday',0);

我需要如下输出：

+-------+---------------+-------------+----------------------+
| Code  | StartBaseDate | EndBaseDate | TotalAvailabletoSell |
+-------+---------------+-------------+----------------------+
| PERCH | 14/05/2015    | 16/05/2015  |                    3 |
| PERCH | 18/05/2015    | 21/05/2015  |                    4 |
+-------+---------------+-------------+----------------------+

Answer 1

这会给你想要的东西。但我觉得有一种方法可以减少它碰到桌子的次数

WITH Groupings AS (

    SELECT
        Code
        ,LastChange
        ,MIN(BaseDate)  AS StartBaseDate 
        ,MAX(BaseDate)  AS EndBaseDate
        ,COUNT(*)       AS DaysInPeriod
    FROM
        @sampleData AS s1
        CROSS APPLY (
            SELECT
                MAX(BaseDate) AS LastChange
            FROM
                @sampleData AS cv
            WHERE
                s1.BaseDate > cv.BaseDate
            AND s1.AvailabletoSell != cv.AvailabletoSell
            AND s1.Code = cv.Code
        ) AS cv
    WHERE
        s1.AvailabletoSell = 1
    GROUP BY
        Code
        ,LastChange
)
SELECT
    g.Code
    ,g.StartBaseDate
    ,g.EndBaseDate
    ,CASE WHEN a.DayName = a.ChangeoverDay THEN DaysInPeriod - 1 ELSE DaysInPeriod END  AS TotalAvailableToSell
FROM
    Groupings AS g
    INNER JOIN @sampleData AS a 
        ON a.BaseDate = g.StartBaseDate AND a.Code = g.Code
WHERE
    CASE WHEN a.DayName = a.ChangeoverDay THEN DaysInPeriod - 1 ELSE DaysInPeriod END > 2

其中的逻辑很简单：

• 查找AvailableToSell标志在"this AvailableToSell“
• 之前翻转的最后日期按这些日期分组，如果开始日期以
• 作为ChangeoverDay

，则将其中的行数减1。

我还没有考虑到你关于ChangeoverDay是一个逗号分隔字段的说明。有大量的资源可以帮助你解决这个问题，然后你可以加入其中。但是我认为您还需要扩展在此场景中发生的事情，因为DayName在ChangeoverDays列表中