Finding, ordering and unique references for records with overlapping dates

Transact-SQL
,
1

I am trying to create a dataset for a range chart in SSRS, the purpose of this chart is to act as a promotional calendar for our sales team to be able to see where they have promotions and at times multiple concurrent promotions running at retail. I have got the bar chart working well but I am struggling with overlapping dates.

I have a test dataset that highlights my problem

[CREATE TABLE #promotiontest
(
Brand VARCHAR(20),
Product VARCHAR(20),
Deals VARCHAR(20),
StartDate DATE,
EndDate DATE,
Duration INT,
LineID INT
)

insert into #promotiontest values('Brand A','Product A','Deal 1','2016-06-01','2016-06-30','29',1);
insert into #promotiontest values('Brand A','Product A','Deal 2','2016-06-10','2016-09-30','112',2);
insert into #promotiontest values('Brand A','Product A','Deal 3','2016-06-25','2016-07-31','36',3);
insert into #promotiontest values('Brand A','Product A','Deal 4','2016-06-25','2016-07-31','36',4);
insert into #promotiontest values('Brand A','Product A','Deal 5','2016-06-30','2016-07-14','14',1);
insert into #promotiontest values('Brand A','Product A','Deal 6','2016-08-31','2016-09-30','30',1);
insert into #promotiontest values('Brand A','Product B','Deal 7','2016-06-01','2016-06-30','29',1);
insert into #promotiontest values('Brand A','Product B','Deal 8','2016-07-01','2016-07-30','29',1);
insert into #promotiontest values('Brand A','Product B','Deal 9','2016-07-31','2016-08-31','31',1);
insert into #promotiontest values('Brand A','Product B','Deal 10','2016-09-01','2016-09-30','29',1);

SELECT *
FROM #promotiontest

My challenge, is I need to create a dynamic column that works just like LineID (which I have hardcoded to show how it should look). LineID is used to signify if there are overlapping promotions, 1 means it is the first one, 2 means its the second etc etc. The challenge is getting Deal 5 to have a lineID of 1, as the promotion that is on Line 1 would have finished. All of product B should be on one line as there are no overlaps. This needs to be dynamic because if deal 2 was filtered out by the user I need that line to disappear in the report.

I use the LineID in the range chart to work as a grouping, which I have uploaded below.

Capture.JPG1095×684 39.6 KB

Apologies if this doesn't make sense.

Any help would be gratefully received.

Thank you

2

Hello @khtan apologies for mentioning you but looking through the forum your response to a post is extremely close to what I am trying to do.

The code you wrote below works to a point but I need to be able to scan the rcte table to see if there are records in it that the dates are between rather than using row number -1 as my rows can be all over the place.

; with data (Brand, Product, Deal, StartDate, EndDate, Duration, LineID) as
(
SELECT 'Brand A','Product A','Deal 1','2016-06-01','2016-06-30','29',1 UNION all
SELECT 'Brand A','Product A','Deal 2','2016-06-10','2016-09-30','112',2 UNION all
SELECT 'Brand A','Product A','Deal 3','2016-06-25','2016-07-31','36',3 UNION all
SELECT 'Brand A','Product A','Deal 4','2016-06-25','2016-07-31','36',4 UNION all
SELECT 'Brand A','Product A','Deal 5','2016-06-30','2016-07-14','14',1 UNION all
SELECT 'Brand A','Product A','Deal 6','2016-08-31','2016-09-30','30',1 UNION all
SELECT 'Brand A','Product B','Deal 7','2016-06-01','2016-06-30','29',1 UNION all
SELECT 'Brand A','Product B','Deal 8','2016-07-01','2016-07-30','29',1 UNION all
SELECT 'Brand A','Product B','Deal 9','2016-07-31','2016-08-31','31',1 UNION ALL
SELECT 'Brand A','Product B','Deal 10','2016-09-01','2016-09-30','29',1
),
cte as
(
select rn = row_number() over (partition by Brand,Product order by StartDate), *
from data
),
rcte AS
(
select rn, Brand, Product,Deal , StartDate, EndDate,LineID,
grp = 1, grpStart = StartDate, grpEnd = EndDate
from cte
where rn = 1

union ALL

select  c.rn, c.Brand, c.Product, c.Deal, c.StartDate, c.EndDate,c.LineID,
    grp     = case     when c.StartDate between r.grpStart and r.grpEnd
            then r.grp +1
            else r.grp
            end,
    grpStart = case when c.StartDate BETWEEN r.grpStart and r.grpEnd
            then r.grpStart
            else c.StartDate
            end,
    grpEnd   = case when c.EndDate >= r.grpEnd then c.EndDate else r.grpEnd end
from    rcte r
    inner join cte c    
	ON  r.Brand    = c.Brand 
	AND r.Product = c.Product
    AND r.rn        = c.rn -1

)

SELECT * FROM rcte
ORDER BY Brand,Product,StartDate

Does anyone know if you can use a cte and run a scan to the destination table before inserting the record in?

Thank you and Kind REgards

Rich

3

Seems like you have already got it as 1

what do you mean ? With Deal 2 removed, how would the expected result like ?

4

Hello @khtan thank you for response.

The code above returns deal 5 as a 5 not a 1, I believe the 1 is what I hardcoded.

In reference to deal 2, I more mean the code needs to be dynamic and calculate in the fly, so if I created a parameter it was removed via the parameter it would be excluded from the calculation.

Thank you and kind regards

Rich