Combining Master and Detail Records In New Table

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  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:

  CUSTOMERNBRCONTRACTSUBCONTRACTSTARTDATEENDDATEEFLAGHFLAG

  10001000101A910400801/01/201401/31/2014

  10001000101A910400802/01/201403/21/2014H

  10001000101A910400804/01/201404/30/2014

  10001000101A910400805/01/201405/31/2014H

  10001000101A910400806/01/201412/31/2099E

  10001000102A555500101/01/201403/31/2014E

  10001000102A555500104/01/201404/30/2014EH

  10001000102A555500105/01/201406/30/2014E

  10001000102A555500107/01/201412/31/2099

  10001000103A666600401/01/201410/01/2014

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  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.