Dedicated to design and performance of databases and audio systems.
Yesterday I was asked if I had any SQL examples that would determine an end-of-week date based on the current date. The purpose was to embed this logic in a view where the business users would be assured of the same static results for a week. The week would run Monday to Sunday based on the data valuation--meaning Monday morning would contain data as of Sunday night. The assumption that a daily update will occur seven days per week producing data as of the night before, is a given.
We have always had our method for locking in the prior month-end valuation point,
current_date - extract(day from current_date)
but a week-end valuation point is relatively new. It should also be pointed out that we want to perform this process on the fly and not use a date control table.
With Teradata 13+ many of the System Calendar fields are now functions that we can call without joining to the System Calendar. One of those is DAY_OF_WEEK
. It assigns a numeric value to each day (i.e. Sunday = 1, Monday = 2, ..., Saturday = 7) and will be used here.
If the week ended on Saturday, the logic would be very simple. However, we need to offset the DAY_OF_WEEK
value in order to make the week end on Sunday instead. Our test case follows
-- table definition
create multiset volatile table foo, no log (
id integer not null,
eff date not null,
xpr date not null,
cde character(1))
primary index (id)
on commit preserve rows
;
-- data
insert into foo (1, date '2013-05-01',date '2013-05-02','A');
insert into foo (1, date '2013-05-02',date '2013-05-03','B');
insert into foo (1, date '2013-05-03',date '2013-05-04','C');
insert into foo (1, date '2013-05-04',date '2013-05-05','D');
insert into foo (1, date '2013-05-05',date '2013-05-06','E');
insert into foo (1, date '2013-05-06',date '2013-05-07','F');
insert into foo (1, date '2013-05-07',date '9999-12-31','G');
-- query
select
id,
eff,
xpr,
cde
from foo
where
eff <=
current_date -
coalesce(
nullifzero(
syslib.day_of_week(current_date) - 1
),
7) -- -1 Sunday offset
order by 1,2
;
Here we had to offset the end of week by one day since Saturday is day 7, but now we want Sunday to be day 7. Therefore, the logic in the WHERE
clause accomplishes that task. Every day shifts by one day (i.e. the difference of Saturday to Sunday; Monday is now weekday 1) and Sunday's zero is replaced with the 7.
Running this process on May 8, 2013 (i.e. current_date) produced the records with code values A through E because May 5th was Sunday--the end of the week. However, while the results are good, we still want to validate that the EXPLAIN
plan manufactures a DATE
value from our logic to be used as the criterion for the query.
1) First, we do an all-AMPs RETRIEVE step from foo by way of an all-rows scan with a condition of ("foo.eff <= DATE '2013-05-05'") into Spool 1 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1 (foo.id, foo.eff). The size of Spool 1 is estimated with no confidence to be 3 rows (126 bytes). The estimated time for this step is 0.01 seconds.
It does. The Sunday value of 2013-05-05 is produced by the optimizer which is far more efficient than re-executing the logic redundantly.
I hope this helps with some of your DATE
logic/math challenges in the future.
Cheers
-Brad