How to reterive latest status change as previous

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)

How to reterive latest status change as previous

Author

Topic

hgarg
Starting Member

1 Post

Posted - 2015-03-14 : 19:39:53

I have data like this (example for one customer )

customer_id Date Customer_status
1 D14 A
1 D13 A
1 D12 Z
1 D11 E
1 D10 Y
1 D9 Y
1 D8 Z
1 D7 A
1 D6 X
1 D5 Y
1 D4 Y
1 D3 Y
1 D2 Y
1 D1 Y

In this table, data for customer is kept with its current customer_status for each day. I need to find it latest previous customer status change.
So for eg when Customer was added in system its status was 'Y' till D5 its current status was never changed so its status needs to be "No Previous status"
On D6 it status was changes to X so previous has become Y so on .Again on D9-D11 it was in status Y and latest change was from Z.

Desired Output

customer_id Date Current_Customer_status PREVIOUS_CUSTOMER_STATUS
1 D14 A Z
1 D13 A Z
1 D12 Z Y
1 D11 Y Z
1 D10 Y Z
1 D9 Y Z
1 D8 Z A
1 D7 A X
1 D6 X Y
1 D5 Y No Previous status
1 D4 Y No Previous status
1 D3 Y No Previous status
1 D2 Y No Previous status
1 D1 Y No Previous status
1 D0 Y No Previous status

Can you please help me with this

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-16 : 09:25:28

[code]
-- *** Test Data ***
-- Please provide in a consumable format in future
CREATE TABLE #t
(
customer_id int NOT NULL
,cDate date NOT NULL
,Customer_status char(1) NOT NULL
);
INSERT INTO #t
VALUES (1, '20150114', 'A'), (1, '20150113', 'A'), (1, '20150112', 'Z'), (1, '20150111', 'E')
,(1, '20150110', 'Y'), (1, '20150109', 'Y'), (1, '20150108', 'Z'), (1, '20150107', 'A')
,(1, '20150106', 'X'), (1, '20150105', 'Y'), (1, '20150104', 'Y'), (1, '20150103', 'Y')
,(1, '20150102', 'Y'), (1, '20150101', 'Y');
-- *** End Test Data ***

WITH Grps
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY cDate)
- ROW_NUMBER() OVER (PARTITION BY customer_id, Customer_status ORDER BY cDate) AS Grp
FROM #t
)
,Packed
AS
(
SELECT customer_id, Customer_status, Grp
,MIN(cDate) AS MinDate, MAX(cDate) AS MaxDate
,ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY MIN(cDate)) AS GrpOrder
FROM Grps
GROUP BY customer_id, Customer_status, Grp
)
,PrevStatii
AS
(
SELECT P1.customer_id, P1.MinDate, P1.MaxDate, P2.Customer_status AS PrevStatus
FROM Packed P1
LEFT JOIN Packed P2
ON P1.customer_id = P2.customer_id
AND P1.GrpOrder = P2.GrpOrder + 1
)
SELECT T.*, P.PrevStatus
FROM #t T
JOIN PrevStatii P
ON T.customer_id = P.customer_id
AND T.cDate BETWEEN P.MinDate AND P.MaxDate
ORDER BY cDate DESC;
[/code]

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2015-03-16 : 13:30:35

Another solution based on Ifor data sample
SELECT *, PrevStatus =
ISNULL
(
(
SELECT TOP 1 CONVERT(VARCHAR(100),t2.Customer_Status)
FROM #t t2 WHERE t2.customer_id=t1.customer_id AND t2.cDate < t1.cDate AND t2.Customer_Status<>t1.Customer_Status
),'no previous'
)
FROM #t t1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources