Post reply

Need hep wtih a complicated aggregate

  • October 29, 2012 at 8:48 am

    #266136

    Hello! I've been tasked with generating some data that seems like it's going to be quite challenging to put together (with my understanding, at least!). The business owner would like to determine, for the past 365 days, what the biggest seven-day sales numbers for every item in our database are. I've had to do something similar to this recently, where I had to aggregate monthly sales for all items, and that was done with a smattering of temp tables; that worked out quite well, but I'm honestly not sure how to approach this.

    The problem is, I'm not quite sure how much sample data is allowable to post here, and how much everyone would be alright with using. I've got 102,000 rows of sample data cooked up, but I think that might be overkill :hehe:. I'd like to provide as much as I need to in order to get accurate help, but I don't know if dumping that much here is a good idea or not. Considering that I'll need several items and 52 weeks' worth of data, though, I don't know how much I can trim down the data supplied.

    Anyhow, here's some of the data I'm working with; it covers just three products over the requested timespan, but it's still 342 rows. Hopefully this isn't too weighty!

    IF OBJECT_ID('TempDB..#DateAggregates','U') IS NOT NULL

    DROP TABLE #DateAggregates

    CREATE TABLE #DateAggregates

    (

    OrderNumber int,

    OrderDate DateTime,

    SKU varchar(75),

    Quantity int)

    INSERT INTO #DateAggregates(OrderNumber, OrderDate, SKU, Quantity)

    SELECT '149735','Oct 28 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '150098','Nov 1 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '150658','Nov 6 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '150816','Nov 6 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '150895','Nov 6 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '151311','Nov 9 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '151421','Nov 10 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '151690','Nov 13 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '151970','Nov 14 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '155759','Nov 29 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '155761','Nov 29 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '155831','Nov 29 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '156041','Nov 30 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '156101','Nov 30 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '156139','Nov 30 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '156668','Dec 1 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '156675','Dec 1 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '156681','Dec 1 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '156684','Dec 1 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '156998','Dec 2 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157012','Dec 2 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157302','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157913','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157922','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157928','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157930','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157932','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157951','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157963','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157973','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157987','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157995','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '157998','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '158001','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '158021','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '158026','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '158063','Dec 4 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '158639','Dec 6 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '159353','Dec 7 2011 12:00AM','ACC12049','4' UNION ALL

    SELECT '159734','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '159746','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '159749','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '159757','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '159761','Dec 8 2011 12:00AM','ACC12049','2' UNION ALL

    SELECT '159768','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '159769','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '159775','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '159787','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '159803','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '159835','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '159845','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '160035','Dec 8 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '160214','Dec 9 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '163708','Dec 14 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '163873','Dec 14 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '164071','Dec 15 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '164355','Dec 15 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '164455','Dec 15 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '164465','Dec 15 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '164680','Dec 16 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '164790','Dec 16 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '164816','Dec 16 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '165302','Dec 18 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '165457','Dec 18 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '165902','Dec 19 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '165988','Dec 20 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '165996','Dec 20 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166087','Dec 20 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166102','Dec 20 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166107','Dec 20 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166124','Dec 20 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166155','Dec 20 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166386','Dec 21 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166394','Dec 21 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166407','Dec 21 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166415','Dec 21 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166428','Dec 21 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166441','Dec 21 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166477','Dec 22 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166629','Dec 22 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166637','Dec 22 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166641','Dec 22 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166645','Dec 22 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '166656','Dec 22 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '167465','Dec 26 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '167751','Dec 27 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '167819','Dec 27 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '167889','Dec 27 2011 12:00AM','ACC12049','2' UNION ALL

    SELECT '168066','Dec 28 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '168083','Dec 28 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '168194','Dec 28 2011 12:00AM','ACC12049','1' UNION ALL

    SELECT '168718','Jan 3 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '168804','Jan 3 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '169052','Jan 3 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '170199','Jan 9 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '170909','Jan 12 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '170998','Jan 13 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '183710','Apr 30 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '185370','May 18 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '185755','May 23 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '188345','Jun 19 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '188367','Jun 19 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '189244','Jun 27 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '207251','Aug 13 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '221209','Sep 17 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '222505','Sep 19 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '222995','Sep 21 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '223737','Sep 24 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '223778','Sep 24 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '235409','Oct 23 2012 12:00AM','ACC12049','1' UNION ALL

    SELECT '236005','Oct 24 2012 12:00AM','ACC12049','2' UNION ALL

    SELECT '150540','Nov 4 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '152498','Nov 17 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '154506','Nov 26 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '154668','Nov 26 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '154680','Nov 26 2011 12:00AM','DOW7202','2' UNION ALL

    SELECT '155275','Nov 28 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '155278','Nov 28 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '155713','Nov 29 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '155808','Nov 29 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '155836','Nov 29 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '155866','Nov 29 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '156261','Nov 30 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '156562','Dec 1 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '156583','Dec 1 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '156734','Dec 1 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '156742','Dec 1 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '156743','Dec 1 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '156745','Dec 1 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '157071','Dec 2 2011 12:00AM','DOW7202','2' UNION ALL

    SELECT '157096','Dec 2 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '157110','Dec 2 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '157145','Dec 2 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '158423','Dec 5 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '159389','Dec 8 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '159400','Dec 8 2011 12:00AM','DOW7202','2' UNION ALL

    SELECT '160329','Dec 9 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '160623','Dec 10 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '162136','Dec 12 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '162662','Dec 13 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '165538','Dec 18 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '165925','Dec 20 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '166111','Dec 20 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '166451','Dec 21 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '166457','Dec 22 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '166464','Dec 22 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '166636','Dec 22 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '166687','Dec 22 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '166945','Dec 23 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '167003','Dec 26 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '167284','Dec 26 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '167366','Dec 26 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '167373','Dec 26 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '167431','Dec 26 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '167758','Dec 27 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '167791','Dec 27 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '167794','Dec 27 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '167799','Dec 27 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '167853','Dec 27 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168141','Dec 28 2011 12:00AM','DOW7202','2' UNION ALL

    SELECT '168161','Dec 28 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168162','Dec 28 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168167','Dec 28 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168199','Dec 28 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168330','Dec 29 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168344','Dec 29 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168346','Dec 29 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168347','Dec 29 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168385','Dec 29 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168533','Dec 30 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168536','Dec 30 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168543','Dec 30 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168552','Dec 30 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168560','Dec 30 2011 12:00AM','DOW7202','1' UNION ALL

    SELECT '168995','Jan 3 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169055','Jan 3 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169059','Jan 3 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169063','Jan 3 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169065','Jan 3 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169458','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169459','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169460','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169461','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169464','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169474','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169483','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169522','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169534','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169535','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169554','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169555','Jan 4 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169651','Jan 5 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169657','Jan 5 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169659','Jan 5 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169664','Jan 5 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '169917','Jan 6 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '170241','Jan 9 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '170244','Jan 9 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '170446','Jan 9 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '171593','Jan 17 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '173843','Feb 2 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '175159','Feb 13 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '175227','Feb 13 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '175455','Feb 15 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '185083','May 14 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '187789','Jun 13 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '188315','Jun 18 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '188521','Jun 20 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '189745','Jul 2 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '199977','Jul 25 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '208074','Aug 14 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '228269','Oct 8 2012 12:00AM','DOW7202','1' UNION ALL

    SELECT '150912','Nov 7 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '150957','Nov 7 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '151618','Nov 11 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '151687','Nov 13 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '152216','Nov 15 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '153291','Nov 21 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '153711','Nov 23 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '153834','Nov 23 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '154383','Nov 26 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '156153','Nov 30 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '156289','Nov 30 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '158291','Dec 5 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '159438','Dec 8 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '162321','Dec 12 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '164711','Dec 16 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '166810','Dec 23 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '166874','Dec 23 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '166902','Dec 23 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '166922','Dec 23 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '166976','Dec 23 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '168085','Dec 28 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '168104','Dec 28 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '168168','Dec 28 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '168176','Dec 28 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '168311','Dec 29 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '168336','Dec 29 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '168338','Dec 29 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '168350','Dec 29 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '168392','Dec 29 2011 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '168904','Jan 3 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '168983','Jan 3 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '169068','Jan 3 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '169137','Jan 3 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '169157','Jan 3 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '169224','Jan 3 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '169360','Jan 4 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170206','Jan 9 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170255','Jan 9 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170297','Jan 9 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170308','Jan 9 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170328','Jan 9 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170541','Jan 10 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170643','Jan 10 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170674','Jan 11 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170751','Jan 11 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170754','Jan 11 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170919','Jan 12 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170921','Jan 12 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '170960','Jan 12 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171064','Jan 13 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171076','Jan 13 2012 12:00AM','PSIIBCRES1','2' UNION ALL

    SELECT '171121','Jan 15 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171134','Jan 15 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171136','Jan 15 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171157','Jan 15 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171399','Jan 16 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171463','Jan 16 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171518','Jan 17 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171566','Jan 17 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171568','Jan 17 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171570','Jan 17 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171571','Jan 17 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171591','Jan 17 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171614','Jan 17 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171636','Jan 17 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171638','Jan 17 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171661','Jan 17 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171746','Jan 18 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171748','Jan 18 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171757','Jan 18 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171763','Jan 18 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171768','Jan 18 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171782','Jan 18 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171786','Jan 18 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171833','Jan 18 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '171836','Jan 18 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '172084','Jan 20 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '172168','Jan 23 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '172197','Jan 23 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '172272','Jan 23 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '172274','Jan 23 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '172616','Jan 24 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '172674','Jan 24 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '172932','Jan 26 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '172989','Jan 27 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '173094','Jan 30 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '173325','Jan 30 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '173371','Jan 30 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '173394','Jan 30 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '173428','Jan 31 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '173536','Jan 31 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '173588','Feb 1 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '174023','Feb 6 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '174157','Feb 6 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '174695','Feb 9 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '174949','Feb 13 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '175261','Feb 14 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '175296','Feb 14 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176113','Feb 20 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176114','Feb 20 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176138','Feb 21 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176139','Feb 21 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176226','Feb 21 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176242','Feb 21 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176276','Feb 21 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176280','Feb 21 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176283','Feb 21 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176284','Feb 21 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176429','Feb 22 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '176970','Feb 27 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '177103','Feb 28 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '177179','Feb 28 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '179273','Mar 19 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '179288','Mar 19 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '179749','Mar 23 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '179797','Mar 23 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '179952','Mar 26 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '180212','Mar 27 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '181382','Apr 9 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '181907','Apr 11 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '186575','Jun 1 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '186680','Jun 4 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '186773','Jun 4 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '187175','Jun 7 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '187716','Jun 12 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '187721','Jun 12 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '187722','Jun 12 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '187730','Jun 13 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '188046','Jun 15 2012 12:00AM','PSIIBCRES1','1' UNION ALL

    SELECT '195752','Jul 16 2012 12:00AM','PSIIBCRES1','1'

    SET DATEFORMAT dmy

    If that proves to be too much of a data barrage, though, general solution suggestions are perfectly appreciated! My current idea seems unwieldy, in that I was going to try to do a weekly aggregate in temp tables and make determinations from there, but it seems incredibly clunky... I believe a calendar table could also be used here, so I've started reading up on those to see if something clicks. If there's a better solution idea that I could use that I'm not seeing, I could merrily experiment on it until I figure it out myself. Either is very well acceptable :-). Thank you for your time!

    EDIT: Gah, forgot to post the date qualifications for this task. A week, for us, starts on Sunday and ends on Saturday, and if week carries over into a new month, it doesn't matter; our current week, for example, starts on the 28th and goes to the 3rd.

    - 😀

  • October 29, 2012 at 9:23 am

    #1553769

    Try using a table of dates.

    Code posted here Creates a table variable with dates in it, however you can/Should have a table of dates,

    They are great at helping solve problems like this.

    Heres an article worth a read.

    [/url]

    This query will give you a rolling total by range, with sdate being the start date.

    This should get you started.

    DECLARE @Dates TABLE (ID INT IDENTITY, Date DATETIME)

    -- Credit to Itzik Ben-Gan for the spiffy query to create a table of numbers then converted to dates

    ;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),

    Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),

    Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),

    Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),

    Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )

    INSERT INTO @Dates(Date)

    SELECT DATEADD(dd, num, CAST('2011-10-28 00:00:00.000' AS DATETIME) -1) AS Date

    FROM (SELECT ROW_NUMBER() OVER (ORDER BY n) AS num

    FROM Nbrs ) t

    WHERE DATEADD(dd, num, CAST('2011-10-28 00:00:00.000' AS DATETIME) -1) BETWEEN '2011-10-28 00:00:00.000' AND '2012-10-24 00:00:00.000'

    SELECT

    d.Date AS sDate

    ,DATEADD(dd, 7, d.Date) AS eDate

    ,SKU

    ,SUM(a.Quantity) AS SoldDuringRange

    FROM @Dates d

    LEFT OUTER JOIN #DateAggregates a

    ON a.OrderDate BETWEEN d.Date AND DATEADD(dd, 7, d.Date)

    GROUP BY d.Date

    ,DATEADD(dd, 7, d.Date)

    ,SKU

    ORDER BY SoldDuringRange DESC

  • October 29, 2012 at 11:21 am

    #1553833

    Ray, thank you for replying; I can see where this query is going, and I think it should be able to help me put something together, but it seems there's a bit of a problem with it. On the line where the CTE is declared, I'm getting a conversion error from varchar to datetime; tinkered with the query for a bit, but I couldn't figure out where things needed to be adjusted.

    - 😀

  • October 29, 2012 at 12:57 pm

    #1553873

    Sorry, I didn't run this

    SET DATEFORMAT dmy

    Change the date strings to follow your dmy format,

    Or run this.

    SET DATEFORMAT ymd

  • October 29, 2012 at 1:08 pm

    #1553879

    Doh! I really should've noticed that the date strings were in a different order... My mistake. This seems to do exactly what I'll need it to do; much appreciated! I'll be tinkering with it to adapt it to the full problem set and to understand exactly how it works, since I get the feeling this sort of thing will be needed again in the future. Thanks again!

    - 😀

  • October 29, 2012 at 2:44 pm

    #1553917

    Alright, I think I've got it! The code above seems to break everything into a week-long interval whenever a sale happens, so a sale on Monday creates an interval from that Monday to the next one, and so on. With that, I just have to toss out all of the intervals that start on anything except Sunday by using DATEPART(dw,sDate) = 1.

    After that, I only want the intervals with the most sales per item, so I put the results of the query into another temp table and run a MAX on SoldDuringRange, and group by SKU, which produces the most sales for every item over the entire year divided into 52 intervals that begin on Sunday. Whew! I don't believe I need to know the dates that the interval ran through, but I can tack that on as well if needed.

    Thank you again for the help; this definitely got me where I needed to be. I'll spend more time tomorrow dissecting the code to figure out exactly how it works, with the hopes of being able to do something like this myself the next time it comes up 🙂

    - 😀

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply