How do I create this table using proc sql passthru using teradata calendar table

[ilearnsas]

This is more of a teradata question since I am using proc sql passthru to generate the table below. 

I would like 12-29-2008 to be the start date for my table i.e. I want to assign this as the start date and with the week number corresponding to this as 209 (in a different column).

Then the next row follows with the following Monday (+7 days)  and the week number corresponding to this is being incremented by 1 (i.e.209+1) . The end date for this table is 12/27/2021 (Monday)

TABLE STRUCTURE is below:

Weekstart/Monday of the week    Week num

12/29/2008                                  209

1/5/2009                                       210

....

....

....

12/27/2021                                   887

I tried something like below but it only generated one row.

SEL WeekBegin , (WeekBegin - DATE '2008-12-29')/7 + 209 as week_num

FROM Sys_Calendar.BusinessCalendar

WHERE calendar_date = DATE '2008-12-29';

Could you please let me know how we can do an autocalculate using the syscalendar in teradata and obtain the subsequent rows i.e. the entire table all the way through 12/27/21 ? 

Thanks for your help on this! 

[Patrick]

Change your WHERE clause logic so that you select more than one row from the source table.

WHERE 
  calendar_date >= DATE '2008-12-29' 
  and calendar_date <= DATE '2021-12-27'

[ilearnsas]

Thanks. I tried below.

    SEL WeekBegin, (WeekBegin - DATE '2008-12-29')/7 + 209 as week_num
FROM Sys_Calendar.BusinessCalendar 
    WHERE 
  calendar_date >= DATE '2008-12-29' 
  and calendar_date <= DATE '2021-12-27';
It gives 3 rows and 3 weeknum for the same date.

The output looked like below:

Weekbegin      Weeknum
12/28/2008      209
12/28/2008      209
12/28/2008       209
...
12/26/2021       886
12/26/2021       886


The errors are below-
1. See duplicate entires (3 rows or 2 rows instead of 1)
2. Need to display monday of the week (however the last row displayed was 12/26/21 instead of 12/27/21 -- and the 
corresponding week num should be 887 and not 886. Any suggestion on how we can fix this to get Monday of every week
until 12/27/21?



How do we get one unique row for each date and weeknum (no dups) and also get only Monday of every week?

Thanks!

[Patrick]

I don't know why you're getting multiple rows for the query you've posted.

Updated post:

You're just selecting rows from a single "table" so there can't be any duplication of rows but it must already be this way in the source table. Your source table has a row per date so that's 7 rows per week - and that's what you get with your query. 

If you just need to de-dup the result then you can use DISTINCT in your Select statement or follow what @Tom suggests.

[Tom · Accepted solution]

If you select by WEEKBEGIN you are going to get 7 observations since there are 7 days in a week.

If you want to select by MONDAY then use 

day_of_week=2

Since that variable is code 1 to 7 with 1 meaning Sunday.