Hi everyone,
I need help to create a loop for each month for calculating the count of ID keeping the minimum date fix i.e the launch date. I am sharing the logic below :
Fact_Demand:
Load
patient_id,
brand_name,
fiscal_month_end_date
from
[FACT__SALES.qvd](qvd)
where len(patient_id)>0 and isnull(disc_date) and Year(fiscal_month_end_date)='2015';
LET vMinActiveDate=Num(MakeDate(2015,01,01));
Demand:
NoConcatenate
Load
patient_id,
brand_name,
'1/31/2015' as fiscal_month_end_date
Resident Fact_Demand where num(fiscal_month_end_date)>=$(vMinActiveDate) and num(fiscal_month_end_date)<='42035';
Concatenate (Demand)
Load
patient_id,
brand_name,
'2/28/2015' as fiscal_month_end_date
Resident Fact_Demand where num(fiscal_month_end_date)>=$(vMinActiveDate) and num(fiscal_month_end_date)<='42063';
Concatenate (Demand)
Load
patient_id,
brand_name,
'3/31/2015' as fiscal_month_end_date
Resident Fact_Demand where num(fiscal_month_end_date)>=$(vMinActiveDate) and num(fiscal_month_end_date)<='42094';
Drop Table Fact_Demand;
Can someone help me create a loop where monthend can be dynamic and I can get data from 2015 for each month till 2022.
