| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-13-10, 01:21
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 52
|
|
|
Help with date range query..
|
|
Hi Guys,
I have a table which contains start date and end date. Given a date range, it should give me the date range expect the date range present in the table
For an example,
If the table is as shown below.
Code:
Start_date End_date
3-jan-2010 7-jan-2010
13-jan-2010 19-jan-2010
Given the start date as 1-jan-2010 and end date as 25-jan-2010. Then the output should be as
Code:
Start_date End_date
1-jan-2010 2-jan-2010
8-jan-2010 12-jan-2010
20-jan-2010 25-jan-2010
I was able to create all the date from start to end date. But unable to get the desired output. The code i used is
Code:
with temp(date) as
(select '1-jan-2010' as date
from dual
union all
select date+1 days from temp
where date <='25-jan-2010')
select * from temp
Hope i made myself clear.
Thanks for your help in advance.
Magesh.
|
|
07-13-10, 06:19
|
|
Registered User
|
|
Join Date: Jun 2007
Location: germany
Posts: 96
|
|
|
__________________
Dick Brenholtz, Ami in Deutschland
|
|
07-13-10, 06:32
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
|
|
Here is an example considering the case where given start date or end date was overlapped with existing date range.
(I thought that this query would be shorter by using LAG/LEAD OLAP specifications.)
assumption:
No overlaps between existing ranges.
Code:
------------------------------ Commands Entered ------------------------------
WITH
/**************************************************
********** Start of test data **********
**************************************************/
given_range(start_date , end_date) AS (
SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
, DATE( TO_DATE( end_date , 'dd-mon-yyyy') )
FROM (VALUES
( '3-jan-2010' , '7-jan-2010')
, ('13-jan-2010' , '19-jan-2010')
, ( '2-feb-2010' , '5-feb-2010')
) s(start_date , end_date)
)
, start_end(start_date , end_date) AS (
SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
, DATE( TO_DATE( end_date , 'dd-mon-yyyy') )
FROM (VALUES
('1-jan-2010' , '25-jan-2010')
-- ('5-jan-2010' , '25-jan-2010') /* start_date was overlapped with existing range ( '3-jan-2010' , '7-jan-2010') */
-- ('1-jan-2010' , '15-jan-2010') /* end_date was overlapped with existing range ('13-jan-2010' , '19-jan-2010') */
-- ('1-jan-2010' , '10-feb-2010')
) s(start_date , end_date)
)
/**************************************************
********** End of test data **********
**************************************************/
SELECT end_date + 1 DAY AS start_date
, start_date - 1 DAY AS end_date
FROM (SELECT start_date
, ROW_NUMBER() OVER(ORDER BY start_date) AS rn
FROM
(SELECT gr.start_date
FROM given_range gr
, start_end se
WHERE gr.start_date
BETWEEN se.start_date AND se.end_date
UNION ALL
SELECT se.end_date + 1 DAY
FROM start_end se
WHERE NOT EXISTS
(SELECT 0
FROM given_range gr
WHERE se.end_date
BETWEEN gr.start_date AND gr.end_date
)
) s(start_date)
) s
INNER JOIN
(SELECT end_date
, ROW_NUMBER() OVER(ORDER BY end_date) AS rn
FROM
(SELECT gr.end_date
FROM given_range gr
, start_end se
WHERE gr.end_date
BETWEEN se.start_date AND se.end_date
UNION ALL
SELECT se.start_date - 1 DAY
FROM start_end se
WHERE NOT EXISTS
(SELECT 0
FROM given_range gr
WHERE se.start_date
BETWEEN gr.start_date AND gr.end_date
)
) e(end_date)
) e
ON e.rn = s.rn
;
------------------------------------------------------------------------------
START_DATE END_DATE
---------- ----------
2010-01-01 2010-01-02
2010-01-08 2010-01-12
2010-01-20 2010-01-25
3 record(s) selected.
|
Last edited by tonkuma; 07-13-10 at 07:47.
Reason: Add an assumption. Change UNION to UNION ALL.
|
07-13-10, 07:39
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Another example using LEAD OLAP specification.
Code:
------------------------------ Commands Entered ------------------------------
WITH
/**************************************************
********** Start of test data **********
**************************************************/
given_range(start_date , end_date) AS (
SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
, DATE( TO_DATE( end_date , 'dd-mon-yyyy') )
FROM (VALUES
( '3-jan-2010' , '7-jan-2010')
, ('13-jan-2010' , '19-jan-2010')
, ( '2-feb-2010' , '5-feb-2010')
) s(start_date , end_date)
)
, start_end(start_date , end_date) AS (
SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
, DATE( TO_DATE( end_date , 'dd-mon-yyyy') )
FROM (VALUES
('1-jan-2010' , '25-jan-2010')
-- ('5-jan-2010' , '25-jan-2010') /* start_date was overlapped with existing range ( '3-jan-2010' , '7-jan-2010') */
-- ('1-jan-2010' , '15-jan-2010') /* end_date was overlapped with existing range ('13-jan-2010' , '19-jan-2010') */
-- ('1-jan-2010' , '10-feb-2010')
) s(start_date , end_date)
)
/**************************************************
********** End of test data **********
**************************************************/
SELECT start_date
, end_date
FROM (SELECT end_date + 1 DAY AS start_date
, LEAD(start_date)
OVER(ORDER BY start_date) - 1 DAY AS end_date
FROM (SELECT gr.start_date
, gr. end_date
FROM given_range gr
, start_end se
WHERE gr.start_date BETWEEN se.start_date AND se.end_date
OR gr. end_date BETWEEN se.start_date AND se.end_date
UNION ALL
SELECT DATE('0001-01-02')
, start_date - 1 DAY
FROM start_end se
WHERE NOT EXISTS
(SELECT 0
FROM given_range gr
WHERE se.start_date
BETWEEN gr.start_date AND gr.end_date
)
UNION ALL
SELECT end_date + 1 DAY
, DATE('9999-12-30')
FROM start_end se
WHERE NOT EXISTS
(SELECT 0
FROM given_range gr
WHERE se.end_date
BETWEEN gr.start_date AND gr.end_date
)
) s(start_date , end_date)
)
WHERE end_date IS NOT NULL
ORDER BY
start_date
;
------------------------------------------------------------------------------
START_DATE END_DATE
---------- ----------
2010-01-01 2010-01-02
2010-01-08 2010-01-12
2010-01-20 2010-01-25
3 record(s) selected.
|
|
07-13-10, 08:24
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 52
|
|
Thanks tonkuma.... can you please explain me the logic you have done.. thanks once again 
|
|
07-14-10, 01:09
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 52
|
|
Tonkuma, i found one problem with the query.. If we have a overlapping range in the table (given_range of yours), then the result is coming wrongly.
For an example, if the given_range table contains
Quote:
3-jan-2010 5-jan-2010
4-jan-2010 15-jan-2010
|
Then it should consider as as both of them overlaps.
|
|
07-14-10, 05:24
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Yes.
I had added an assumption on my first post in this thread.
Quote:
assumption:
No overlaps between existing ranges.
|
If we have a overlapping range in the table, we should first remove the overlapping.
Here is an example(I change the table name to range_table):
Code:
------------------------------ Commands Entered ------------------------------
WITH
/**************************************************
********** Start of test data **********
**************************************************/
range_table(start_date , end_date) AS (
SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
, DATE( TO_DATE( end_date , 'dd-mon-yyyy') )
FROM (VALUES
( '3-jan-2010' , '5-jan-2010')
, ( '4-jan-2010' , '15-jan-2010')
, ( '2-feb-2010' , '5-feb-2010')
, ( '5-mar-2010' , '10-mar-2010')
, ( '8-mar-2010' , '15-mar-2010')
, ('14-mar-2010' , '20-mar-2010')
) s(start_date , end_date)
)
/**************************************************
********** End of test data **********
**************************************************/
, range_no_overlap(start_date , end_date) AS (
SELECT start_date , end_date
FROM (SELECT start_date
, ROW_NUMBER() OVER(ORDER BY start_date) rn_s
FROM range_table q
WHERE NOT EXISTS
(SELECT 0
FROM range_table r
WHERE q.start_date > r.start_date
AND q.start_date < r.end_date
)
) s
INNER JOIN
(SELECT end_date
, ROW_NUMBER() OVER(ORDER BY end_date) rn_e
FROM range_table q
WHERE NOT EXISTS
(SELECT 0
FROM range_table r
WHERE q.end_date > r.start_date
AND q.end_date < r.end_date
)
) e
ON rn_e = rn_s
)
SELECT * FROM range_no_overlap
;
------------------------------------------------------------------------------
START_DATE END_DATE
---------- ----------
2010-01-03 2010-01-15
2010-02-02 2010-02-05
2010-03-05 2010-03-20
3 record(s) selected.
|
Last edited by tonkuma; 07-14-10 at 05:40.
|
07-14-10, 17:41
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
Here is an example to be considered overlapping ranges in the range table.
Example:
Changed the table names and sample data.
Result multiple periods at once.
Code:
WITH
/**************************************************
********** Start of test data **********
**************************************************/
range_table(start_date , end_date) AS (
SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
, DATE( TO_DATE( end_date , 'dd-mon-yyyy') )
FROM (VALUES
( '3-jan-2010' , '5-jan-2010')
, ( '4-jan-2010' , '15-jan-2010')
, ( '2-feb-2010' , '5-feb-2010')
, ( '5-mar-2010' , '10-mar-2010')
, ( '8-mar-2010' , '15-mar-2010')
, ('14-mar-2010' , '20-mar-2010')
) s(start_date , end_date)
)
, period(start_date , end_date) AS (
SELECT DATE( TO_DATE(start_date , 'dd-mon-yyyy') )
, DATE( TO_DATE( end_date , 'dd-mon-yyyy') )
FROM (VALUES
('1-jan-2010' , '31-mar-2010')
, ('5-jan-2010' , '31-mar-2010') /* start_date was overlapped with ( '4-jan-2010' , '15-jan-2010') */
, ('1-jan-2010' , '12-mar-2010') /* end_date was overlapped with ( '8-mar-2010' , '15-mar-2010') */
) s(start_date , end_date)
)
/**************************************************
********** End of test data **********
**************************************************/
SELECT pd.start_date AS period_start
, pd. end_date AS period_end
, rs.end_date + 1 DAY AS start_date
, COALESCE(
(SELECT MIN(rt.start_date) - 1 DAY
FROM range_table rt
WHERE rt.start_date
BETWEEN rs.end_date AND pd.end_date
AND NOT EXISTS -- Remove overlapping ranges
(SELECT 0
FROM range_table rt_e
WHERE rt.start_date > rt_e.start_date
AND rt.start_date < rt_e.end_date
)
)
, pd.end_date
) AS end_date
FROM period pd
CROSS JOIN
LATERAL
(SELECT rt.end_date
FROM range_table rt
WHERE rt.end_date
BETWEEN pd.start_date AND pd.end_date
AND NOT EXISTS -- Remove overlapping ranges
(SELECT 0
FROM range_table rt_e
WHERE rt.end_date > rt_e.start_date
AND rt.end_date < rt_e.end_date
)
UNION ALL
SELECT pd.start_date - 1 DAY
FROM sysibm.sysdummy1
WHERE NOT EXISTS
(SELECT 0
FROM range_table rt
WHERE pd.start_date
BETWEEN rt.start_date AND rt.end_date
)
) rs(end_date)
ORDER BY
period_start
, period_end
, start_date
;
------------------------------------------------------------------------------
PERIOD_START PERIOD_END START_DATE END_DATE
------------ ---------- ---------- ----------
2010-01-01 2010-03-12 2010-01-01 2010-01-02
2010-01-01 2010-03-12 2010-01-16 2010-02-01
2010-01-01 2010-03-12 2010-02-06 2010-03-04
2010-01-01 2010-03-31 2010-01-01 2010-01-02
2010-01-01 2010-03-31 2010-01-16 2010-02-01
2010-01-01 2010-03-31 2010-02-06 2010-03-04
2010-01-01 2010-03-31 2010-03-21 2010-03-31
2010-01-05 2010-03-31 2010-01-16 2010-02-01
2010-01-05 2010-03-31 2010-02-06 2010-03-04
2010-01-05 2010-03-31 2010-03-21 2010-03-31
10 record(s) selected.
Relationships of sample data:
Code:
Jan Feb Mar Apr
0 1 2 3 0 1 2 0 1 2 3 0
1234567890123456789012345678901 1234567890123456789012345678 1234567890123456789012345678901 123456789
------------------------------- ---------------------------- ------------------------------- ---------
<-> ( '3-jan-2010' , '5-jan-2010')
<----------> ( '4-jan-2010' , '15-jan-2010')
<--> ( '2-feb-2010' , '5-feb-2010')
<----> ( '5-mar-2010' , '10-mar-2010')
<------> ( '8-mar-2010' , '15-mar-2010')
<-----> ('14-mar-2010' , '20-mar-2010')
<------------------------------ ---------------------------- ------------------------------> period (1)
<-------------------------- ---------------------------- ------------------------------> period (2)
<------------------------------ ---------------------------- -----------> period (3)
------------------------------- ---------------------------- ------------------------------- ---------
0 1 2 3 0 1 2 0 1 2 3 0
1234567890123456789012345678901 1234567890123456789012345678 1234567890123456789012345678901 123456789
Jan Feb Mar Apr
This is a formatted output by modifying the sample query:
Code:
PERIOD_START PERIOD_END START_DATE END_DATE
------------ ---------- ---------- ----------
2010-01-01 2010-03-12 2010-01-01 2010-01-02
2010-01-01 2010-03-12 2010-01-16 2010-02-01
2010-01-01 2010-03-12 2010-02-06 2010-03-04
---------- ---------- ---------- ----------
2010-01-01 2010-03-31 2010-01-01 2010-01-02
2010-01-01 2010-03-31 2010-01-16 2010-02-01
2010-01-01 2010-03-31 2010-02-06 2010-03-04
2010-01-01 2010-03-31 2010-03-21 2010-03-31
---------- ---------- ---------- ----------
2010-01-05 2010-03-31 2010-01-16 2010-02-01
2010-01-05 2010-03-31 2010-02-06 2010-03-04
2010-01-05 2010-03-31 2010-03-21 2010-03-31
---------- ---------- ---------- ----------
13 record(s) selected.
Modified query in the example:
Code:
SELECT SUBSTR( CHAR(pd.start_date) || '----------' , 1 + GROUPING(rs.end_date) * 10 , 10 ) AS period_start
, SUBSTR( CHAR(pd. end_date) || '----------' , 1 + GROUPING(rs.end_date) * 10 , 10 ) AS period_end
, COALESCE( CHAR(rs.end_date + 1 DAY) , '----------' ) AS start_date
, COALESCE(
(SELECT CHAR( MIN(rt.start_date) - 1 DAY )
FROM range_table rt
WHERE rt.start_date
BETWEEN rs.end_date AND pd.end_date
AND NOT EXISTS -- Remove overlapping ranges
(SELECT 0
FROM range_table rt_e
WHERE rt.start_date > rt_e.start_date
AND rt.start_date < rt_e.end_date
)
)
, SUBSTR( CHAR(pd.end_date) || '----------' , 1 + GROUPING(rs.end_date) * 10 , 10 )
) AS end_date
FROM period pd
...
...
GROUP BY -- Added.
GROUPING SETS( (pd.start_date , pd.end_date) -- Added.
, (pd.start_date , pd.end_date , rs.end_date) ) -- Added
ORDER BY
pd.start_date
, pd. end_date
, rs. end_date
;
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Help with date range query..
