Pages

Thursday, October 18, 2012

RANGE BETWEEN and leap years

Answering a question on the OTN forum was a bit tricky to get an analytic sum using a RANGE BETWEEN that would handle leap years, but in the end I came up with a workaround that satisfies the requirement. Along the way I realized why there are two different INTERVAL datatypes :-)

Then is added another requirement "we also want the sales for the same 4 days of the previous year".
Well we might try this one then:

select to_char(day, 'YYYY-MM-DD') day
, qty
, sum(qty) over (
order by day
range between interval '3' day preceding
and current row
) four_days
, sum(qty) over (
order by day
range between interval '1' year + interval '3' day preceding
and interval '1' year preceding
) four_lastyear
from sales
order by day
/

Now why won't that work? Hmmm... OK, we can try with days instead of years:

select to_char(day, 'YYYY-MM-DD') day
, qty
, sum(qty) over (
order by day
range between interval '3' day preceding
and current row
) four_days
, sum(qty) over (
order by day
range between interval '368' day(3) preceding
and interval '365' day(3) preceding
) four_lastyear
from sales
order by day
/

Works for 2011 but not for 2012, because 2012 is a leap year. OK, we use a different set of days:

select to_char(day, 'YYYY-MM-DD') day
, qty
, sum(qty) over (
order by day
range between interval '3' day preceding
and current row
) four_days
, sum(qty) over (
order by day
range between interval '369' day(3) preceding
and interval '366' day(3) preceding
) four_lastyear
from sales
order by day
/

We saw that the correct range was 369-366 for leap years and 368-365 for non-leap years. So let's get data for the ranges 369, 368-366 and 365:

select to_char(day, 'YYYY-MM-DD') day
, qty
, sum(qty) over (
order by day
range between interval '3' day preceding
and current row
) four_days
, sum(qty) over (
order by day
range between interval '369' day(3) preceding
and interval '369' day(3) preceding
) d1
, sum(qty) over (
order by day
range between interval '368' day(3) preceding
and interval '366' day(3) preceding
) d2
, sum(qty) over (
order by day
range between interval '365' day(3) preceding
and interval '365' day(3) preceding
) d3
from sales
order by day
/

No problem. That calculation is first substracting an INTERVAL YEAR TO MONTH literal from a DATE column - the result will be a DATE. Then it subtracts an INTERVAL DAY TO SECOND literal from the resulting DATE which in turn returns yet another DATE. Perfect.

Now look at this one - strictly "mathematically" this should be equivalent: