-
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..........
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
Last edited by taskchat; 11-12-2002 at 12:33 PM.
-
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
Last edited by chrisrlong; 11-12-2002 at 02:19 PM.
-
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?
Jeff Hunter
-
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?
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|