Group continuous date ranges from same table SQL Server

siv3szwd  于 2023-11-16  发布在  SQL Server
关注(0)|答案(3)|浏览(197)

I have following data:

  1. CREATE TABLE #Rate
  2. (
  3. RateId Bigint
  4. ,PropertyId Bigint
  5. ,StartDate DATETIME
  6. ,EndDate DATETIME
  7. )
  8. INSERT INTO #Rate VALUES (100,1000,'2015-01-01','2010-01-11')
  9. INSERT INTO #Rate VALUES (100,1000,'2015-01-12','2015-02-02')
  10. INSERT INTO #Rate VALUES (100,1000,'2015-02-11','2015-02-25')
  11. INSERT INTO #Rate VALUES (100,1002,'2015-01-01','2010-01-11')
  12. INSERT INTO #Rate VALUES (100,1002,'2015-01-12','2015-02-02')
  13. INSERT INTO #Rate VALUES (101,1000,'2015-02-11','2015-02-25')
  14. INSERT INTO #Rate VALUES (101,1000,'2015-01-01','2010-01-11')
  15. INSERT INTO #Rate VALUES (101,1000,'2015-01-12','2015-02-02')

And I need this result set

  1. 100 1000 '2015-01-01' '2015-02-02'
  2. 100 1000 '2015-02-11' '2015-02-25'
  3. 100 1002 '2015-01-01' '2015-02-02'
  4. 101 1002 '2015-01-01' '2015-02-02'

I need to group by RateId and propertyId and continuous date range for this. I have done this using cursor but I don't want cursor because we have lots of records.

If we can create view out of it that will be great :)

Thanks.

laik7k3q

laik7k3q1#

Changing all the 2010 with 2015 in your data the actual resultset you can expect is

  1. RateId PropertyId StartDate EndDate
  2. -------------------- -------------------- ---------- ----------
  3. 100 1000 2015-01-01 2015-02-02
  4. 100 1000 2015-02-11 2015-02-25
  5. 100 1002 2015-01-01 2015-02-02
  6. 101 1000 2015-01-01 2015-02-02
  7. 101 1000 2015-02-11 2015-02-25

this question is quite similar to find start and stop date for contiguous dates in multiple rows so I'll use my answer to that one as a template

  1. WITH D AS (
  2. SELECT RateId, PropertyId, StartDate, EndDate
  3. , _Id = ROW_NUMBER() OVER (PARTITION BY RateId, PropertyId
  4. ORDER BY StartDate, EndDate)
  5. FROM #Rate
  6. ), N AS (
  7. SELECT m.RateId, m.PropertyId, m.StartDate, m.EndDate
  8. , LastStop = p.EndDate
  9. FROM D m
  10. LEFT JOIN D p ON m.RateID = p.RateId
  11. AND m.PropertyId = p.PropertyId
  12. AND m._Id = p._Id + 1
  13. ), B AS (
  14. SELECT RateId, PropertyId, StartDate, EndDate, LastStop
  15. , Block = SUM(CASE WHEN LastStop Is Null Then 1
  16. WHEN LastStop + 1 < StartDate Then 1
  17. ELSE 0
  18. END)
  19. OVER (PARTITION BY RateId, PropertyId ORDER BY StartDate, EndDate)
  20. FROM N
  21. )
  22. SELECT RateId, PropertyId
  23. , MIN(StartDate) StartDate
  24. , MAX(EndDate) EndDate
  25. FROM B
  26. GROUP BY RateId, PropertyId, Block
  27. ORDER BY RateId, PropertyId, Block;

D generates a row counter to avoid to use triangular join.
N get the previous EndDate in the same RateID, PropertyID group for every row.
B generate a sequence number for every block
The main query aggregates the data in B to get the wanted resultset.

展开查看全部
hmtdttj4

hmtdttj42#

Assuming you are using SQL Server 2012+, you can take the following approach:

  • Find all the records that do not overlap with the prev record. These begin a range.
  • Count the number of such records before any given record. These assign a constant value to the range.
  • Use this as a grouping factor.

The query looks like:

  1. select rateid, propertyid, min(startdate) as startdate, max(enddate) as enddate
  2. from (select r.*,
  3. sum(case when preved < startdate then 1 else 0 end) over (partition by rateid, propertyid order by startdate) as grp
  4. from (select r.*,
  5. lag(enddate) over (partition by rateid, propertyid order by enddate) as preved
  6. from #Rate r
  7. ) r
  8. ) r
  9. group by rateid, propertyid, grp;

EDIT:

In SQL Server 2008, you can do something similar:

  1. with r as (
  2. select r.*,
  3. (case when exists (select 1
  4. from #rate r2
  5. where r2.rateid = r.rateid and r2.propertyid = r.propertyid and
  6. (r2.startdate <= dateadd(1 day, r.enddate) and
  7. r2.enddate >= r.startdate)
  8. ) then 0 else 1 end) as isstart
  9. from #Rate r join
  10. #Rate r2
  11. )
  12. select rateid, propertyid, min(startdate) as startdate, max(enddate) as enddate
  13. from (select r.*,
  14. (select sum(isstart)
  15. from r r2
  16. where r2.rateid = r.rateid and r2.propertyid = r.propertyid
  17. r2.startdate <= r.startdate) as grp
  18. from r
  19. ) r
  20. group by rateid, propertyid, grp;
展开查看全部
kyvafyod

kyvafyod3#

Based on @Serpiton answer, here is a shorter solution :

  1. CREATE TABLE #Rate
  2. (
  3. RateId Bigint
  4. ,PropertyId Bigint
  5. ,StartDate DATETIME
  6. ,EndDate DATETIME
  7. )
  8. INSERT INTO #Rate VALUES (100,1000,'2015-01-01','2015-01-11')
  9. INSERT INTO #Rate VALUES (100,1000,'2015-01-12','2015-02-02')
  10. INSERT INTO #Rate VALUES (100,1000,'2015-02-03','2015-02-04')
  11. INSERT INTO #Rate VALUES (100,1000,'2015-02-05','2015-02-06')
  12. INSERT INTO #Rate VALUES (100,1000,'2015-02-11','2015-02-25')
  13. INSERT INTO #Rate VALUES (100,1000,'2015-02-27','2015-03-02')
  14. INSERT INTO #Rate VALUES (100,1000,'2015-03-03','2015-03-13')
  15. INSERT INTO #Rate VALUES (100,1002,'2015-01-01','2015-01-11')
  16. INSERT INTO #Rate VALUES (100,1002,'2015-01-12','2015-02-02')
  17. INSERT INTO #Rate VALUES (101,1003,'2015-02-11','2015-02-25')
  18. INSERT INTO #Rate VALUES (101,1003,'2015-01-01','2015-01-11')
  19. INSERT INTO #Rate VALUES (101,1003,'2015-01-12','2015-02-02')
  20. WITH B AS (
  21. SELECT
  22. m.RateId, m.PropertyId, m.StartDate, m.EndDate,
  23. SUM(
  24. CASE WHEN p.EndDate Is Null Then 1
  25. WHEN p.EndDate + 1 < m.StartDate Then 1
  26. ELSE 0
  27. END
  28. ) OVER (PARTITION BY m.RateId, m.PropertyId ORDER BY m.StartDate, m.EndDate) AS Block
  29. FROM #rate m
  30. LEFT JOIN #rate p ON
  31. p.RateID = m.RateId AND
  32. p.PropertyId = m.PropertyId AND
  33. p.enddate+1 = m.startdate
  34. )
  35. SELECT RateId, PropertyId, MIN(StartDate) StartDate, MAX(EndDate) EndDate
  36. FROM B
  37. GROUP BY RateId, PropertyId, Block
  38. ORDER BY RateId, PropertyId, Block
展开查看全部

相关问题