Post reply

Combining Master and Detail Records In New Table

  • September 4, 2014 at 11:01 am

    #310694

    CREATE TABLE DHS(CUSTOMERNBR VARCHAR(20), CONTRACT VARCHAR(20), SUBCONTRACT VARCHAR(20) , STARTDATE DATETIME, ENDDATE DATETIME, EFLAG VARCHAR(20), HFLAG VARCHAR(20))

    The data which will be going into this table is from two table which have a 1 to many relationship:

    Here is the 1 side and data:

    CREATE TABLE CUSTOMERS(

    CUSTOMERNBR VARCHAR(20),

    CONTRACT VARCHAR(20),

    SUBCONTRACT VARCHAR(20),

    STARTDATE DATETIME,

    ENDDATE DATETIME DEFAULT '12/31/2099')

    INSERT INTO CUSTOMERS(CUSTOMERNBR, CONTRACT, SUBCONTRACT, STARTDATE) VALUES('10001000101', 'A9104', '008', '01/01/2014')

    INSERT INTO CUSTOMERS(CUSTOMERNBR, CONTRACT, SUBCONTRACT, STARTDATE) VALUES('10001000102', 'A5555', '001', '01/01/2014')

    INSERT INTO CUSTOMERS(CUSTOMERNBR, CONTRACT, SUBCONTRACT, STARTDATE. ENDDATE) VALUES('10001000103', 'A6666', '004', '01/01/2014', '10/01/2014')

    Here is the Many side and data:

    CREATE TABLE FLAGS(CUSTOMERNBR VARCHAR(20), FLAGCODE VARCHAR(20), STARTDATE DATETIME, ENDDATE DATETIME DEFAULT '12/31/2099')

    INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000101', 'H', '02/01/2014', '03/31/2014')

    INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000101', 'H', '05/01/2014', '05/31/2014')

    INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE) VALUES('10001000101', 'E', '06/01/2014')

    INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000102', 'E', '01/01/2014', '06/30/2014')

    INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000102', 'H', '04/01/2014', '04/30/2014')

    The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the

    FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the

    DHS table from combining records in the CUSTOMERS and FLAGS tables:

    CUSTOMERNBR CONTRACT SUBCONTRACT STARTDATE ENDDATE EFLAG HFLAG

    10001000101 A9104 008 01/01/2014 01/31/2014

    10001000101 A9104 008 02/01/2014 03/21/2014 H

    10001000101 A9104 008 04/01/2014 04/30/2014

    10001000101 A9104 008 05/01/2014 05/31/2014 H

    10001000101 A9104 008 06/01/2014 12/31/2099 E

    10001000102 A5555 001 01/01/2014 03/31/2014 E

    10001000102 A5555 001 04/01/2014 04/30/2014 E H

    10001000102 A5555 001 05/01/2014 06/30/2014 E

    10001000102 A5555 001 07/01/2014 12/31/2099

    10001000103 A6666 004 01/01/2014 10/01/2014

  • September 4, 2014 at 12:19 pm

    #1743299

    If what you're intending is to "explode" the range (say 1/1/2014 to 6/1/2014), then the easiest way is probably using an auxiliary Tally table. (Mine is called "Numbers" for some stupid reason.)

    Here's the query:

    SELECT CustomerNbr

    , FlagCode

    , StartDate

    , EndDate

    , DATEDIFF(m,StartDate,DATEADD(d,1,EndDate)) AS MosBetween

    , SomeNumber

    ,DATEADD(m,SomeNumber,StartDate) as TheDate

    FROM Flags CROSS JOIN SCRIDB.dbo.t_Numbers

    WHERE EndDate<='01-Jan-2015'

    AND SCRIDB.dbo.t_Numbers.SomeNumber BETWEEN 1 AND DATEDIFF(m,StartDate,DATEADD(d,1,EndDate));

    The date math might be a little off, but you get the idea. You cross join the table with dates (that you're calculating from) and the Tally or Numbers table and filter using a BETWEEN/WHERE clause.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply