Teradata SQL: Group results from date ranges that cross years in same query

0
votes

So I have this query that gives me a count of accounts that had a certain kind of transaction at least once in every month in the given time frame. In this case I'm checking for transactions over a 3 month period. See query below:

select  
  Yr
, count(distinct acct_nbr) as AcctCt

from  (
        select          
          Yr
        , acct_nbr
        , count(distinct Mth) as Mths        

        from  (
                select                  
                  extract(year from tran_dt) as Yr
                , acct_nbr        
                , extract(month from tran_dt) as Mth
                , tran_id

                from br.bdft

                where prod_type = 44
                and (tran_dt between '2017-10-01' and '2017-12-31'
                or tran_dt between '2016-10-01' and '2016-12-31'
                or tran_dt between '2015-10-01' and '2015-12-31'
                or tran_dt between '2014-10-01' and '2014-12-31'
                or tran_dt between '2013-10-01' and '2013-12-31')
                and tran_cd = 'DD'
              ) dt

        group by 1,2                
      ) dt2

where Mths = 3

group by 1

The results I'm getting are as follows:

Yr:    AcctCt:
2017   258527
2016   231304
2015   188550
2014   210339
2013   170781

I'm currently grouping the results by YEAR, by extracting the YEAR from the transaction date. This works fine when then date ranges don't cross years (e.g. what I'm using in my query above). But once I cross years, e.g. 2017-11-01 to 2018-01-31, this approach no longer works.

Is there a better way to group results where it always works irrespective of the date range I'm using?

3
sqlteradata
Those date ranges are always hard-coded/known in advance? – dnoeth
It would run every month. Ideally I would like to automate it so that I don't have to manually update the date range each month, but I'm a SQL novice and don't know how to do that yet (if even possible). – starfly
What are your rules to determine the three months ranges? The current plus the two previous months for the previous 4 plus the current year? – dnoeth
Yes, current month + 2 prior months, and current year + prior 4 years. – starfly

3 Answers

2
votes

Create a second table to hold the time periods (heavy psuedo code : create table time_periods as ID,period_name,start_date,end_date). Join to this table

from br.bdft 
inner join time_periods on bdft.tran_dt between start_date and end_date

Group by the period_name column.

Sorry, short on time, or I would provide more info...however the concept is here (define time periods in a table and join to that table using between). Give a try, I'll revisit today/tomorrow with a full solution if you still require

1
votes

When they cross years, just subtract a certain amount of time. For your example, use this expression for Yr:

extract(year from add_months(tran_dt, -1) ) as Yr
1
votes

Simply adjust the date using ADD_MONTHS before extracting the year. Additionally there's no need for two COUNT(DISTINCT).

This is your query simplified:

select          
  Yr
, count(*) as AcctCt      

from  (
        select                  
          Extract(YEAR From Add_Months(tran_dt,-1)) as Yr
        , acct_nbr        

        from br.bdft

        where prod_type = 44
        and (tran_dt between '2017-11-01' and '2018-01-31'
          or tran_dt between '2016-11-01' and '2017-01-31'
          or tran_dt between '2015-11-01' and '2016-01-31'
          or tran_dt between '2014-11-01' and '2015-01-31'
          or tran_dt between '2013-11-01' and '2014-01-31')
        and tran_cd = 'DD'
        group by acct_nbr
        , yr
        having count(distinct extract(month from tran_dt)) = 3
      ) dt
 group by 1

Edit:

To get the three months ranges based on today you can apply TRUNC/LAST_DAY/ADD_MONTHS calculations on CURRENT_DATE.

select          
  Yr
, count(*) as AcctCt      

from  (
        select 
          -- shift back the date between 0 and 2 months based on the month of the current quarter
          -- to get the same year for consecutive months
          Extract(YEAR From Add_Months(tran_dt, -Extract(MONTH From currdt) MOD 3)) as Yr
        , acct_nbr        

        from br.bdft

        where prod_type = 44
        -- get the current months plus the two previous for the current and the previous four years
        -- will be calculated once = Explain shows hard-coded dates
        AND (   tran_dt BETWEEN Trunc(Add_Months(Current_Date,-2      ), 'mon') AND Last_Day(           Current_Date       ) 
             OR tran_dt BETWEEN Trunc(Add_Months(Current_Date,-2 -1*12), 'mon') AND Last_Day(Add_Months(Current_Date,-1*12))
             OR tran_dt BETWEEN Trunc(Add_Months(Current_Date,-2 -2*12), 'mon') AND Last_Day(Add_Months(Current_Date,-2*12))
             OR tran_dt BETWEEN Trunc(Add_Months(Current_Date,-2 -3*12), 'mon') AND Last_Day(Add_Months(Current_Date,-3*12))
             OR tran_dt BETWEEN Trunc(Add_Months(Current_Date,-2 -4*12), 'mon') AND Last_Day(Add_Months(Current_Date,-4*12))
            )
        and tran_cd = 'DD'
        group by acct_nbr
        , yr
        having count(distinct extract(month from tran_dt)) = 3
      ) dt
 group by 1 as Yr