Merging timeseries data to a timeseries(FromDate, ToDate) table

• chandrakant_gaurav

  Right there with Babe

  Points: 799

  More actions

  January 25, 2017 at 8:00 am

  #334595

  Hey, I have a real life problem where I received some files initially and based on those files, history was generated like below table and was pushed to production:
  TableName - SymbolHistory_tbl
  ColumnNames - SymbolTypeID INT, SymbolValue varchar, FromDate datetime. ToDate Datetime

  After few months we received another file which has only the data which was missing as well as some repeated data in the first file (rows missing in millions). I generated the history only by using this file-
  TableName - SymbolHistory_tbl_GapData
  ColumnNames - SymbolTypeID INT, SymbolValue varchar, FromDate datetime. ToDate Datetime

  Now I am facing huge challenge in merging these SymbolHistory_tbl_GapData -  into - SymbolHistory_tbl.

  There are huge number of records and nothing can be done manually.
  Please help or suggest any link which can help me solve this.

  Thanks

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 25, 2017 at 8:12 am

  #1924866

  I'm almost certainly missing something here, but could it be as simple as this?

  INSERT INTO SymbolHistory_tbl (
    SymbolTypeID
,   SymbolValue
,   FromDate
,   ToDate
    )
SELECT
    SymbolTypeID
,   SymbolValue
,   FromDate
,   ToDate
FROM SymbolHistory_tbl_GapData

  John

• chandrakant_gaurav

  Right there with Babe

  Points: 799

  More actions

  January 25, 2017 at 8:17 am

  #1924870

  John Mitchell-245523 - Wednesday, January 25, 2017 8:12 AM

  I'm almost certainly missing something here, but could it be as simple as this?

  INSERT INTO SymbolHistory_tbl (
    SymbolTypeID
,   SymbolValue
,   FromDate
,   ToDate
    )
SELECT
    SymbolTypeID
,   SymbolValue
,   FromDate
,   ToDate
FROM SymbolHistory_tbl_GapData

  John

  You missed the fact that these are timeseries tables (FromDate, toDate columns). They should not overlap. after merging there should be not duplicate for any date for any SymbolTypeiD. Thanks

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  January 25, 2017 at 8:22 am

  #1924871

  If a simple insert won't work, we'll need a lot more information starting with sample data and expected results as outlined in the first link in my signature.

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 25, 2017 at 8:24 am

  #1924873

  But if, as you said, the data in the second table is data that was missing from the first table, there won't be any duplicates.  Unless I'm missing something else, that is.  This is difficult to visualise - I'm sure some sample data would make it a lot easier, please.

  John

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  January 25, 2017 at 9:54 am

  #1924908

  chandrakant_gaurav - Wednesday, January 25, 2017 8:00 AM

  Hey, I have a real life problem where I received some files initially and based on those files, history was generated like below table and was pushed to production:
  TableName - SymbolHistory_tbl
  ColumnNames - SymbolTypeID INT, SymbolValue varchar, FromDate datetime. ToDate Datetime

  After few months we received another file which has only the data which was missing as well as some repeated data in the first file (rows missing in millions). I generated the history only by using this file-
  TableName - SymbolHistory_tbl_GapData
  ColumnNames - SymbolTypeID INT, SymbolValue varchar, FromDate datetime. ToDate Datetime

  Now I am facing huge challenge in merging these SymbolHistory_tbl_GapData -  into - SymbolHistory_tbl.

  There are huge number of records and nothing can be done manually.
  Please help or suggest any link which can help me solve this.

  Thanks

  Try:
  
MERGE SymbolHistory_tbl TargetTab
USING SymbolHistory_tbl_GapData SourceTab
ON TargetTab.SymbolTypeID = SourceTab.SymbolTypeID
AND TargetTab.FromDate= SourceTab.FromDate
AND TargetTab.ToDate= SourceTab.ToDate
WHEN MATCHED THEN UPDATE SET SymbolValue=SourceTab.SymbolValue
WHEN NOT MATCHE THEN INSERT (SymbolTypeID, SymbolValue, FromDate, ToDate)
VALUES (SourceTab.SymbolTypeID, SourceTab.SymbolValue, SourceTab.FromDate, SourceTab.ToDate);