Counting Records Through Multiple Date Ranges

Database

Counting Records Through Multiple Date Ranges

I am pretty new to teradata and know some basic SQL but I find that I am struggling with this problem.

I need to count the number of records for total sales and cash sales according to what quarter they were sold in. This is similar to what I have inherited. It gives me the count of both for 1 quarter at a time. What I would like is for it to give me the count for each quarter from the past year without me needing to go in and hard code in the dates each time.

create table count_types as 
 (select a.geo_name,  a.state, c.ct_all, c. ct_cash
  from msa_list as a
           left join
                     (select d.state, count(*) as ct_all, sum(case transaction_type when 'Cash'  then 1 else 0 end) as ct_cash
   from
                            (select propstate, sale_date, transaction_type, salesprice
                             from BestTransaction
                             where sale_date >='2013-01-01' and sale_date <='2013-03-31'
                                          and salesprice>1000)
                     as c
                     on a.state=c.propstate);  
   
1 REPLY

Re: Counting Records Through Multiple Date Ranges

What I've ended up doing is adding in a case statement to specify the range of dates to the quarters. Then I grouped by the quarters.

Just wondering if anyone out there has a better way of doing this.