b¬äd†ødd

Weekly Valuation Point

May 8, 2013 by Brad

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