i have small sample of 1 of our tables:
user category initialdate lastrequestdate lastupdate 1 1907 1/1/2010 2/1/2011 1/15/2011 2 6509 6/10/2010 4/1/2011 3/15/2011 3 3102 3/1/2010 3/15/2011 2/17/2011 4 3102 3/1/2010 2/15/2011 3/17/2011 5 6509 5/10/2010 2/1/2011 1/25/2011 6 1907 1/1/2010 3/1/2011 4/15/2011 each user has 1 row in table , users being added.
there 30 categories new categories being added.
i need summary table each day each category counts...
do have suggestion best way handle besides doing temporary tables, cursors, etc...? i'm thinking row_number partition or / , cte...but not sure how this... group won't work because of dates being on same row... when use group 3 rows each sku duplicate counts...
date catalog initialcount lastrequestcount lastupdatecount 1/1/2010 1907 2 0 0 3/1/2010 3102 2 0 0 5/10/2010 6509 1 0 0 6/10/2010 6509 1 0 0 1/15/2011 1907 0 0 2 1/25/2011 6509 0 0 1 2/1/2011 1907 0 1 0 2/1/2011 6509 0 1 0 2/15/201 3102 0 1 0 2/17/2011 3102 0 0 1 3/1/2011 1907 0 1 0 3/15/2011 3102 0 1 0 3/17/2011 3102 0 0 1 4/1/2011 6509 0 1 0 4/15/2011 1907 0 0 1
run pre-query of distinct instances of each category , respective dates... then, use basis next level...
select prequery.uniqdate, prequery.category, sum( case when prequery.uniqdate = yt.initialdate 1 else 0 end ) initialcount, sum( case when prequery.uniqdate = yt.lastrequestdate 1 else 0 end ) lastrequestcount, sum( case when prequery.uniqdate = yt.lastupdate 1 else 0 end ) lastupdatecount ( select distinct category, initialdate uniqdate yourtable union select category, lastrequestdate uniqdate yourtable union select category, lastupdate uniqdate ) prequery join yourtable yt on prequery.category = yt.category , ( prequery.uniqdate = yt.initialdate or prequery.uniqdate = yt.lastrequestdate or prequery.uniqdate = yt.lastupdate ) group prequery.uniqdate, prequery.category 
Comments
Post a Comment