TheLazy.Dev
Portfolio GitHub

Blog

sql |teradata

Some Teradata SQL Fun with Dates Differing by Months

geraldew

geraldew

Posted on June 3, 2022

Some Teradata SQL Fun with Dates Differing by Months

I recently made a comment and then a contribution to a Dev posting about calculating dates differing by month. See that at:

As my paid work happens to be on a Teradata datawarehouse, I added a Teradata equivalent method and result.

At the time, I had the feeling that this kind of calculation needs to be tested/checked over spans of up to four years - essentially to see if there are anomalies among various methods. By chance, today I had a spare moment that I then used to write out and run something more comprehensive.

A Teradata Feature

I should point out that I'll be using a Teradata feature - its "System Calendar". This is a table/view that you can call on, that provides a set of calendar dates with several pre-derived values.

Here is an example of simply fetching it. I've usually presumed that it gets generated on demand - but for all I know it's really stored as an actual table. The truth is that I don't care about the distinction, I just use it and can rely on it being available.

SELECT
    calendar_date ,
    year_of_calendar ,
    month_of_year
FROM
    sys_calendar.calendar
;
Enter fullscreen mode Exit fullscreen mode

Note: it has other fields beyond these, I'm just quoting the ones I'll use here.

The main thing is that I can rely on it being a correctly valid set of dates. In practice, it is nearly always constrained with a WHERE clause to a range/type/set of desired dates.

First Query - One Month Difference - Across All Dates

Here what I'll do is generalise from the specific examples in the other article, and then count how often the different variations occur.

In effect, we'll be generating triplets of dates:

  • central date
  • date at one month before
  • date at one month after

In particular, our focus will be one what "day of the month" each of those derivations produces. We're really not interested in all the situations where all three dates are the same day number in the month.

  • i.e. that a month before 6th June is the 6th May and one month after is the 6th July

So, we filter those out.

Then, we count up the combinations of interest. We'll aggregate across all the years covered by the system calendar, and look at the combinations of:

  • which month, for the central date
  • which day of the month for the central date
  • which day of the month for one month before
  • which day of the month for one month after

The SQL looks like:

SELECT
    month_of_year ,
    day_of_month ,
    MonthBefore_Day ,
    MonthAfter_Day ,
    COUNT(*) AS Rw_Cnt ,
    COUNT( calendar_date ) AS Day_Cnt ,
    COUNT( DISTINCT calendar_date ) AS Day_Ctd ,
    MIN( year_of_calendar ) AS Min_Year ,
    MAX( year_of_calendar ) AS Max_Year ,
    NULLIF( MAX( year_of_calendar ), Min_Year ) AS Mxd_Year 
FROM
    ( -- InterestingDays
        SELECT
            calendar_date ,                
            year_of_calendar ,
            month_of_year ,                
            day_of_month ,                 
            ADD_MONTHS( calendar_date, 1 ) AS MonthBefore_Dt , 
            EXTRACT( DAY FROM ADD_MONTHS( calendar_date, -1 ) ) AS MonthBefore_Day ,
            ADD_MONTHS( calendar_date, 1 ) AS MonthAfter_Dt , 
            EXTRACT( DAY FROM ADD_MONTHS( calendar_date, 1 ) ) AS MonthAfter_Day 
        FROM
            sys_calendar.calendar 
        WHERE
            NOT ( ( day_of_month = MonthBefore_Day ) AND ( day_of_month = MonthAfter_Day ) AND ( MonthBefore_Day = MonthAfter_Day ) )
        ) AS InterestingDays
GROUP BY
    month_of_year ,                
    day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day 
ORDER BY
    month_of_year ,                
    day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day 
;
Enter fullscreen mode Exit fullscreen mode

and here is what that produces:

month_of_year day_of_month MonthBefore_Day MonthAfter_Day Rw_Cnt Day_Cnt Day_Ctd Min_Year Max_Year Mxd_Year
1 29 29 28 152 152 152 1,900 2,100 2,100
1 30 30 28 152 152 152 1,900 2,100 2,100
1 30 30 29 49 49 49 1,904 2,096 2,096
1 31 31 28 152 152 152 1,900 2,100 2,100
1 31 31 29 49 49 49 1,904 2,096 2,096
3 29 28 29 152 152 152 1,900 2,100 2,100
3 30 28 30 152 152 152 1,900 2,100 2,100
3 30 29 30 49 49 49 1,904 2,096 2,096
3 31 28 30 152 152 152 1,900 2,100 2,100
3 31 29 30 49 49 49 1,904 2,096 2,096
5 31 30 30 201 201 201 1,900 2,100 2,100
7 31 30 31 201 201 201 1,900 2,100 2,100
8 31 31 30 201 201 201 1,900 2,100 2,100
10 31 30 30 201 201 201 1,900 2,100 2,100
12 31 30 31 201 201 201 1,900 2,100 2,100

All well and good.

Extend analysis to multiple month spans of interest

For the next part, I'll make another use of the Teradata system calendar. This time I'm merely going to use it as row generator.

The general method is as follows, in this case to generate five rows. Note that I use yet another column this time: "day_of_calendar", which begins at one.

SELECT
    day_of_calendar AS N
FROM
    sys_calendar.calendar 
WHERE
    day_of_calendar BETWEEN 1 AND 5
;
Enter fullscreen mode Exit fullscreen mode

While that only generates a list of numbers, it's quite straightforward to use a CASE expression to set custom values instead.

Such as:

SELECT
    CASE 
        WHEN day_of_calendar = 1 THEN 1
        WHEN day_of_calendar = 2 THEN 6
        WHEN day_of_calendar = 3 THEN 12
        WHEN day_of_calendar = 4 THEN 24
        WHEN day_of_calendar = 5 THEN 48
        END AS N
FROM
    sys_calendar.calendar 
WHERE
    day_of_calendar BETWEEN 1 AND 5
;
Enter fullscreen mode Exit fullscreen mode

Then, to use that set of numbers (of months as we'll use it) we:

  • add it to the query as a derived table - called "Make_N"
  • link Make_N as a CROSS JOIN - so that we'll get the same analysis as before but now for every different number of months
  • replace our number 1 usage in the ADD_MONTHS functions with the number from Make_N - note that for clarity I've written those as zero plus and zero minus N
  • add N to the list of GROUP BY fields - so that we can see the outcomes per different months difference

Here we go:

SELECT
    N ,
    month_of_year ,                
    day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day ,
    COUNT(*) AS Rw_Cnt ,
    COUNT( calendar_date ) AS Day_Cnt ,
    COUNT( DISTINCT calendar_date ) AS Day_Ctd ,
    MIN( year_of_calendar ) AS Min_Year ,
    MAX( year_of_calendar ) AS Max_Year ,
    NULLIF( MAX( year_of_calendar ), Min_Year ) AS Mxd_Year 
FROM
    ( -- InterestingDays
        SELECT
            Make_N.N ,
            calendar_date ,                
            year_of_calendar ,
            month_of_year ,                
            day_of_month ,                 
            ADD_MONTHS( calendar_date, 0 - Make_N.N ) AS MonthBefore_Dt , 
            EXTRACT( DAY FROM ADD_MONTHS( calendar_date, 0 - Make_N.N ) ) AS MonthBefore_Day ,
            ADD_MONTHS( calendar_date, 0 + Make_N.N ) AS MonthAfter_Dt , 
            EXTRACT( DAY FROM ADD_MONTHS( calendar_date, 0 + Make_N.N ) ) AS MonthAfter_Day 
        FROM
            sys_calendar.calendar 
            CROSS JOIN
            ( -- Make_N
                SELECT
                    CASE 
                        WHEN day_of_calendar = 1 THEN 1
                        WHEN day_of_calendar = 2 THEN 6
                        WHEN day_of_calendar = 3 THEN 12
                        WHEN day_of_calendar = 4 THEN 24
                        WHEN day_of_calendar = 5 THEN 48
                        END AS N
                FROM
                    sys_calendar.calendar 
                WHERE
                    day_of_calendar BETWEEN 1 AND 5
                ) AS Make_N                 
        WHERE
            NOT ( ( day_of_month = MonthBefore_Day ) AND ( day_of_month = MonthAfter_Day ) AND ( MonthBefore_Day = MonthAfter_Day ) )
        ) AS InterestingDays
GROUP BY
    N ,
    month_of_year ,                
    day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day 
ORDER BY
    ABS( N ) ,
    N ,
    month_of_year ,                
    day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day 
;
Enter fullscreen mode Exit fullscreen mode

and here is what that produces:

N month_of_year day_of_month MonthBefore_Day MonthAfter_Day Rw_Cnt Day_Cnt Day_Ctd Min_Year Max_Year Mxd_Year
1 1 29 29 28 152 152 152 1,900 2,100 2,100
1 1 30 30 28 152 152 152 1,900 2,100 2,100
1 1 30 30 29 49 49 49 1,904 2,096 2,096
1 1 31 31 28 152 152 152 1,900 2,100 2,100
1 1 31 31 29 49 49 49 1,904 2,096 2,096
1 3 29 28 29 152 152 152 1,900 2,100 2,100
1 3 30 28 30 152 152 152 1,900 2,100 2,100
1 3 30 29 30 49 49 49 1,904 2,096 2,096
1 3 31 28 30 152 152 152 1,900 2,100 2,100
1 3 31 29 30 49 49 49 1,904 2,096 2,096
1 5 31 30 30 201 201 201 1,900 2,100 2,100
1 7 31 30 31 201 201 201 1,900 2,100 2,100
1 8 31 31 30 201 201 201 1,900 2,100 2,100
1 10 31 30 30 201 201 201 1,900 2,100 2,100
1 12 31 30 31 201 201 201 1,900 2,100 2,100
6 3 31 30 30 201 201 201 1,900 2,100 2,100
6 5 31 30 30 201 201 201 1,900 2,100 2,100
6 8 29 28 28 103 103 103 1,900 2,100 2,100
6 8 29 28 29 49 49 49 1,903 2,095 2,095
6 8 29 29 28 49 49 49 1,904 2,096 2,096
6 8 30 28 28 103 103 103 1,900 2,100 2,100
6 8 30 28 29 49 49 49 1,903 2,095 2,095
6 8 30 29 28 49 49 49 1,904 2,096 2,096
6 8 31 28 28 103 103 103 1,900 2,100 2,100
6 8 31 28 29 49 49 49 1,903 2,095 2,095
6 8 31 29 28 49 49 49 1,904 2,096 2,096
6 10 31 30 30 201 201 201 1,900 2,100 2,100
6 12 31 30 30 201 201 201 1,900 2,100 2,100
12 2 29 28 28 49 49 49 1,904 2,096 2,096
24 2 29 28 28 49 49 49 1,904 2,096 2,096
48 2 29 28 29 1 1 1 1,904 1,904 ?
48 2 29 29 28 1 1 1 2,096 2,096 ?

Another Improvement - Twelve and Two and Four

Looking at the previous output I did wonder whether I was perhaps missing something interesting for some other combinations.

I decided to change my months-apart set to have from one to twelve months, then two years and four years.

Here is my new inner month-number generating SQL:

SELECT
    CASE 
        WHEN day_of_calendar <= 12 THEN day_of_calendar
        WHEN day_of_calendar = 13 THEN 24
        WHEN day_of_calendar = 14 THEN 48
        END AS N
FROM
    sys_calendar.calendar 
WHERE
    day_of_calendar BETWEEN 1 AND 14
;
Enter fullscreen mode Exit fullscreen mode

and here it is plugged into the previous query.

SELECT
    N ,
    month_of_year ,                
    day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day ,
    COUNT(*) AS Rw_Cnt ,
    COUNT( calendar_date ) AS Day_Cnt ,
    COUNT( DISTINCT calendar_date ) AS Day_Ctd ,
    MIN( year_of_calendar ) AS Min_Year ,
    MAX( year_of_calendar ) AS Max_Year ,
    NULLIF( MAX( year_of_calendar ), Min_Year ) AS Mxd_Year 
FROM
    ( -- InterestingDays
        SELECT
            Make_N.N ,
            calendar_date ,                
            year_of_calendar ,
            month_of_year ,                
            day_of_month ,                 
            ADD_MONTHS( calendar_date, 0 - Make_N.N ) AS MonthBefore_Dt , 
            EXTRACT( DAY FROM ADD_MONTHS( calendar_date, 0 - Make_N.N ) ) AS MonthBefore_Day ,
            ADD_MONTHS( calendar_date, 0 + Make_N.N ) AS MonthAfter_Dt , 
            EXTRACT( DAY FROM ADD_MONTHS( calendar_date, 0 + Make_N.N ) ) AS MonthAfter_Day 
        FROM
            sys_calendar.calendar 
            CROSS JOIN
            ( -- Make_N
                SELECT
                    CASE 
                        WHEN day_of_calendar <= 12 THEN day_of_calendar
                        WHEN day_of_calendar = 13 THEN 24
                        WHEN day_of_calendar = 14 THEN 48
                        END AS N
                FROM
                    sys_calendar.calendar 
                WHERE
                    day_of_calendar BETWEEN 1 AND 14
                ) AS Make_N                 
        WHERE
            NOT ( ( day_of_month = MonthBefore_Day ) AND ( day_of_month = MonthAfter_Day ) AND ( MonthBefore_Day = MonthAfter_Day ) )
        ) AS InterestingDays
GROUP BY
    N ,
    month_of_year ,                
    day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day 
ORDER BY
    ABS( N ) ,
    N ,
    month_of_year ,                
    day_of_month ,                 
    MonthBefore_Day ,
    MonthAfter_Day 
;
Enter fullscreen mode Exit fullscreen mode

and here is what that produces:

N month_of_year day_of_month MonthBefore_Day MonthAfter_Day Rw_Cnt Day_Cnt Day_Ctd Min_Year Max_Year Mxd_Year
1 1 29 29 28 152 152 152 1,900 2,100 2,100
1 1 30 30 28 152 152 152 1,900 2,100 2,100
1 1 30 30 29 49 49 49 1,904 2,096 2,096
1 1 31 31 28 152 152 152 1,900 2,100 2,100
1 1 31 31 29 49 49 49 1,904 2,096 2,096
1 3 29 28 29 152 152 152 1,900 2,100 2,100
1 3 30 28 30 152 152 152 1,900 2,100 2,100
1 3 30 29 30 49 49 49 1,904 2,096 2,096
1 3 31 28 30 152 152 152 1,900 2,100 2,100
1 3 31 29 30 49 49 49 1,904 2,096 2,096
1 5 31 30 30 201 201 201 1,900 2,100 2,100
1 7 31 30 31 201 201 201 1,900 2,100 2,100
1 8 31 31 30 201 201 201 1,900 2,100 2,100
1 10 31 30 30 201 201 201 1,900 2,100 2,100
1 12 31 30 31 201 201 201 1,900 2,100 2,100
2 1 31 30 31 201 201 201 1,900 2,100 2,100
2 4 29 28 29 152 152 152 1,900 2,100 2,100
2 4 30 28 30 152 152 152 1,900 2,100 2,100
2 4 30 29 30 49 49 49 1,904 2,096 2,096
2 7 31 31 30 201 201 201 1,900 2,100 2,100
2 8 31 30 31 201 201 201 1,900 2,100 2,100
2 12 29 29 28 152 152 152 1,900 2,100 2,100
2 12 30 30 28 152 152 152 1,900 2,100 2,100
2 12 30 30 29 49 49 49 1,903 2,095 2,095
2 12 31 31 28 152 152 152 1,900 2,100 2,100
2 12 31 31 29 49 49 49 1,903 2,095 2,095
3 1 31 31 30 201 201 201 1,900 2,100 2,100
3 3 31 31 30 201 201 201 1,900 2,100 2,100
3 5 29 28 29 152 152 152 1,900 2,100 2,100
3 5 30 28 30 152 152 152 1,900 2,100 2,100
3 5 30 29 30 49 49 49 1,904 2,096 2,096
3 5 31 28 31 152 152 152 1,900 2,100 2,100
3 5 31 29 31 49 49 49 1,904 2,096 2,096
3 7 31 30 31 201 201 201 1,900 2,100 2,100
3 8 31 31 30 201 201 201 1,900 2,100 2,100
3 11 29 29 28 152 152 152 1,900 2,100 2,100
3 11 30 30 28 152 152 152 1,900 2,100 2,100
3 11 30 30 29 49 49 49 1,903 2,095 2,095
3 12 31 30 31 201 201 201 1,900 2,100 2,100
4 1 31 30 31 201 201 201 1,900 2,100 2,100
4 3 31 30 31 201 201 201 1,900 2,100 2,100
4 5 31 31 30 201 201 201 1,900 2,100 2,100
4 6 29 28 29 152 152 152 1,900 2,100 2,100
4 6 30 28 30 152 152 152 1,900 2,100 2,100
4 6 30 29 30 49 49 49 1,904 2,096 2,096
4 7 31 31 30 201 201 201 1,900 2,100 2,100
4 8 31 30 31 201 201 201 1,900 2,100 2,100
4 10 29 29 28 152 152 152 1,900 2,100 2,100
4 10 30 30 28 152 152 152 1,900 2,100 2,100
4 10 30 30 29 49 49 49 1,903 2,095 2,095
4 10 31 30 28 152 152 152 1,900 2,100 2,100
4 10 31 30 29 49 49 49 1,903 2,095 2,095
4 12 31 31 30 201 201 201 1,900 2,100 2,100
5 1 31 31 30 201 201 201 1,900 2,100 2,100
5 7 29 28 29 152 152 152 1,900 2,100 2,100
5 7 30 28 30 152 152 152 1,900 2,100 2,100
5 7 30 29 30 49 49 49 1,904 2,096 2,096
5 7 31 28 31 152 152 152 1,900 2,100 2,100
5 7 31 29 31 49 49 49 1,904 2,096 2,096
5 9 29 29 28 152 152 152 1,900 2,100 2,100
5 9 30 30 28 152 152 152 1,900 2,100 2,100
5 9 30 30 29 49 49 49 1,903 2,095 2,095
6 3 31 30 30 201 201 201 1,900 2,100 2,100
6 5 31 30 30 201 201 201 1,900 2,100 2,100
6 8 29 28 28 103 103 103 1,900 2,100 2,100
6 8 29 28 29 49 49 49 1,903 2,095 2,095
6 8 29 29 28 49 49 49 1,904 2,096 2,096
6 8 30 28 28 103 103 103 1,900 2,100 2,100
6 8 30 28 29 49 49 49 1,903 2,095 2,095
6 8 30 29 28 49 49 49 1,904 2,096 2,096
6 8 31 28 28 103 103 103 1,900 2,100 2,100
6 8 31 28 29 49 49 49 1,903 2,095 2,095
6 8 31 29 28 49 49 49 1,904 2,096 2,096
6 10 31 30 30 201 201 201 1,900 2,100 2,100
6 12 31 30 30 201 201 201 1,900 2,100 2,100
7 1 31 30 31 201 201 201 1,900 2,100 2,100
7 7 29 29 28 152 152 152 1,900 2,100 2,100
7 7 30 30 28 152 152 152 1,900 2,100 2,100
7 7 30 30 29 49 49 49 1,903 2,095 2,095
7 7 31 31 28 152 152 152 1,900 2,100 2,100
7 7 31 31 29 49 49 49 1,903 2,095 2,095
7 9 29 28 29 152 152 152 1,900 2,100 2,100
7 9 30 28 30 152 152 152 1,900 2,100 2,100
7 9 30 29 30 49 49 49 1,904 2,096 2,096
8 1 31 31 30 201 201 201 1,900 2,100 2,100
8 3 31 31 30 201 201 201 1,900 2,100 2,100
8 5 31 30 31 201 201 201 1,900 2,100 2,100
8 6 29 29 28 152 152 152 1,900 2,100 2,100
8 6 30 30 28 152 152 152 1,900 2,100 2,100
8 6 30 30 29 49 49 49 1,903 2,095 2,095
8 7 31 30 31 201 201 201 1,900 2,100 2,100
8 8 31 31 30 201 201 201 1,900 2,100 2,100
8 10 29 28 29 152 152 152 1,900 2,100 2,100
8 10 30 28 30 152 152 152 1,900 2,100 2,100
8 10 30 29 30 49 49 49 1,904 2,096 2,096
8 10 31 28 30 152 152 152 1,900 2,100 2,100
8 10 31 29 30 49 49 49 1,904 2,096 2,096
8 12 31 30 31 201 201 201 1,900 2,100 2,100
9 1 31 30 31 201 201 201 1,900 2,100 2,100
9 3 31 30 31 201 201 201 1,900 2,100 2,100
9 5 29 29 28 152 152 152 1,900 2,100 2,100
9 5 30 30 28 152 152 152 1,900 2,100 2,100
9 5 30 30 29 49 49 49 1,903 2,095 2,095
9 5 31 31 28 152 152 152 1,900 2,100 2,100
9 5 31 31 29 49 49 49 1,903 2,095 2,095
9 7 31 31 30 201 201 201 1,900 2,100 2,100
9 8 31 30 31 201 201 201 1,900 2,100 2,100
9 11 29 28 29 152 152 152 1,900 2,100 2,100
9 11 30 28 30 152 152 152 1,900 2,100 2,100
9 11 30 29 30 49 49 49 1,904 2,096 2,096
9 12 31 31 30 201 201 201 1,900 2,100 2,100
10 1 31 31 30 201 201 201 1,900 2,100 2,100
10 4 29 29 28 152 152 152 1,900 2,100 2,100
10 4 30 30 28 152 152 152 1,900 2,100 2,100
10 4 30 30 29 49 49 49 1,903 2,095 2,095
10 7 31 30 31 201 201 201 1,900 2,100 2,100
10 8 31 31 30 201 201 201 1,900 2,100 2,100
10 12 29 28 29 152 152 152 1,900 2,100 2,100
10 12 30 28 30 152 152 152 1,900 2,100 2,100
10 12 30 29 30 49 49 49 1,904 2,096 2,096
10 12 31 28 31 152 152 152 1,900 2,100 2,100
10 12 31 29 31 49 49 49 1,904 2,096 2,096
11 1 29 28 29 152 152 152 1,900 2,100 2,100
11 1 30 28 30 152 152 152 1,900 2,100 2,100
11 1 30 29 30 49 49 49 1,905 2,097 2,097
11 1 31 28 31 152 152 152 1,900 2,100 2,100
11 1 31 29 31 49 49 49 1,905 2,097 2,097
11 3 29 29 28 152 152 152 1,900 2,100 2,100
11 3 30 30 28 152 152 152 1,900 2,100 2,100
11 3 30 30 29 49 49 49 1,903 2,095 2,095
11 3 31 30 28 152 152 152 1,900 2,100 2,100
11 3 31 30 29 49 49 49 1,903 2,095 2,095
11 5 31 30 30 201 201 201 1,900 2,100 2,100
11 7 31 31 30 201 201 201 1,900 2,100 2,100
11 8 31 30 31 201 201 201 1,900 2,100 2,100
11 10 31 30 30 201 201 201 1,900 2,100 2,100
11 12 31 31 30 201 201 201 1,900 2,100 2,100
12 2 29 28 28 49 49 49 1,904 2,096 2,096
24 2 29 28 28 49 49 49 1,904 2,096 2,096
48 2 29 28 29 1 1 1 1,904 1,904 ?
48 2 29 29 28 1 1 1 2,096 2,096 ?

Summary and Challenge

I'm inclined to say the data speaks for itself - particularly that what this is really telling us is how the Teradata ADD_MONTHS function operates.

  • I'd have to be honest that I've been mainly on that platform for so long I probably take how it works - with ADD_MONTHS - as "normal". I vaguely remember seeing discussion about SQL dialects and engines varying in exactly what their methods for this produces.

It would be interesting to see how the other platforms compare, but frankly I don't have any means to do that. Also, I can't even guess what the equivalents are for the Teradata system calendar.

So dear readers: over to you.

💖 💪 🙅 🚩
geraldew
geraldew

Posted on June 3, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Some Teradata SQL Fun with Dates Differing by Months
sql Some Teradata SQL Fun with Dates Differing by Months

June 3, 2022

Data Visualization Techniques and Tools
datascience Data Visualization Techniques and Tools

October 28, 2021