August 14, 2014 at 8:46 am
I have a customer history table with the follow structure and data:
CustomerID Tier StartDate RecordStatus
123 A 01/01/2013 1
123 A 03/01/2013 0
123 B 03/01/2013 1
123 B 06/01/2013 0
123 A 08/01/2014 1
456 C 02/01/2014 1
CREATE TABLE TEMPHISTORY(
CUSTOMERID VARCHAR(11),
TIER VARCHAR(10),
STARTDATE DATE,
RECORDSTATUS TINYINT)
INSERT INTO TEMPHISTORY(CUSTOMERID, TIER, STARTDATE. RECORDSTATUS) VALUES( '123', 'A', '01/01/2013', 1)
INSERT INTO TEMPHISTORY(CUSTOMERID, TIER, STARTDATE. RECORDSTATUS) VALUES( '123', 'A', '03/01/2013', 0)
INSERT INTO TEMPHISTORY(CUSTOMERID, TIER, STARTDATE. RECORDSTATUS) VALUES( '123', 'B', '03/01/2013', 1)
INSERT INTO TEMPHISTORY(CUSTOMERID, TIER, STARTDATE. RECORDSTATUS) VALUES( '123', 'B', '06/01/2013', 0)
INSERT INTO TEMPHISTORY(CUSTOMERID, TIER, STARTDATE. RECORDSTATUS) VALUES( '123', 'A', '08/01/2014', 1)
INSERT INTO TEMPHISTORY(CUSTOMERID, TIER, STARTDATE. RECORDSTATUS) VALUES( '456', 'C', '02/01/2014', 1)
The RECORDSTATUS value of 1 means the record is active. A corresponding record of the
same CustomerID, Tier. in startdate chronology, with a value of 0 indicates that the previous
record with the status of 1 has now terminated and the startdate of the record with recordstatus of
0 is the start date of the termination of the previous record, or better stated, the end date of
the previous record.
What I need to do is re-record the above data the startdate of each terminated record become
an enddate for the previous record, minus 1 day, as follows:
CUSTOMERID TIER STARTDATE ENDDATE
123 A 01/01/2013 02/28/2013
123 B 03/01/2013 05/31/2013
123 A 08/01/2014 NULL
456 C 02/01/2014 NULL
Can someone help with SQL to do this?
August 14, 2014 at 9:29 am
WITH Starts AS (
SELECT
CUSTOMERID
,TIER
,STARTDATE
,RECORDSTATUS
FROM
TEMPHISTORY
WHERE
RECORDSTATUS = 1
)
, Ends AS (
SELECT
CUSTOMERID
,TIER
,STARTDATE ENDDATE
,RECORDSTATUS
FROM
TEMPHISTORY
WHERE
RECORDSTATUS = 0
)
SELECT
s.CUSTOMERID
,s.TIER
,s.STARTDATE
,DATEADD(dd,-1,e.ENDDATE) ENDDATE
FROM
Starts s
LEFT JOIN
Ends e
ON
s.CUSTOMERID = e.CUSTOMERID AND s.TIER = e.TIER
AND
e.ENDDATE > s.STARTDATE
John
Edit: - this may not be the most efficient way of doing it, since it involves two separate table scans. Using PIVOT may be more efficient.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply