Analyst Analyst - 1 year ago 44
SQL Question

How to create a dynamic where clause in sql?

So I have created a table that has the following columns from a transaction table with all customer purchase records:
1. Month-Year, 2.Customer ID, 3. Number of Transactions in that month.

I'm trying to create a table that has the output of
1. Month-Year, 2. Number of active customers defined by having at least 1 purchase in the previous year.

The code that I have currently is this but the case when obviously only capturing one date and the where clause isn't dynamic. Would really appreciate your help.

select month_start_date, cust_ID,
(case when month_start_Date between date and add_months(date, -12) then count(cust_ID) else 0 end) as active
from myserver.mytable
group by 1,2

EDIT: I'm just trying to put a flag next to a customer if they are active in each month defined as having at least one transaction in the last year thanks!

Answer Source

You might use Teradata's proprietary EXPAND ON synax for creating time series:

SELECT month_start_date, COUNT(*)
 ( -- create one row for every month within the next year
   -- after a customer's transaction  
      BEGIN(pd) AS month_start_date, 
   FROM myserver.mytable
   EXPAND ON PERIOD(month_start_date, ADD_MONTHS(month_start_date,12)) AS pd
       BY ANCHOR MONTH_BEGIN         -- every 1st of month
       FOR PERIOD (DATE - 500, DATE) -- use this to restrict to a specific date range
 ) AS dt
GROUP BY month_start_date
ORDER BY month_start_date