I have following data:
CREATE TABLE #Rate
(
RateId Bigint
,PropertyId Bigint
,StartDate DATETIME
,EndDate DATETIME
)
INSERT INTO #Rate VALUES (100,1000,'2015-01-01','2010-01-11')
INSERT INTO #Rate VALUES (100,1000,'2015-01-12','2015-02-02')
INSERT INTO #Rate VALUES (100,1000,'2015-02-11','2015-02-25')
INSERT INTO #Rate VALUES (100,1002,'2015-01-01','2010-01-11')
INSERT INTO #Rate VALUES (100,1002,'2015-01-12','2015-02-02')
INSERT INTO #Rate VALUES (101,1000,'2015-02-11','2015-02-25')
INSERT INTO #Rate VALUES (101,1000,'2015-01-01','2010-01-11')
INSERT INTO #Rate VALUES (101,1000,'2015-01-12','2015-02-02')
And I need this result set
100 1000 '2015-01-01' '2015-02-02'
100 1000 '2015-02-11' '2015-02-25'
100 1002 '2015-01-01' '2015-02-02'
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.
3条答案
按热度按时间laik7k3q1#
Changing all the
2010
with2015
in your data the actual resultset you can expect isthis 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
D
generates a row counter to avoid to use triangular join.N
get the previousEndDate
in the sameRateID, PropertyID
group for every row.B
generate a sequence number for every blockThe main query aggregates the data in
B
to get the wanted resultset.hmtdttj42#
Assuming you are using SQL Server 2012+, you can take the following approach:
The query looks like:
EDIT:
In SQL Server 2008, you can do something similar:
kyvafyod3#
Based on @Serpiton answer, here is a shorter solution :