September 29, 2014 at 3:07 pm
Here is the data I am working with:
CREATE TABLE CUSTOMERS(
CUSTOMER VARCHAR(11),
CONTRACT VARCHAR(5),
STARTDATE DATETIME,
ENDDATE DATETIME)
--CONTRACT CHANGED 06/01 BUT COVERAGE WAS CONTINUOUS WRITE 2 RECORDS
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188001', 'A5400', '01/01/2012', '12/31/2012')
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188001', 'A5400', '01/01/2013', '12/31/2013')
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188001', 'A5400', '01/01/2014', '05/31/2014')
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188001', 'A5401', '06/01/2014', NULL)
--CONTRACT CHANGED BUT COVERAGE WAS CONTINUOUS. WRITE 2 RECORDS
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188002', 'A5400', '01/01/2012', '12/31/2012')
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188002', 'A5500', '01/01/2013', '12/31/2013')
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188002', 'A5500', '01/01/2014', NULL)
--CONTRACT CHANGED 3 TIMES AND THERE WAS COVERAGE BREAK OF > 28 DAYS
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188003', 'A5600', '01/01/2012', '12/31/2012')
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188003', 'A5700', '01/01/2013', '12/31/2013')
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188003', 'A5800', '01/01/2014', '05/31/2014')
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188003', 'A5901', '07/01/2014', NULL)
--NO BREAK IN COVERAGE AND CONTRACT # REMAINED SAME - WRITE ORIGINAL RECORD ONLY
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188004', 'A5600', '01/01/2012', '12/31/2012')
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188004', 'A5600', '01/01/2013', '12/31/2013')
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188004', 'A5600', '01/01/2014', '06/30/2014')
INSERT INTO CUSTOMERS(CUSTOMER, CONTRACT, STARTDATE, ENDDATE) VALUES('21011188004', 'A5600', '07/01/2014', NULL)
The task to to examine each customer's contract history and write records off to a history
table where:
1 RECORD ORIGINAL START AND LAST ENDDATE WHERE
CONTRACT DID NOT CHANGE AND COVERAGE WAS CONTINUOUS
EACH RECORD FOR
1 EACH ORIGINAL CONTRACT
AND 1 RECORD WHERE EITHER THE CONTRACT CHANGED OR THERE WAS A
GAP IN COVERAGE OF > 28 DAYS
September 29, 2014 at 3:44 pm
Shouting isn't necessary... all caps is hard to read...
okay... this is a classic gaps & islands question - well covered in Itzik Ben-Gan's book on T-SQL 2012 Window functions, and in this article:
http://sqlmag.com/sql-server-2012/solving-gaps-and-islands-enhanced-window-functions
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply