Himu\’s Attempt at Blogging

Tidbits from my thoughts

SQL – Dynamic Date Range, Number Range in Oracle, SQL Server, and DB2/400

with 9 comments

Often, we need to make coverage reports that span over a period of time and where all points in the period do not contain data.

Assume a shop has 50 outlets and we want to find the number of sales in each branch over the past ten days. Business is bad in some areas and so some outlets had no sale at all. A query like the following will give only those outlets that had sales but not a list of all outlets:

SELECT outletid, saledate, count(*)
FROM sales
GROUP BY outletid, saledate
ORDER BY outletid, saledate

If we had a date or time table then we could outer join with it to get the desired result:

SELECT outletid, thedate, count(*)
FROM dates left outer join sales on dates.thedate = sales.saledate
GROUP BY outletid, thedate
ORDER BY outletid, thedate

Unfortunately, operational databases do not contain such date dimensions (data warehouses do) and creating a temporary or utility table for this purpose is always not possible or feasible. It would be much better if we could generate the list of dates dynamically in SQL which automatically gets thrown away.

In this post, I am going to show you how to dynamically generate

  • List of dates
  • Range bands of dates

in

  1. DB2/400
  2. SQL Server
  3. Oracle

Similar logic can be used for generating list or band of numbers – the queries will be much simpler.

0 – Notes

  • Parameter markers for Oracle and DB2/400 is ‘: ’ while for SQL Server it is ‘@’; also in the code below, I’ve put a space between the ‘:’ and parameter name so that WordPress doesn’t convert it to a smiley 😛
  • When there is no table to be provided,
    • Oracle uses DUAL
    • DB2/400 uses sysibm.sysdummy1
    • SQL Server simply doesn’t require anything
  • For a parameter in the SELECT clause, DB2/400 requires it to be cast to the intended type; otherwise it throws an error
  • Due to the default maximum recursion level of 100 for CTE’s in SQL Server, your list or range band will be limited – there is some OPTION (MAXRECURSION n) thing but don’t know about it
  • For Oracle, TRUNC is needed in the date-parameterized queries to clear off any time value which will cause the result to be an INTERVAL type triggering ORA-30081 error
  • The LEAST function used in Oracle is good for bounding the range to the end date. Similar can be achieved in DB2/400 and SQL Server using the CASE WHEN x < y THEN x ELSE y END syntax and modifying the < (less than) condition in the WHERE clause to <= (less than or eq). But I am not including that to keep the queries comprehendible.

1 – List of Dates

DB2/400 – Direct

WITH DateRange(dt) AS
(
SELECT date('2009-01-01') dt FROM sysibm.sysdummy1
UNION ALL
SELECT (dt + 1 day) dt FROM DateRange WHERE dt < date('2009-01-10')
)
SELECT dt FROM DateRange

DB2/400 – Parameterized

WITH DateRange(dt) AS
(
SELECT CAST(: P_START AS DATE) dt FROM sysibm.sysdummy1
UNION ALL
SELECT (dt + : P_GAP days) dt FROM DateRange WHERE dt + : P_GAP days < : P_END
)
SELECT dt start_dt, dt + (: P_GAP-1) days end_dt FROM DateRange

SQL Server – Direct

WITH DateRange(dt) AS
(
SELECT CONVERT(datetime, '2009-01-01') dt
UNION ALL
SELECT DATEADD(dd,1,dt) dt FROM DateRange WHERE dt < CONVERT(datetime, '2009-01-10')
)
SELECT dt FROM DateRange

SQL Server – Parameterized

WITH DateRange(dt) AS
(
SELECT @StartDate dt
UNION ALL
SELECT DATEADD(dd,1,dt) dt FROM DateRange WHERE dt < @EndDate
)
SELECT dt FROM DateRange

Oracle – Direct

SELECT TO_DATE('2009-05-01','YYYY-MM-DD')+LEVEL-1 as the_date
FROM dual
CONNECT BY LEVEL <=
TO_DATE('2009-05-10','YYYY-MM-DD') - TO_DATE('2009-05-01','YYYY-MM-DD') + 1

Oracle – Parameterized

SELECT : P_START + LEVEL - 1 AS the_date
FROM dual
CONNECT BY LEVEL <= TRUNC(: P_END) - TRUNC(: P_START) + 1

2 – Range Band of Dates

DB2/400 – Direct

WITH DateRange(dt) AS
(
SELECT date('2009-01-01') dt FROM sysibm.sysdummy1
UNION ALL
SELECT (dt + 10 days) dt FROM DateRange WHERE dt + 10 days < date('2009-02-20')
)
SELECT dt start_dt, dt + 9 days end_dt FROM DateRange

DB2/400 – Parameterized

WITH DateRange(dt) AS
(
SELECT cast(: P_START as date) dt FROM sysibm.sysdummy1
UNION ALL
SELECT (dt + : P_GAP days) FROM DateRange WHERE dt + : P_GAP days < : P_END
)
SELECT dt start_dt, dt + (: P_GAP-1) days end_dt FROM DateRange

SQL Server – Direct

WITH DateRange(dt) AS
(
SELECT CONVERT(datetime, '2009-01-01') dt
UNION ALL
SELECT DATEADD(dd,10,dt) dt FROM DateRange WHERE DATEADD(dd,10,dt) < CONVERT(datetime, '2009-02-20')
)
SELECT dt start_dt, DATEADD(dd,9,dt) end_dt FROM DateRange

SQL Server – Parameterized

WITH DateRange(dt) AS
(
SELECT @StartDate dt
UNION ALL
SELECT DATEADD(dd,@Gap,dt) dt FROM DateRange WHERE DATEADD(dd,@Gap,dt) < @EndDate
)
SELECT dt start_dt, DATEADD(dd,(@Gap-1),dt) end_dt FROM DateRange

Oracle – Direct

SELECT TO_DATE('2009-01-01','YYYY-MM-DD') + ((LEVEL - 1) * 10) start_dt,
<tt>LEAST(TO_DATE('2009-01-01','YYYY-MM-DD') + ((LEVEL - 1) * 10) + 9,
TO_DATE('2009-02-20','YYYY-MM-DD')) end_dt
FROM DUAL
CONNECT BY TO_DATE('2009-01-01','YYYY-MM-DD') + ((LEVEL - 1) * 10)
<= TO_DATE('2009-02-20','YYYY-MM-DD')

 

Oracle – Parameterized

SELECT TRUNC(: P_START) + ((LEVEL - 1) * : P_GAP) start_dt,
LEAST(TRUNC(: P_START) + ((LEVEL - 1) * : P_GAP) + (: P_GAP-1), TRUNC(: P_END)) end_dt
FROM DUAL
CONNECT BY TRUNC(: P_START) + ((LEVEL - 1) * : P_GAP) <= TRUNC(: P_END)

Advertisements

Written by mhimu

May 7, 2009 at 11:03 am

9 Responses

Subscribe to comments with RSS.

  1. What is the &lt value for? I tried your SQL in sql server and oracle, none worked.

    will

    November 5, 2009 at 3:34 am

    • sorry but that is actually the < (less than) symbol

      mhimu

      November 5, 2009 at 2:28 pm

      • I love this bag especially the chain. Sucks it isn't being sold in the states. I have a pretty ok camera case but this bag has coptmrmaents and is a bit more stylish. Your sweater is awesome.De,

        Polly

        April 26, 2017 at 12:54 am

  2. Thanks now worked great!

    But I just realized my SQL Server is at version 2000, a CISCO ACD database.

    So the bad news is I can not use WITH statement, can not create objects in the database, otherwise lose the support.

    Do you know how to accomplish this with SQL Server 2000?

    will

    November 6, 2009 at 4:25 am

  3. How do you apply this technique? Is this used as part of a CREATE FUNCTION statement? Does this work in SQL Server 2000?

    Keith

    November 7, 2009 at 12:56 am

  4. From Date Enter And Last 4 Mounts And Add 4 Months And Balance and date show Queries

    Sandip

    June 3, 2010 at 3:27 pm

  5. Very useful informations for me. Thank you.

    Martin Mareš

    August 28, 2010 at 1:08 am

  6. hi, thanks a lot..
    I tried this on oracle database, its working fine.

    if now I want to left join with some other tables(column_date,column_quantity)
    can show me how to do?

    heng yeow

    October 31, 2011 at 4:35 pm

  7. Unfortunately the following doesn’t work in SQL Server 2000:

    1 WITH DateRange(dt) AS
    2 (
    3 SELECT CONVERT(datetime, ‘2009-01-01’) dt
    4 UNION ALL
    5 SELECT DATEADD(dd,10,dt) dt FROM DateRange WHERE DATEADD(dd,10,dt) < CONVERT(datetime, '2009-02-20')
    6 )
    7 SELECT dt start_dt, DATEADD(dd,9,dt) end_dt FROM DateRange

    Any idea how we can rewrite it for SQL Server 2000?

    mah431

    December 14, 2011 at 10:56 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: