Archives of the TeradataForum

Message Posted: Thu, 27 Jan 2011 @ 20:12:18 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Sami -

You can generate the SQL you need without retyping, but that may or may not help given the platform you're running the report from.

If you're using bteq, you can do something like this:

     .export file=runme.btq

     select x.txt (title '') from (
     select 1 step, 1 rn, cast('SELECT      productID ,
     ProductName,' as varchar(100)) txt from sys_calendar.calendar where day_of_calendar = 1
     union select 2 step, rn, 'max(case when date=' || cast(calendar_date as
     integer)|| ' then Products_Sold else 0  end )    as '
     || cast(cast(calendar_date as format 'yyyy-mm-dd') as char(10)) || case
     when rn <> 1 then ',' else '' end txt
     from
     (-- dates to report
     select calendar_date,
            day_of_calendar as substep,
        	   row_number() over (order by calendar_date desc) rn from sys_calendar.calendar
     where day_of_week = 6 and calendar_date < current_date qualify row_number() over (order
     by calendar_date desc) <= 3) d union select 3 step, 1 rn, 'from TABLE group by 1,2 order
     by productID;' from sys_calendar.calendar where day_of_calendar = 1
     ) x
     order by step, rn desc;

     .export reset
     .run file=runme.btq

This assumes that data in your table is always dated on Fridays even if it's a holiday, and will work as long as you always want to report the three most recent weeks. The period is easy to extend by following the pattern above.

Changes for other kinds of calendar variability might be a little trickier, but do-able as long as you have the indicators you need in some calendar table.

If you're running this manually every week, you'll have to run the above and copy/paste the output to run. If you're using Business Objects or some other reporting layer, the crosstabbing and week filtering can probably be baked into the report in that layer.

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>