Holiday Availability Calender - sum available days still left to sell over consecutive days

Question

I require is a min & max of the BaseDate where the available to sell = 1 and there are 3 or more consecutive days still available to sell. However, the sum needs to be excluded if the properties changeoverday starts on the same day as the BaseDate, as we are only interested in the gaps that we can't sell due to changeover restrictions. The data would have to be grouped by Code, as we have over 1,000 properties. BaseDates are for 2015 & 2016.

NB: Some properties have more than 1 changeoverDay & are currently held in one column comma separated i.e. Saturday, Sunday

Example Data:-

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

I would require the output as below:-

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

Answer 1

This gives you what you want. But I feel there's a way to reduce the number of times it touches the table

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

The logic is pretty much:

• Find the last date where the AvailableToSell flag flipped before "this row"
• Group into sets by those dates and count the rows in it
• Decrement by 1 if the start date has DayName as the ChangeoverDay

I havent accounted for your note about the ChangeoverDay being a comma separated field. There are plenty of resources on breaking that out which you could then join to. But I think you also need to expand what happens in this scenario with regards to DayName is in the list of ChangeoverDays