group and split the overlapped date

Question

Post reply

group and split the overlapped date

x_japanfans5312

SSC-Addicted

Points: 458
More actions
June 13, 2011 at 8:35 pm

#242064

hi, i have a question about this query problem
declare @MeterContract table
(
MeterContract int identity(1,1),
MeterID int,
ContractID int,
Startdate date,
Enddate date
)
insert into @MeterContract values ( 1,1,'01-JAN-2011','01-AUG-2011')
insert into @MeterContract values ( 1,1,'01-MAR-2012','31-DEC-2012')
insert into @MeterContract values ( 1,2,'01-JAN-2011','01-JAN-2012')
insert into @MeterContract values ( 1,2,'01-AUG-2012','31-DEC-2012')
insert into @MeterContract values ( 1,3,'01-OCT-2011','30-SEP-2012')
select * from @MeterContract
Here are the range in picture :
jan 11|------------------------------------------------------------| dec 12
jan 11|----------|aug 11mar 12|-------------------| dec 12
jan 11|----------------|jan 12 aug 12 |--------------------| dec 12
...........................|--------------------------------|
........................... oct 11.................................sep 12
The result should be
MeterID StartDate EndDate
1 '01-JAN-2011' '01-AUG-2011' ( for Contract 1 and 2)
1 '01-OCT-2011' '01-JAN-2012' ( for Contract 2 and 3)
1 '01-MAR-2012' '01-AUG-2012' ( for Contract 1 and 3)
1 '01-AUG-2012' '30-SEP-2012' ( for contract 1 , 2 and 3)
1 '30-SEP-2012' '31-DEC-2012' ( for Contract 2 and 3)
How to write the query to have a result like above ?
thanks before.. 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

x_japanfans5312 SSC-Addicted Points: 458 More actions · Answer 1

what i have done is this :

SELECT distinct

MC.MeterID,

MC.ContractID,

Common.ufnToDateDisplay(cast(Common.ufnGetMaxOfTwoValues( MC.Startdate, MC2.Startdate)as DATE)),

Common.ufnToDateDisplay(cast(Common.ufnGetMinOfTwoValues(MC.Enddate,MC2.Enddate)as DATE))

FROM

@MeterContract MC

join @MeterContract MC2

on MC.MeterID = MC2.MeterID

and MC.ContractID <> MC2.ContractID

and ( MC.Startdate between MC2.Startdate and MC2.Enddate

or MC.Enddate between MC2.Startdate and MC2.Enddate

)

and (MC2.Startdate between MC.Startdate and MC.Enddate or

MC2.Enddate between MC.Startdate and MC.Enddate)

this returns:

MeterIDContractIDStartDateEndDate

1101-AUG-201231-DEC-2012

1101-JAN-201101-AUG-2011

1101-MAR-201230-SEP-2012

1201-AUG-201230-SEP-2012

1201-AUG-201231-DEC-2012

1201-JAN-201101-AUG-2011

1201-OCT-201101-JAN-2012

1301-AUG-201230-SEP-2012

1301-MAR-201230-SEP-2012

1301-OCT-201101-JAN-2012

this result match with two row fromthe expected result.

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 2

Seems to work with your data, also '30-SEP-2012' '31-DEC-2012' is contract 1 and 2, not 2 and 3.

WITH StartsAndEnds AS (

SELECT MeterID,ContractID,Startdate AS theDate

FROM @MeterContract

UNION ALL

SELECT MeterID,ContractID,Enddate

FROM @MeterContract),

GapsFwd(MeterID,Startdate,Enddate) AS (

SELECT s.MeterID,Startdate,MIN(e.theDate)

FROM @MeterContract s

INNER JOIN StartsAndEnds e ON e.MeterID=s.MeterID

AND e.ContractID<>s.ContractID

AND e.theDate > s.Startdate

AND e.theDate <= s.Enddate

GROUP BY s.MeterID,s.Startdate),

GapsRev(MeterID,Startdate,Enddate) AS (

SELECT s.MeterID,

MAX(e.theDate),

s.Enddate

FROM @MeterContract s

INNER JOIN StartsAndEnds e ON e.MeterID=s.MeterID

AND e.ContractID<>s.ContractID

AND e.theDate >= s.Startdate

AND e.theDate < s.Enddate

GROUP BY s.MeterID,s.Enddate)

SELECT MeterID,

Startdate,Enddate

FROM GapsFwd

UNION

SELECT MeterID,

Startdate,Enddate

FROM GapsRev;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

DECLARE@MeterContract TABLE

(

MeterContract INT IDENTITY(1, 1),

MeterID INT NOT NULL,

ContractID INT NOT NULL,

StartDate DATE NOT NULL,

EndDate DATE NOT NULL

)

INSERT@MeterContract

VALUES(1, 1, '01-JAN-2011', '01-AUG-2011'),

(1, 1, '01-MAR-2012', '31-DEC-2012'),

(1, 2, '01-JAN-2011', '01-JAN-2012'),

(1, 2, '01-AUG-2012', '31-DEC-2012'),

(1, 3, '01-OCT-2011', '30-SEP-2012')

;WITH cteSource(MeterID, theDate)

AS (

SELECT DISTINCTmc.MeterID,

f.theDate

FROM@MeterContract AS mc

CROSS APPLY(

SELECTDATEADD(DAY, Number, mc.StartDate) AS theDate

FROMmaster.dbo.spt_values

WHEREType = 'P'

AND Number <= DATEDIFF(DAY, mc.StartDate, mc.EndDate)

) AS f(theDate)

)

SELECTMeterID,

MIN(theDate) AS StartDate,

MAX(theDate) AS EndDate,

STUFF(MIN(Data), 1, 1, '') AS Contracts

FROM(

SELECTs.MeterID,

s.theDate,

DATEDIFF(DAY, 0, s.theDate) - ROW_NUMBER() OVER (PARTITION BY s.MeterID, f.Data ORDER BY s.theDate) AS SeqID,

f.Data

FROMcteSource AS s

CROSS APPLY(

SELECT',' + CAST(mc.ContractID AS VARCHAR(11))

FROM@MeterContract AS mc

WHEREmc.MeterID = s.MeterID

AND mc.StartDate <= s.theDate

AND mc.EndDate >= s.theDate

ORDER BYmc.ContractID

FOR XMLPATH('')

) AS f(Data)

) AS d

GROUP BYMeterID,

SeqID

ORDER BYMeterID,

MIN(theDate)

N 56°04'39.16"
E 12°55'05.25"

x_japanfans5312 SSC-Addicted Points: 458 More actions · Answer 4

Mark-101232 (6/14/2011)

Seems to work with your data, also '30-SEP-2012' '31-DEC-2012' is contract 1 and 2, not 2 and 3.

oh yeah , you are right mark, it should be for contract 1 and contract 2...

thanks for your help Mark .. i really appreciate it... thanks again

@SwePeso

thanks for your help SwePeso .. i really appreciate it... thanks again