Pivot transaction dates with state into date range

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

SQL Server 2012 Forums

Transact-SQL (2012)

Pivot transaction dates with state into date range

Author

Topic

rasmuslh
Starting Member

2 Posts

Posted - 2015-04-07 : 08:23:30

Hi

I received data monthly on our customer in the following form:

ID Date State
1 20140101 Paying
1 20140201 Paying
1 20140301 Paying
1 20140401 Paying
1 20140501 Paying
1 20140601 Cancelled
1 20140701 Cancelled
1 20140801 Cancelled
1 20140901 Cancelled
1 20141001 Paying
1 20141101 Cancelled
1 20141201 Cancelled
1 20150101 Cancelled
1 20150201 Paying
1 20150301 Paying
1 20150401 Paying

I would like to convert this data into the following format:

ID DateFrom DateTo State
1 20140101 20140501 Paying
1 20140601 20140601 Cancelled
1 20141001 20141001 Paying
1 20141101 20150101 Cancelled
1 20150201 20150401 Paying

I have considered using a counter Next/Prev row but I would like to know whether somebody had a more elegant solution.

Kind regards
Rasmus

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-07 : 08:55:04

[code]
;WITH cteSample AS
(
SELECT ID ,Date ,State
FROM (VALUES (1, '20140101', 'Paying')
,(1, '20140201' ,'Paying')
,(1, '20140301' ,'Paying')
,(1, '20140401' ,'Paying')
,(1, '20140501' ,'Paying')
,(1, '20140601' ,'Cancelled')
,(1, '20140701' ,'Cancelled')
,(1, '20140801' ,'Cancelled')
,(1, '20140901' ,'Cancelled')
,(1, '20141001' ,'Paying')
,(1, '20141101' ,'Cancelled')
,(1, '20141201' ,'Cancelled')
,(1, '20150101' ,'Cancelled')
,(1, '20150201' ,'Paying')
,(1, '20150301','Paying')
,(1, '20150401' ,'Paying') ) A (ID,Date,State)
)
,cteFinal
AS
(
SELECT id, Date, State
--,ROW_NUMBER()OVER(ORDER BY ID,Date) AS RN1
,ROW_NUMBER()OVER(ORDER BY ID,Date)
- ROW_NUMBER()OVER(PARTITION BY State ORDER BY ID,Date) AS Grp
FROM cteSample
--ORDER BY RN1
)

SELECT
ID
,MIN(Date) AS DateFrom
,MAX(Date) AS DateTo
,State
FROM
cteFinal
GROUP BY ID, State ,Grp
ORDER BY DateFrom
[/code]

the result set is:
[code]
ID DateFrom DateTo State
1 20140101 20140501 Paying
1 20140601 20140901 Cancelled
1 20141001 20141001 Paying
1 20141101 20150101 Cancelled
1 20150201 20150401 Paying
[/code]

sabinWeb MCP

rasmuslh
Starting Member

2 Posts

Posted - 2015-04-07 : 09:05:53

Sweet. Thanks so much. Just what I was looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources