# Limit the records based on ranges column in sql server

46 Reputation points
2023-02-26T20:50:08.0833333+00:00

Problem Statement: If partially overlap then return till the next plan and incase of fully overlap then return maxplanid.

Input data is having all the required information just we have to limit no of records based on the condition. I tried myself but didn't get exected results.

Case 1: If plan is partially overlap then return the records between that overlap start and Overlapend date

For Example: Planid : 10 is partially overlap so we need to return Nov and dec because new planid :20 has released from jan-2023.

Case 2: If plan is Fully overlap then return the max plan id records between that overlap start and Overlapend date

For Example: Planid :20,30,40,50 are the fully overlap plans then need to return only max planid :50 records from jan,feb,mar,apr-2023.

## -

``````  - **Input  Data**

`Create table #target(`
`SLID int,`
`planid	int,`
`monthstartdate date,`
`StartDate date,`
`EndDate Date,`
`OverlapstartDate Date,`
`OverlapEndDate Date,`
`OLPlanID int,`
`IsFullOverlap int`
`)`
`insert into #target values`
`(1,10,'2022-11-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
`(1,10,'2022-12-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
`(1,10,'2023-01-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
`(1,10,'2023-02-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
`(1,10,'2023-03-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
`(1,10,'2023-04-01','2022-11-01','2023-04-30','2022-11-01','2022-12-31',10,0),`
`(1,20,'2022-11-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',20,1),`
`(1,20,'2022-12-01','2023-01-01','2023-04-30','2023-01-01','2023-01-01',20,1),`
`(1,20,'2023-01-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',20,1),`
`(1,20,'2023-02-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',20,1),`
`(1,20,'2023-03-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',20,1),`
`(1,20,'2023-04-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',20,1),`
`(1,30,'2022-11-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
`(1,30,'2022-12-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
`(1,30,'2023-01-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
`(1,30,'2023-02-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
`(1,30,'2023-03-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
`(1,30,'2023-04-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',30,1),`
`(1,40,'2022-11-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
`(1,40,'2022-12-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
`(1,40,'2023-01-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
`(1,40,'2023-02-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
`(1,40,'2023-03-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
`(1,40,'2023-04-01','2023-01-01','2023-04-30','2023-01-01','2023-04-30',40,1),`
`(1,50,'2022-11-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL),`
`(1,50,'2022-12-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL),`
`(1,50,'2023-01-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL),`
`(1,50,'2023-02-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL),`
`(1,50,'2023-03-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL),`
`(1,50,'2023-04-01','2023-01-01','2023-04-30',NULL,NULL,NULL,NULL);`

``````
• Output

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,644 questions

1. 22,621 Reputation points Microsoft Vendor
2023-02-27T02:41:55.7433333+00:00

Hi @Sahoo

Planid :20,30,40,50 are the fully overlap plans then need to return only max planid :50

One thing I am confused is that you say '50' is the fully overlap plan, however, the `OverlapStartdate` and `OverlapEnddate` of '50' is NULL in your table.

``````;WITH CTE AS
(
SELECT SLID,planid,monthstartdate,StartDate,EndDate,OverlapstartDate,OverlapEndDate,OLPlanID
,DENSE_RANK()OVER(ORDER BY planid DESC) AS RNum
FROM #target
WHERE (monthstartdate BETWEEN StartDate AND EndDate)
AND ((StartDate = OverlapstartDate AND EndDate = OverlapEndDate) OR OverlapEndDate IS NULL)
)
SELECT SLID,planid,monthstartdate,StartDate,EndDate,OverlapstartDate,OverlapEndDate,OLPlanID
FROM CTE WHERE RNum = 1
UNION
SELECT SLID,planid,monthstartdate,StartDate,EndDate,OverlapstartDate,OverlapEndDate,OLPlanID
FROM #target
WHERE (monthstartdate BETWEEN OverlapstartDate AND OverlapEndDate) AND EndDate <> OverlapEndDate
``````

Output:

Best regards,

Cosmog Hong

1. 100.8K Reputation points MVP
2023-02-26T22:25:31.4666667+00:00

I will have to admit that there is much here I don't understand. Specifically, I don't know anything about the actual business, so for me these SLID, planid, OverlapStart etc is just mumbo-jumbo. But it could certainly help if you explain.
It also unclear to me that is the expected output is given the sample in #target. Is that the grid with five rows in the image?

That result can be produced with this query:

``````  SELECT SLID, planid, MIN(StartDate), MAX(EndDate)
FROM   #target
GROUP  BY SLID, planid
``````

But maybe that is overly simple-minded?