Welcome to EMC Consulting Blogs Sign in | Join | Help

Jim 2.0

PL-SQL: Common date range lookups; Daily, Weekly; MTD; YTD; Last 12 Months

As part of my current role as a tester I am often required to write the same basic expressions again and again in my queries, most noticeably to bring back data for specific date ranges, therefore for the benefit of anyone else out there who has to do likewise I have detailed below my method for returning the following basic ranges:

Daily; Week to date; Month to date; Year to Date; and Rolling year (or Last 12 months). Presently I am working predominantly with Oracle based systems, there for the following is only the PL SQL for the aforementioned ranges.

Daily

As good a starting place as any, the query to return data for the day is the basis from which we can build the other expressions;

    1 Select  a_field

    2 from    a_table

    3 where   the_date = to_date('01/01/2006', 'DD/MM/YYYY')

This will return the data in a_field for the 1st January 2006 only, however to make the query more flexible we can replace the exact data with a variable like '&date' which will prompt the user to enter the date at run time.

The format in the second half of the to_date expression is the format that the date must be entered in and also tells the SQL engine what format the date is being entered in, therefore you can set the format however you like, for example 'MM/DD/YYYY' for the US date format.

One final trick before we get into the other ranges is to account for dates outside of the range for which we have data. This may be required for example when the data load into a data warehouse is a day behind the current date (as data loads often run over night up to and including that day’s data, therefore in the morning the data is current up to the day before). This method will also be used to account for partial ranges in the subsequent queries; e.g. a partial month or week.

    1 Select  a_field

    2 from    a_table

    3 where   the_date =  (case when to_date('&Date', 'DD/MM/YYYY') > trunc(sysdate)-1

    4                     then trunc(sysdate)-1

    5                     else to_date('&Date', 'DD/MM/YYYY')

    6                     end)

The case statement here evaluates the date entered by the user to determine if it is greater than the most recent date for which we have data – in this case the day before today (sysdate is Oracle’s command to return the current date). Note that we truncate sysdate because it is a date/time entry, but we are only interested in the date portion.

Weekly

For week ending we can make use of the default behaviour for subtracting days from a date

    1 Select  a_field

    2 from    a_table

    3 where   the_date    between (to_date('&Date', 'DD/MM/YYYY')-6)

    4                     and     (case when to_date('&Date', 'DD/MM/YYYY') > trunc(sysdate)-1

    5                             then trunc(sysdate)-1

    6                             else to_date('&Date', 'DD/MM/YYYY')

    7                             end)

Note that in this example I have used the date function ‘betweenand’, however some people prefer to use a pair of ‘where’ clauses, as in the example below.

    1 where   the_date    >=  (to_date('&Date', 'DD/MM/YYYY')-6)

    2 and     the_date    <=  (case when to_date('&Date', 'DD/MM/YYYY') > trunc(sysdate)-1

    3                         then trunc(sysdate)-1

    4                         else to_date('&Date', 'DD/MM/YYYY')

    5                         end)

As far as I am aware, there is no performance difference between the two, more a matter of personal preference. Some people may feel that the where statements give them more control over exactly which dates are included because they can make use of ‘<, <=, =, >=, >, etc’ and may be uncertain of whether ‘betweenand’ is inclusive of the dates or not. I am confident enough to say however that it is inclusive therefore the same amount of control can be attained by varying the date used, e.g. shift it one day higher to emulate ‘>’, etc.

This sql relies on the user entering the week ending date for the week they are interested in, however if you wanted to construct it so that all they had to do was enter a date within the week of interest, you would need to evaluate the date to identify the begining and end of the week. One way this could be done is to use the next_day function to identify for example the next Saturday or Sunday, depending when you define the week as ending. By subtracting 7 from the date then and finding the next Sunday or Monday (depending when you define the week as starting, you would have your week begining date too.

Monthly

For month ending:

    1 Select  a_field

    2 from    a_table

    3 where   the_date    >=  to_date('&Date','MM YYYY')

    4 and     the_date    <=  (case when last_day(to_date('&Date','MM YYYY')) > trunc(sysdate)-1

    5                         then trunc(sysdate)-1

    6                         else last_day(to_date('&Date','MM YYYY'))

    7                         end)

Notice I have used a different date format here - 'MM YYYY', which allows me to enter the date as just a ‘month year’ combination, such as ‘January 2006’. The to_date function automatically takes this as the first of the month allowing me to use the last_day function to acquire the end of the month. Again the case statement allows data for a partial month to be retrieved.

If you wanted to create a specific month to date query for a particular day in the month such as month to the 15/01/2006 all you need do is change the date format, such as to 'DD/MM/YYYY' in this example.

Yearly

For the year to date:

    1 Select  a_field

    2 from    a_table

    3 where   the_date    >=  TRUNC(to_date('&date', 'MM YYYY'), 'YYYY')

    4 and     the_date    <=  (case when last_day(to_date('&Date','MM YYYY')) > trunc(sysdate)-1

    5                         then trunc(sysdate)-1

    6                         else last_day(to_date('&Date','MM YYYY'))

    7                         end)

The paramater '&date' is entered as a month year combination (e.g. January 2006), but to_date converts it to ‘DD/MM/YYYY’. By truncating the date on the year, the date is converted to ‘01-01-YYYY.’

Once again if you required a year to a specific date then simply apply the appropriate format ('DD/MM/YYYY') to the expression. Alternatively, you may just want to enter the year and rely on the case statement to find the appropriate point for year to date, e.g.:

    1 where  the_date    >=   TRUNC(to_date('&date', 'YYYY'), 'YYYY')

    2 and    the_date    <=   (case when TRUNC(to_date('&date'+1, 'YYYY'), 'YYYY')-1 > trunc(sysdate)-1

    3                         then trunc(sysdate)-1

    4                         else TRUNC(to_date('&date'+1, 'YYYY'), 'YYYY')-1

    5                         end)

Last 12 Months

For the last 12 months to date you can utilise any of the date formating options shown above to define the date at which the range runs from and to. In the example I provide below I am interested in the range from the begining of the month to the end of the month 12 months later:

    1 Select a_field

    2 from   a_table

    3 where  the_date    >=   add_months(to_date('&date', 'MM YYYY'),-11)

    4 and    the_date    <=   (case when last_day(to_date('&date', 'MM YYYY')) > trunc(sysdate)-1

    5                         then trunc(sysdate)-1

    6                         else last_day(to_date('&date', 'MM YYYY'))

    7                         end)

On occasion I have also had the need for a quarter to date range, but at the moment I can't find an example of this, so I will endeavour to provide this at some point, unless anyone else would like to provide it. Since I am often also required to query against both Oracle systems and SQL server, I aim to at some point provide each of these ranges in T-SQL too. But that's for another blog.

James

Published Saturday, February 10, 2007 4:00 AM by James.Pipe

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jim 2.0 said:

As promised in my original post on PL/SQL date ranges previously, below is a sql statement which can

March 1, 2007 1:46 AM
 

Jim 2.0 said:

Recently my colleague challenged me to find a method for identifying the week ending date for the week

April 24, 2007 3:46 PM
 

Dennis said:

How about quarterly report... how should we code it?

October 1, 2007 7:32 PM
 

James.Pipe said:

This should work for you:

select add_months(trunc(to_date('&date', 'YYYYMMDD'), 'YYYY'),3*(to_char(to_date('&date', 'YYYYMMDD'), 'q')-1))"BEGIN_QUARTER"

, add_months(trunc(to_date('&date', 'YYYYMMDD'), 'YYYY'),3*(to_char(to_date('&date', 'YYYYMMDD'), 'q')))"END_QUARTER"

from dual

What this does is to take the date you enter ('&date') and identify which quarter it is in ("to_char(to_date('&date', 'YYYYMMDD'), 'q')").

This is then used to determine how many months to add onto the beginning of the year. Since a quarter is always 3 months long, this is a multiple of three.

The beginning of the year is found by truncating your date by year ("trunc(to_date('&date', 'YYYYMMDD'), 'YYYY')")

To find the start of the quarter we have to add a multiple of 3 months as stated above based on the quarter in question, so we must subtract 1 from the quarter E.g. if we are in Q1 then we add 0 * 3 months, since the start of the year is the same as the start of the quarter. If we are in Q2 we add 1 * 3 months to get to the start of April (the fourth month), etc.

To find the end of the quarter, I've actually identified the start of the next quarter by adding an extra 3 months (by multiplying the quarter by 3). I prefer to do this as it is easier to calculate and I can simply use '<' in my where clause instead of '<='.

If you want the actual last day of the quarter then you could either subtract one day from this, or add one less month on then use the 'last_day' function to find the last of that month, or find the last day of the start month, then add 2 months on to this. There are several options.

There may be a more compact way to do the above, but I use calculations as that's what I know how to do best and it seems to work. Depending on your performance requirements, hopefully it will work for you too.

James

October 3, 2007 5:20 PM
 

Julie Guerre said:

Hello,

I was wondering if you could help me with a SQL query?

I need to retrieve some bonus payment dates between the last 1st of March to today's date. The month of march cannot have a fixed year associated to it as this will be used in a report for a client and has to be updated automatically every year.

Today the report should return payments made since 01 March 07 to today's date but when we'll be in June 08 it should return data between 01 March  08 to today's date...

Would you have an idea on how to do this?

Your help would be very appreciated,,

Many Thanks,

Julie

February 12, 2008 4:33 PM
 

James.Pipe said:

Hi Julie,

just off the top of my head, you could try something like the following:

select case

when sysdate < add_months(trunc(sysdate, 'YYYY'),2)

then add_months(trunc(sysdate, 'YYYY'),-10)

else add_months(trunc(sysdate, 'YYYY'),2)

end BonusStartDate

from dual

;

trunc(sysdate,yyyy) sets the date to January 1st for the current year, and adding 2 months to this gives you March 1st of the current year

Evaluate sysdate compared to this March 1st

If sysdate is < March 1st of the current year then we subtract 10 months to give us March 1st of the previous year

Otherwise we use March 1st of the current year as the start date.

Hope this is what you wanted.

James

February 12, 2008 5:35 PM
 

Anuj Shrivastava said:

(case when last_day(to_date('&Date','MM YYYY')) > trunc(sysdate)-1

   5                         then trunc(sysdate)-1

   6                         else last_day(to_date('&Date','MM YYYY'))

   7                         end)

June 24, 2008 3:18 PM
 

Richa Pandharikar said:

Hi,

I need to fetch the data for last 12 months, each month can have multiple transactions. So the query should return me the monthly total for last 12 months. Can you help me with the query please?

Thanks,

RIcha

July 14, 2008 12:10 AM
 

Shilpa said:

Hi,

I need to populate a calendar table using pl/sql. The table stores dates on certain parameters such as  day, month, week, quarter, fiscal year.

Can you please help me with it.

August 24, 2008 4:13 PM
 

Shilpa said:

Hi,

I need to insert records in a calendar table with fields such as Calendar_date,day_of_month,week_day_name, week_day_short_name, week_key,week_of_month_number, week_of_year_number, month_key, month_name, month_short_name, month_of_qtr_number, month_of_year_number, quarter_key, qtr_of_year_number, month_of_year_number, quarter_key,qtr_of_year_number, year_key

Please help me with the query/stored proc.

August 24, 2008 4:22 PM
 

X31 said:

1. select a_field

  from a_table

  where a_d_key='&a_d_key';

One error will generat. a_d_key doesn't match with '&a_d_key'...

2. How to apply it in Fiscal year?

  For example, how to calculate late fiscal year revenue from the day of the last year...

Much thanks

X31

August 29, 2008 3:06 PM
 

Ryan said:

Thanks! I find this very helpful

March 20, 2009 2:57 PM
 

ANI said:

HOW TO USE MENU DERIVEN PROGRAM IN PL/SQL

MEANS WE ARE DET ONE BY ONE VALES FROM USE AND PROGRA CAN MAKE EXECUTION AFTER GET VALUES WHICH IS GIVEN BY THE USER

April 7, 2010 6:06 PM
 

air jordan 4 said:

It looks good,I have learn a recruit!

Recently,I found an excellent online store, the XX are completely various, good quality and cheap price,it’s worth buying! http://www.alliask.net/

June 5, 2010 7:31 AM
 

Hazel said:

Hi There.. saw your post and it seems you will be able to help me.. I have a big problem that i badly need help. I need to get the 24 months sales depending on the input date Example: March 2010 so i have to get the 24 months from the input date backwards.

Thanks hope I can get a feedback from you.

March 22, 2011 2:46 AM

Leave a Comment

(required) 
(optional)
(required) 
Submit
Powered by Community Server (Personal Edition), by Telligent Systems