Date Split - Sql help

hi Sql Gurus,
This is challenging sql for me , i hope you could solve this.

I have a table with following info
SDate EDate Value
10/10/2002 10/15/2002 15
10/12/2002 10/14/2002 28
10/20/2002 10/30/2002 30

Sql should return like this
SDate EDate Value
10/10/2002 10/11/2002 15
10/12/2002 10/14/2002 28
10/15/2002 10/15/2002 15
10/20/2002 10/30/2002 30

The SQL is to split the records if the date range is overlapped. If it's fully overlapped sql should return one record for the overlapping.

Any help/suggestion is appreciated.
[Cursor/dynamic sql also okay for me]

Thanks
Senthil

Okay, you may need to provide some more info here. First of all, this is an extremely wierd request. You may want to seriously look at your table design here. Regardless, you need to define some rules. Since rows 1 and 2 both cover the same days, why does row 2 win out?

- Chris

Chris,
The application has to accept overlapping date. While viewing the data, the screen has to split the date & show in the above format.
Actaully i've one more table that has data for each days.

ie in the first table i store
SDate EDate Value
10/10/2002 10/15/2002 15

in the second table i store
date value
10/10/2002 15
10/11/2002 15
...
10/15/2002 15

If the date is overlapped in the first table, the corresponding record in the second table will be replaced with the newly overlapped records.

Hope to get a solution.

Okay, I'm still confused. The second table sounds like a results table, but then why did you ask for a result in a different format (in your first query)? If it isn't a results table, then what is it?

Secondly, I don't believe you answered my question. If 2 date ranges overlap in the first table, what is the rule for which range wins for which dates? What if there are 3 overlapping ranges?

Please provide as much detail as possible.

- Chris

You are correct chris. The second table is result table & first table is main table. I give you an example to make this clear.

@10 am User creates an entry for date range 10/10/2002 to 10/15/2002 with qty 15. After 1 hour or next day, he thinks few dates should contain high qty so creates one more record for date range 10/12/02 to 10/14/2002 with qty 28.

ie last entry always wins

After he saves the record through gui, he will expect to see in the below format

10/10/2002 10/11/2002 15
10/12/2002 10/14/2002 28
10/15/2002 10/15/2002 15

As per spec, once user added a record, he cannot update.
If i get a solution for this, i would solve my performance issue. Please help me.......... :confused:

Have a nice day

Okay, now define 'last record'. You provided no sequenced column or date column to obviously define what 'last record' meant.

- Chris

For good html reply please click this link
http://www26.brinkster.com/kec9498/MainResult.htm

Each records in main & result table has RowID ie Sequence & main table has Submission ID which identifies the unique submission.

Similarly each record has last user id & last time stamp ie who created & when it's created.

ie @10AM records in Main & result tables are

Main Table
ID SDate EDate qty SubmissionID lastuserid lasttimestamp
10 10/10/02 10/15/2002 15 3 xx xx-xx-xxxx
11 10/20/02 10/30/2002 30 3 xx xx-xx-xxxx

User has added 2 records in main table, the submission ID is 3[which is unique for each submission]

Result Table
ID MainTableID Date qty lastuserid lasttimestamp
1 10 10/10/02 15 xx xx-xx-xxxx
2 10 10/11/02 15 xx xx-xx-xxxx
3 10 10/12/02 15 xx xx-xx-xxxx
4 10 10/13/02 15 xx xx-xx-xxxx
5 10 10/14/02 15 xx xx-xx-xxxx
6 10 10/15/02 15 xx xx-xx-xxxx
6 11 10/20/02 30 xx xx-xx-xxxx
7 11 10/21/02 30 xx xx-xx-xxxx
--
--
10 11 10/30/02 30 xx xx-xx-xxxx

After 1hr or next day user adds one more record which overlaps with the existing record with the date range 10/12 to 10/14 qty 28.
Now the Main & Result table data look like,

Main Table
ID SDate EDate qty SubmissionID lastuserid lasttimestamp
10 10/10/02 10/15/2002 15 3 xx xx-xx-xxxx
11 10/20/02 10/30/2002 30 3 xx xx-xx-xxxx
12 10/12/02 10/14/2002 28 4 xx xx-xx-xxxx

User has added 1 record in main table, the submission ID is 4

Result Table
ID MainTableID Date qty lastuserid lasttimestamp
1 10 10/10/02 15 xx xx-xx-xxxx
2 10 10/11/02 15 xx xx-xx-xxxx
20 12 10/12/02 28 xx xx-xx-xxxx
21 12 10/13/02 28 xx xx-xx-xxxx
22 12 10/14/02 28 xx xx-xx-xxxx
6 10 10/15/02 15 xx xx-xx-xxxx
6 11 10/20/02 30 xx xx-xx-xxxx
7 11 10/21/02 30 xx xx-xx-xxxx
--
--
10 11 10/30/02 30 xx xx-xx-xxxx

I've not missed anything in table schema.

In GUI, i've to display in this format..
10/10/2002 10/11/2002 15
10/12/2002 10/14/2002 28
10/15/2002 10/15/2002 15

Last submission always wins....

Thanks,
Senthil

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:

Code:

---

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

Quote:

---

Originally posted by 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:

Code:

---

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

---

Geez, That's the EASY solution?

Quote:

---

Originally posted by marist89
Geez, That's the EASY solution?

---

Uh, yeah. Why? ;)

See, the hardest solution would be to produce the resultset directly from the MainTable in a single SQL. With the existence of the Result table, we get to use 3 statements. With the existence of a stored LastSubmissionID value, the first 2 statements become much faster.

See....easy. Right? :D

- Chris

Okay, now here's the tricky part. We have to turn a table with a date for each row into a resultset of date ranges. This is not easy in SQL. Note that a PL/SQL solution would be much easier, albeit slower. The PL/SQL solution is fairly obvious, so we'll skip that, unless you specifically request one.

I made the tables and data
CREATE TABLE RESULT_T
(ID NUMBER, MAINTABLE_ID NUMBER, TARGET_DT DATE, QTY NUMBER);


INSERT INTO RESULT_T VALUES(1, 10, '10-OCT-02', 15);
INSERT INTO RESULT_T VALUES(2, 10, '11-OCT-02', 15 );
INSERT INTO RESULT_T VALUES(20, 12, '12-OCT-02', 28);
INSERT INTO RESULT_T VALUES(21, 12, '13-OCT-02', 28);
INSERT INTO RESULT_T VALUES(22, 12, '14-OCT-02', 28);
INSERT INTO RESULT_T VALUES(6, 10, '15-OCT-02', 15);
INSERT INTO RESULT_T VALUES(6, 11, '20-OCT-02', 30);
INSERT INTO RESULT_T VALUES(7, 11, '21-OCT-02', 30);

Here is one possible SQL solution.

Code:

---

  -- Finally, we have all the information we need in each 'start range' record.
  --      So, we restrict to only thos records.
  --      Since it is possible for a record to be *both* the start *and* end of
  --        a range, we must use the CASE to display the appropriate End Target_DT.
SELECT
  T3.Target_DT
      AS BDate  ,
  CASE
      WHEN
        STOP_RANGE_FL  IS NULL
      THEN
        END_RANGE_DATE
      ELSE
        Target_DT
  END
      AS EDate  ,
  QTY
FROM
  (
      -- Restrict to only those records
      --      and get each range's end-Date
  SELECT
      T2.*  ,
      LEAD(Target_DT)
        OVER
            (
            ORDER BY
              Target_DT
            )
        AS END_RANGE_DATE
  FROM
      (
        -- Determine which records are at the beginning and end of the ranges
      SELECT
        T1.*  ,
        CASE
            WHEN
              Target_DT  <>  PREV_DATE + 1  OR
              QTY        <>  PREV_QTY        OR
              PREV_DATE  IS  NULL       
            THEN
              1
        END
            AS  START_RANGE_FL  ,
        CASE
            WHEN
              Target_DT  <>  NEXT_DATE - 1  OR
              QTY        <>  NEXT_QTY        OR
              NEXT_DATE  IS  NULL         
            THEN
              1
        END
            AS  STOP_RANGE_FL 
      FROM
        (
            -- Get the prev and next dates and quantities for comparisons
        SELECT
            R.*              ,
            LAG(Target_DT)
              OVER
                  (
                  ORDER BY
                    Target_DT
                  )
              AS PREV_DATE  ,
            LAG(QTY)
              OVER
                  (
                  ORDER BY
                    Target_DT
                  )
              AS PREV_QTY  ,
            LEAD(Target_DT)
              OVER
                  (
                  ORDER BY
                    Target_DT
                  )
              AS NEXT_DATE  ,
            LEAD(QTY)
              OVER
                  (
                  ORDER BY
                    Target_DT
                  )
              AS NEXT_QTY
        FROM
            Result_T  R
        )  T1
      )  T2
  WHERE
      T2.START_RANGE_FL  =  1  OR
      T2.STOP_RANGE_FL  =  1
  ) T3
WHERE
  START_RANGE_FL  =  1

---

Let me know if this works for you,

- Chris

BTW - replaced the tabs in my first post with space so it's more readable.

Hi Chris

Do you know if SQL Server 2k supports case expressions and analytic functions? (I know DB2 supports analytic functions but not other rdbms)

That darn ChrisRLong... I was typing in that exact answer and he beat me to it... ;)

Hi Chris,

While you're at it, can you write a short proof disproving Einstein's Theory of Relativity? And if you have something on prime factorization or finding the next largest Mersenne prime, that would be cool too. If you have time, that is. Thanks.

Quote:

---

Originally posted by pando
Do you know if SQL Server 2k supports case expressions and analytic functions? (I know DB2 supports analytic functions but not other rdbms)

---

SQLServer has supported CASE for forever (although the syntax is slightly different, if I remember), but not analytic functions. This can probably be done without them, but it would be much more difficult and expensive.

Quote:

---

Originally posted by marist89
That darn ChrisRLong... I was typing in that exact answer and he beat me to it... ;)

---

Hehe

Quote:

---

Originally posted by stecal
While you're at it, can you write a short proof disproving Einstein's Theory of Relativity? And if you have something on prime factorization or finding the next largest Mersenne prime, that would be cool too. If you have time, that is. Thanks.

---

Uh, no problem. I'll, uh, get right on that ;)

- Chris

Chris,
I think your last solution may work for me. Tomorrow once i go to office, i will try your sql. :)
I've one sql but that doesn't work for overlapping dates, can you check it out? i will post it tomorrow.
:cool:
Thanks yaa

This is the sql i tried, but it didn't work for overlapping..

Select
Res.MainTable_ID,
out.Start_Date,
out.End_Date,
Res.Qty

From(
Select
Min(Result_id) ResID,
Min(gas_day_dt) Start_Date,
Max(gas_day_dt) ERes_Date
from ResultTable
Group by MainTable_ID
) out,
ResultTable Res,
MainTable Main
Where Res.Result_id = out.Result_id
and Res.MainTable_ID = Main.MainTable_ID

I don't understand what you wnt me to look at. It won't work. You already found that out. What else do you want to know? I can tell you that even if it did work, it would be inefficient because you don't need the MainTable join on the outside. Actually, you wouldn't even need 2 levels at all if it had worked. But again, it doesn't, so it doesn't matter.

And why do you say that only the 'last' solution will work for you? Did you already have SQL to properly populate the Result_T table? If so, why did you even mention the MainTable in the original question? I dis-like working up such hefty SQL for no reason. (well okay, it was a little fun:)) If you already have that SQL, I'd be willing to bet that mine's faster anyway (unless you're using the new MERGE command, which would likely be faster). ;)

Let me know how it goes,

- Chris

I got an error message saying "feature not enabled: OLAP window function". I thing there should be a setting to incorporate olap functions.. I've to talk to my DBA [oh god :o] & let you know about ur sql.
Thanks