All Forums Database
ChristaS 1 post Joined 03/15
04 Mar 2015
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

 

You must sign in to leave a comment.