Creating a Dynamic "Calendar" report

Database
CH
Enthusiast

Creating a Dynamic "Calendar" report

Hello! I am trying to create a tabular report that changes the view based on the day the report is run. This report looks forward from the current date and displays a count of rows that are tied to that date in the future (a travel date).The Sql to find the data is this

Select travelstartdate, count(records) from traveltable where travelstartdate > Current_date.

If today is November 13, it should look like this. There is no data in the November column until you get to the 14th.

The columns across the top should start with current month (Not January).

THe days of the month of course go as you'd expect for the row identifier..

The date field is just a date, not a timestamp.

 

         NOV     DEC     JAN     FEB     MAR     APR     MAY
 
 1             35768   33165   12715    8104    3987    3692
 2             34309   41954   11575    8626    3241    2841
 3             28626   38912    8593    6295    2946    2957
 4             21866   37124    9478    5355    3440    3877
 5             22685   41728    8511    5321    3878    3166
 6             23699   37959    7367    4916    5015    2550
 7             26725   31284    7918    6304    3701    2490
 8             29048   27588    8391    5940    3029    2012
 9             28437   22991   10311    8463    3024    1971
10             26065   20687    8924    6999    4209    2150
11             21922   20207    7029    4630    3733    2598
12             24971   21940    6649    4478    4062    2284
13             26266   18694    7660    4767    5753    2196
14     60094   30645   15084    8605    5357    4680    2199
15     67077   37037   14685    9860    6561    4920    2364
16     70718   33140   13125   11997    7905    3238    2116
17     60249   30598   13316    8786    6586    3439    1953
18     51411   32240   12919    8259    4607    4484    2288
19     58666   35321   14182    6440    4139    3803    2466
20     53737   38282   12018    6643    4384    5863    2164
21     46888   42938   12202    7268    4667    5279    2188
22     43087   48274    8949    8168    5692    4204    1543
23     44112   37291    9364   10665    6985    4050    2058
24     44748   27557    9090    8212    5284    3481    1988
25     50020   35038   10122    6806    3641    3412    2714
26     41406   41817   11585    5776    3394    4640    2177
27     33605   39639   10408    6074    3440    5007    1814
28     32197   36857   10229    8119    4149    3186    1754
29     32434   39557    8205            4882    2999    1509
30     36477   35297    8776            6861    3629    1581
31             26680   11011            5402            1628

3 REPLIES
Teradata Employee

Re: Creating a Dynamic "Calendar" report

Hi.

 

At least show us what you got so far...

 

There is a growing trend of people asking others to do their own work/homework instead of just looking for help.

 

Regards

 

Carlos.

CH
Enthusiast

Re: Creating a Dynamic "Calendar" report

Hi Carlos,

It is not homework :). I have no problems accessing the data that I need to fill out the table. I can retrieve it such that each day shows up in a single column labeled count, with each day being a different row.

HOwever my customer wants to see the data in the format as I've asked, and I am asking for help if that is even possible. I looked for examples but have not seen it anywhere to figure out if it is even a possibility

 

SyntaxEditor Code Snippet

select 
cast (Cast(A.CreateDate as Date Format 'YYYY-MM-DD') as CHAR(11)) as Viable_Date,cast(sum(C.QuantSold+D.QuantSoldCount) AS DECIMAL(18,0)) as DocCount
from tables where a.createdate > current_date - 365
group by 1
order by 1

 

Highlighted
Teradata Employee

Re: Creating a Dynamic "Calendar" report

Hi CH,

 

Well, you need some PIVOTing techniques.

If you are in 16.20 you can use PIVOT In-list, which is handy for you.

If not, you have to make a query to build your query.

If you are in 16.10 you can use PIVOT, if not the regular max(case when...) as provided by documentation :

https://docs.teradata.com/reader/756LNiPSFdY~4JcCCcR5Cw/GnnbigJTnAWrDfLbecAN7Q

 

In the end, you want a query like this :

 

with cte_agg as
(
  select to_char(A.CreateDate, 'dd')  as DayOfMonth
       , to_char(A.CreateDate, 'MON') as MonthName
       , cast(sum(C.QuantSold + D.QuantSoldCount) AS DECIMAL(18,0)) as DocCount
    from <your_tables>
where A.CreateDate > current_date group by to_char(A.CreateDate, 'dd') , to_char(A.CreateDate, 'MON') ) select DayOfMonth , max(case MonthName when 'NOV' then DocCount end) as NOV , max(case MonthName when 'DEC' then DocCount end) as DEC , max(case MonthName when 'JAN' then DocCount end) as JAN , max(case MonthName when 'FEB' then DocCount end) as FEB , max(case MonthName when 'MAR' then DocCount end) as MAR , max(case MonthName when 'APR' then DocCount end) as APR , max(case MonthName when 'MAY' then DocCount end) as MAY from cte_agg group by DayOfMonth order by DayOfMonth asc;

But as you can see, columns are static.