Date Split - Sql help

[chrisrlong]

Okay, we'll go with the easiest solution first.

If you store (in another table), the last submission that was applied to the results table, then this becomes much easier. Another useful component would be a DateDimension_T table. I almost always make one of these. It holds a record for every date (going bacwards and forwards a decade or so usually). There are many advantages to such a table, including being able to easily identify working days, holidays, which days belong to which quarters, etc. In this case, the primary advantage is the ability to convert ranges into rows. So, assuming we have the PrevAppliedSubmissionID somewhere that we can load into a variable (I usually have a GlobalValues_T table for such things), we would do:

UPDATE
   Results_T   R
SET
   (
   MainTableID    ,
   Date           ,
   QTY            ,
   LastUserID     ,
   LastTimestamp
   )   =
   (
   SELECT
      MainTableID    ,
      SQL_Date       ,
      QTY            ,
      LastUserID     ,
      LastTimestamp
   FROM
      (
      SELECT
         MT.MainTableID    ,
         DD.SQL_Date       ,
         MT.QTY            ,
         MT.LastUserID     ,
         MT.LastTimestamp  ,
         ROW_NUMBER()
            OVER (
               PARTITION BY
                  DD.DateDimension_ID
               ORDER BY
                  MT.LastTimestamp DESC
               )
            AS RN
      FROM
         MainTable_T       MT   ,
         DateDimension_T   DD
      WHERE
         MT.Submission_ID   >       :Submission_ID   AND
         DD.SQL_DT         BETWEEN  MT.SDate
                           AND      MT.EDate
      )
   WHERE
      RN   =   1
   )
WHERE
   MT.Submission_ID   >   :Submission_ID


INSERT INTO
   Results_T   R
SELECT
   MainTableID    ,
   QTY            ,
   LastUserID     ,
   LastTimestamp
FROM
   (
   SELECT
      MainTableID    ,
      QTY            ,
      LastUserID     ,
      LastTimestamp  ,
      ROW_NUMBER()
         OVER (
            PARTITION BY
               DD.DateDimension_ID
            ORDER BY
               MT.LastTimestamp DESC
            )
         AS RN
   FROM
      MainTable_T       MT   ,
      DateDimension_T   DD
   WHERE
      MT.Submission_ID   >       :Submission_ID   AND
      DD.SQL_DT         BETWEEN  MT.SDate
                        AND      MT.EDate
   )   T
WHERE
   RN   =   1   AND
   NOT EXISTS   (
      SELECT
         1
      FROM
         Results_T   R2
      WHERE
         R2.Date   =   T.Date
      )

So now we have the results table populated. Oh, and don't forget to update the LastSubmissionID value within the same transaction.

Now, we need to convert these values back into ranges for the output. Give me a minute and I'll craft that for ya.


- Chris