collapse the date column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

collapse the date column

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2014-11-06 : 00:56:28

[code]
Table created
[code]
declare @t table
(
Qtr VARCHAR(1000),visittype VARCHAR(1000),
CMSContractID VARCHAR(1000, memid VARCHAR(1000),
CarrierMemID VARCHAR(1000), HeadOfHouse VARCHAR(1000),
dob datetime ,FullName VARCHAR(1000),minstartdt datetime,maxenddt datetime

)

insert into @t

SELECT '06/01/2014 - 08/31/2014','ED','H8677','CA140812115','100000003','954646','1954-04-08','TOM','2014-07-05','2014-07-05' union all
SELECT '06/01/2014 - 08/31/2014','ED','H8677','CA140812115','100000003','954646','1954-04-08','TOM','2014-07-06','2014-07-06' union all
SELECT '06/01/2014 - 08/31/2014','ED','H8677','CA140812115' ,'100000003','954646','1954-04-08','TOM','2014-07-09' ,'2014-07-09'

[/code]

[/code]
Requirement
need to collapse when 1 day gap between maxenddt
and next record's minstartdt

dataset

[code]
Qtr visittype CMSContractID memid CarrierMemID HeadOfHouse dob FullName minstartdt maxenddt
06/01/2014 - 08/31/2014 ED H8677 CA140812115 100000003 954646 8-Apr-54 TOM 5-Jul-14 5-Jul-14
06/01/2014 - 08/31/2014 ED H8677 CA140812115 100000003 954646 8-Apr-54 TOM 6-Jul-14 6-Jul-14
06/01/2014 - 08/31/2014 ED H8677 CA140812115 100000003 954646 8-Apr-54 TOM 9-Jul-14 9-Jul-14

[/code]

Expected output:

[code]
Qtr visittype CMSContractID memid CarrierMemID HeadOfHouse dob FullName minstartdt maxenddt
06/01/2014 - 08/31/2014 ED H8677 CA140812115 100000003 954646 8-Apr-54 TOM 5-Jul-14 6-Jul-14
06/01/2014 - 08/31/2014 ED H8677 CA140812115 100000003 954646 8-Apr-54 TOM 9-Jul-14 9-Jul-14

[/code]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-11-06 : 05:12:42

What is the logic behind to get the above expected output ?

--
Chandu

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2014-11-06 : 09:45:20

we need to collapse into 1 entry instead of two
for the first two line items

quote:
Originally posted by bandi

What is the logic behind to get the above expected output ?

--
Chandu

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-07 : 12:36:42

If you want to rollup consecutive days, try something like:


WITH Grps
AS
(
	SELECT Qtr, visittype, CMSContractID, memid, CarrierMemID, HeadOfHouse, dob, FullName, minstartdt, maxenddt
		,DATEADD(day, -ROW_NUMBER()
			OVER 
			(
				PARTITION BY Qtr, visittype, CMSContractID, memid, CarrierMemID, HeadOfHouse, dob, FullName
				ORDER BY minstartdt
			), minstartdt) AS Grp
	FROM @t
)
, RollUps
AS
(
	SELECT Qtr, visittype, CMSContractID, memid, CarrierMemID, HeadOfHouse, dob, FullName, Grp
		,MIN(minstartdt) AS minstartdt
		,MAX(maxenddt) AS maxenddt
	FROM Grps
	GROUP BY Qtr, visittype, CMSContractID, memid, CarrierMemID, HeadOfHouse, dob, FullName, Grp
)
SELECT Qtr, visittype, CMSContractID, memid, CarrierMemID, HeadOfHouse, dob, FullName, minstartdt, maxenddt
FROM Rollups;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources