-
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
Last edited by chrisrlong; 11-12-2002 at 02:20 PM.
-
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...
Jeff Hunter
-
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.
-
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.
Originally posted by marist89
That darn ChrisRLong... I was typing in that exact answer and he beat me to it...
Hehe
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.
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 ] & let you know about ur sql.
Thanks
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
|