Post reply

HELP NEEDED WRITING GAP RECORDS

  • September 29, 2014 at 3:07 pm

    #397426

    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

    #1748958

    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