## count the order on weekly basis

 Author Message vinaya_2690 Forum Newbie Group: General Forum Members Points: 1 Visits: 3 I want the count of orders of a particular table on weekly basis i.e if date given to me is 10\03\2014 then my output should be count of orders from date 10\03\2014 to 09\03\2014(one week) then count of orders from 2\03\2014 to 08\03\2014(another week) and then from 24\02\2014 to 01\03\2014(another week)..... Koen Verbeeck SSC Guru Group: General Forum Members Points: 59919 Visits: 13297 That is fascinating.Is there also a question?Hint: read the links How to post forum questions and Need an answer? No, you need a question.ps: just to shove you in the right direction: group by week (use the DATEPART function to find the week) and count the orders. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP Lynn Pettis SSC Guru Group: General Forum Members Points: 91190 Visits: 38945 vinaya_2690 (3/10/2014)I want the count of orders of a particular table on weekly basis i.e if date given to me is 10\03\2014 then my output should be count of orders from date 10\03\2014 to 09\03\2014(one week) then count of orders from 2\03\2014 to 08\03\2014(another week) and then from 24\02\2014 to 01\03\2014(another week).....Also, hard to tell what your weeks are from the dates given above. Since this is an international site you may want to post dates in the yyyy-mm-dd format and show start date first followed be end date.From the information given, there really isn't much we can do but guess at what you are actually trying to accomplish. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Luis Cazares SSC-Forever Group: General Forum Members Points: 40354 Visits: 19807 Koen Verbeeck (3/10/2014)That is fascinating.Is there also a question?Hint: read the links How to post forum questions and Need an answer? No, you need a question.ps: just to shove you in the right direction: group by week (use the DATEPART function to find the week) and count the orders.Instead of DATEPART, I would suggest a date calculation to get the first (or last) day of the week to group by it as it would be more descriptive. Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2 Koen Verbeeck SSC Guru Group: General Forum Members Points: 59919 Visits: 13297 Luis Cazares (3/10/2014)Koen Verbeeck (3/10/2014)That is fascinating.Is there also a question?Hint: read the links How to post forum questions and Need an answer? No, you need a question.ps: just to shove you in the right direction: group by week (use the DATEPART function to find the week) and count the orders.Instead of DATEPART, I would suggest a date calculation to get the first (or last) day of the week to group by it as it would be more descriptive.Possibly. I'll think about it when I see some sample data and desired output :-D How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP Aditya Daruka SSC Rookie Group: General Forum Members Points: 45 Visits: 177 Hello,Please find an example sql below. Hope it helps.`-- Drop table if it already existsIF (OBJECT_ID('tempdb..#tmpOrders') IS NOT NULL)BEGIN DROP TABLE #tmpOrders ;END ;-- Create temp table to hold ordersCREATE TABLE #tmpOrders( OrderNo INT IDENTITY (1, 1) NOT NULL , OrderQuantity INT NOT NULL , OrderDate DATE NOT NULL) ;-- Insert ordersINSERT INTO #tmpOrdersSELECT o.OrderQuantity , o.OrderDateFROM ( VALUES ('100', '2014-03-01') , ('200', '2014-03-10') , ('200', '2014-03-09') , ('300', '2014-03-08') , ('300', '2014-03-02') , ('300', '2014-03-07') , ('400', '2013-03-10') , ('500', '2013-03-10') , ('700', '2013-02-10') , ('800', '2013-01-10')) o(OrderQuantity, OrderDate)ORDER BY o.OrderDate ;-- Select orders-- SELECT * FROM #tmpOrders ;-- Set 'Sunday' as the first day of the week (SELECT @@DATEFIRST SET DATEFIRST 7 ;-- Select the Windowed dataSELECT *FROM #tmpOrders oCROSS APPLY ( SELECT CAST(YEAR(o.OrderDate) AS CHAR(4)) + CAST(DATEPART(WEEK, o.OrderDate) AS VARCHAR(2))) xWindow(WindowingValue) ;-- Select the order countsSELECT Window = xwindow.WindowingValue , OrderCount = COUNT(*)FROM #tmpOrders oCROSS APPLY ( SELECT CAST(YEAR(o.OrderDate) AS CHAR(4)) + CAST(DATEPART(WEEK, o.OrderDate) AS VARCHAR(2))) xWindow(WindowingValue)GROUP BY xwindow.WindowingValue ;-- CleanupIF (OBJECT_ID('tempdb..#tmpOrders') IS NOT NULL)BEGIN DROP TABLE #tmpOrders ;END ;GO`