SQL for last 52 Weeks

Database

SQL for last 52 Weeks

Hello,

I am tyring to write a query that will give me the customer counts on a rolling 52 week ending on monthly basis. I can't seem to find anything anywhere that will help me. I am trying to avoid pulling the query 12 times to get the 52 week customer counts ending for each month!

this is what I have so far:

Select brand_dsc, count(distinct xtra_card_nbr)
,case when num_txn=1 then 'Trial' else 'Repeat' end as Tot_txn

From

(Select xtra_card_nbr, extnd_scan_amt, cat_dsc, brand_dsc, fiscal_month_dsc, cat_nbr,d.date_dt

,count(distinct(case when txn_item_type_cd='1' then d.xtra_card_nbr||store_nbr||d.date_dt||txn_nbr||visit_nbr else 0 end)) as num_txn

From P_Ent.POS_txn_dtl d

Join P_Ent.SKU s on d.sku_nbr=s.SKU_nbr

Join P_ent.Day_calendar dc on d.date_dt=dc.date_Dt

Where cat_nbr in (74,3)

and brand_dsc like 'Radiance Platinum'

and extnd_scan_amt>0

and txn_item_type_cd='1'

and d.date_dt between add_month (date - 12) and date

group by 1,2,3,4,5,6,7
)sub
group by 1,3

I think perhaps the Add_month isn't right or I should be using an Extract statement? or should I just be using a Union to pull each segment?

Thanks

Christa