select
id, category, raw_data
from sample
where
category BETWEEN 'A' AND 'A' /* note! [value1] AND [value1] */
OR category BETWEEN 'L' AND 'L' /* note! [value1] AND [value1] */
OR category BETWEEN 'Z' AND 'Z' /* note! [value1] AND [value1] */
So, proof that ‘A’ is BETWEEN ‘A’ AND ‘A’ (ditto L and Z). What we really see here is the effect of EQUALS in the SQL definition of BETWEEN. ‘A’ does equal ‘A’ (and does equal ‘A’). So; keep in mind that BETWEEN includes both boundaries!
select
id, sold_date, raw_data
from sample
where
sold_date BETWEEN to_date('2013/01/01','YYYY/MM/DD')
AND to_date('2013/01/01','YYYY/MM/DD')
OR sold_date BETWEEN to_date('2013/02/01','YYYY/MM/DD')
AND to_date('2013/02/01','YYYY/MM/DD')
OR sold_date BETWEEN to_date('2013/03/01','YYYY/MM/DD')
AND to_date('2013/03/01','YYYY/MM/DD')
Yes! BETWEEN is consistent, if there are exact matches to the provided boundaries of dates/times/numbers they will be included.
select
id, category, raw_data, btwn_A, btwn_Z, btwn_A + btwn_Z as total
, case when (btwn_A + btwn_Z) <> raw_data then ' WRONG :(' else ' :)' end as rslt
from (
select
id
, category
, raw_data
, case when category between 'A' and 'L' then raw_data else 0 end as btwn_A
, case when category between 'L' and 'Z' then raw_data else 0 end as btwn_Z
from sample
) inq
order by category
What we see in this result is the ‘L’ is matched by two BETWEEN expressions and now there is calculation error because we would be double-counting any matches to ‘L’.
select
id, category, raw_data, btwn_A, btwn_Z, btwn_A + btwn_Z as total
, case when (btwn_A + btwn_Z) <> raw_data then ' WRONG :(' else ' :)' end as rslt
from (
select
id
, category
, raw_data
, case when category between 'A' and 'L' then raw_data else 0 end as btwn_A
, case when category between 'M' and 'Z' then raw_data else 0 end as btwn_Z /* correction to 'M' in this line */
from sample
) inq
order by category
So, mutually exclusive ‘buckets’ are a good thing. Note (not shown) if we happened to use ‘N’ instead of ‘M’ we would create a gap, so not only should the buckets be mutually exclusive, it is equally important that they are don’t allow gaps to occur.
select
inq.id, inq.sold_date, mth_1, mth_2, mth_3, (mth_1 + mth_2 + mth_3) as total
, case when (mth_1 + mth_2 + mth_3) <> raw_data then ' WRONG :(' else ' :)' end as rslt
from (
select
id
, sold_date
, category
, raw_data
, case when sold_date between to_date('2013/01/01','YYYY/MM/DD')
and to_date('2013/02/01','YYYY/MM/DD') then raw_data else 0 end as mth_1
, case when sold_date between to_date('2013/02/01','YYYY/MM/DD')
and to_date('2013/03/01','YYYY/MM/DD') then raw_data else 0 end as mth_2
, case when sold_date between to_date('2013/03/01','YYYY/MM/DD')
and to_date('2013/04/01','YYYY/MM/DD') then raw_data else 0 end as mth_3
from sample
) inq
order by inq.sold_date
We saw before that by making the ‘buckets’ mutually exclusive we solve this overlapping problem. But how is this done with dates? There are 2 methods for this:
select
inq.id, inq.sold_date, mth_1, mth_2, mth_3, (mth_1 + mth_2 + mth_3) as total
, case when (mth_1 + mth_2 + mth_3) <> raw_data then ' WRONG :(' else ' :) .' end as rslt
from (
select
id
, sold_date
, category
, raw_data
, case when sold_date between to_date('2013/01/01','YYYY/MM/DD')
and to_date('2013/02/01','YYYY/MM/DD')-1 then raw_data else 0 end as mth_1
, case when sold_date between to_date('2013/02/01','YYYY/MM/DD')
and to_date('2013/03/01','YYYY/MM/DD')-1 then raw_data else 0 end as mth_2
, case when sold_date between to_date('2013/03/01','YYYY/MM/DD')
and to_date('2013/04/01','YYYY/MM/DD')-1 then raw_data else 0 end as mth_3
from sample
) inq
order by inq.sold_date
Excellent! No overlapping and no calculation errors.
select
inq.id, inq.sold_date, mth_1, mth_2, mth_3, (mth_1 + mth_2 + mth_3) as total
, case when (mth_1 + mth_2 + mth_3) <> raw_data then ' WRONG :(' else ' :) ..' end as rslt
from (
select
id
, sold_date
, category
, raw_data
, case when sold_date >= to_date('2013/01/01','YYYY/MM/DD')
and sold_date < to_date('2013/02/01','YYYY/MM/DD') then raw_data else 0 end as mth_1
, case when sold_date >= to_date('2013/02/01','YYYY/MM/DD')
and sold_date < to_date('2013/03/01','YYYY/MM/DD') then raw_data else 0 end as mth_2
, case when sold_date >= to_date('2013/03/01','YYYY/MM/DD')
and sold_date < to_date('2013/04/01','YYYY/MM/DD') then raw_data else 0 end as mth_3
from sample
) inq
order by inq.sold_date
Excellent again! No overlapping and no calculation error (again).
select
inq.id, inq.sold_date, mth_1, mth_2, mth_3, (mth_1 + mth_2 + mth_3) as total
, case when (mth_1 + mth_2 + mth_3) <> raw_data then ' WRONG :(' else ' :) .' end as rslt
from (
select
id
, sold_date
, category
, raw_data
, case when sold_date between to_date('2013/01/01','YYYY/MM/DD')
and to_date('2013/02/01','YYYY/MM/DD')-1 then raw_data else 0 end as mth_1
, case when sold_date between to_date('2013/02/01','YYYY/MM/DD')
and to_date('2013/03/01','YYYY/MM/DD')-1 then raw_data else 0 end as mth_2
, case when sold_date between to_date('2013/03/01','YYYY/MM/DD')
and to_date('2013/04/01','YYYY/MM/DD')-1 then raw_data else 0 end as mth_3
from sample
) inq
order by inq.sold_date
Oh No! A new type of error; Now there is a record that would be missed in calculations (a GAP in the ‘buckets’).
select
inq.id, inq.sold_date, mth_1, mth_2, mth_3, (mth_1 + mth_2 + mth_3) as total
, case when (mth_1 + mth_2 + mth_3) <> raw_data then ' WRONG :(' else ' :) ..' end as rslt
from (
select
id
, sold_date
, category
, raw_data
, case when sold_date >= to_date('2013/01/01','YYYY/MM/DD')
and sold_date < to_date('2013/02/01','YYYY/MM/DD') then raw_data else 0 end as mth_1
, case when sold_date >= to_date('2013/02/01','YYYY/MM/DD')
and sold_date < to_date('2013/03/01','YYYY/MM/DD') then raw_data else 0 end as mth_2
, case when sold_date >= to_date('2013/03/01','YYYY/MM/DD')
and sold_date < to_date('2013/04/01','YYYY/MM/DD') then raw_data else 0 end as mth_3
from sample
) inq
order by inq.sold_date
Look Ma, no hands! No query modification needed and calculations are correct (as no gap exists).
select
inq.id, inq.sold_date, mth_1, mth_2, mth_3, (mth_1 + mth_2 + mth_3) as total
, case when (mth_1 + mth_2 + mth_3) <> raw_data then ' WRONG :(' else ' :) _' end as rslt
from (
select
id
, sold_date
, category
, raw_data
, case when sold_date between to_date('2013/01/01','YYYY/MM/DD')
and to_date('2013/02/01','YYYY/MM/DD')-1/(24*60*60) then raw_data else 0 end as mth_1
, case when sold_date between to_date('2013/02/01','YYYY/MM/DD')
and to_date('2013/03/01','YYYY/MM/DD')-1/(24*60*60) then raw_data else 0 end as mth_2
, case when sold_date between to_date('2013/03/01','YYYY/MM/DD')
and to_date('2013/04/01','YYYY/MM/DD')-1/(24*60*60) then raw_data else 0 end as mth_3
from sample
) inq
order by inq.sold_date
Voila! The right results by deducting the right interval!
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (12)
Author
Commented:Regards, Paul
Commented:
>If the data involves time, to the precision of 1 second, then we can deduct 1 second.
I've chased down many a prod support ticket where developers failed to realize that 1-1-2013 translates to 1-1-2013 00:00:00, and wouldn't include 1-1-2013 12:34:56.
Author
Commented:Commented:
Cheers
Commented:
You can make a field up as DateTime and format it as "dd-mmm-yyyy"
It all looks good. But it is stored as a Double with a Long integer value based on the number of days elapsed since 30-Dec-1899 and the decimal as a fraction of the day as the time.
Now, add some values to it using Date() and others using Now() and watch the fun ensue as you use BETWEEN...AND and get some records in at the 'boundary' and others out.
Those entered via Date() will be dd-mmm-yyyy 12:00:00 AM and will be in.
Those enter via Now() will be dd-mmm-yyyy and some time component and be out.
That trips up a lot of folks!
View More