count the order on weekly basis

  • 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).....

  • That is fascinating.

    Is there also a question?

    Hint: read the links How to post forum questions[/url] 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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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.

  • Koen Verbeeck (3/10/2014)


    That is fascinating.

    Is there also a question?

    Hint: read the links How to post forum questions[/url] 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
  • 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[/url] 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 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hello,

    Please find an example sql below. Hope it helps.

    -- Drop table if it already exists

    IF (OBJECT_ID('tempdb..#tmpOrders') IS NOT NULL)

    BEGIN

    DROP TABLE #tmpOrders ;

    END ;

    -- Create temp table to hold orders

    CREATE TABLE #tmpOrders

    (

    OrderNo INT IDENTITY (1, 1) NOT NULL

    , OrderQuantity INT NOT NULL

    , OrderDate DATE NOT NULL

    ) ;

    -- Insert orders

    INSERT INTO #tmpOrders

    SELECT o.OrderQuantity

    , o.OrderDate

    FROM (

    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 data

    SELECT *

    FROM #tmpOrders o

    CROSS APPLY (

    SELECT CAST(YEAR(o.OrderDate) AS CHAR(4)) + CAST(DATEPART(WEEK, o.OrderDate) AS VARCHAR(2))

    ) xWindow(WindowingValue) ;

    -- Select the order counts

    SELECT Window = xwindow.WindowingValue

    , OrderCount = COUNT(*)

    FROM #tmpOrders o

    CROSS APPLY (

    SELECT CAST(YEAR(o.OrderDate) AS CHAR(4)) + CAST(DATEPART(WEEK, o.OrderDate) AS VARCHAR(2))

    ) xWindow(WindowingValue)

    GROUP BY xwindow.WindowingValue ;

    -- Cleanup

    IF (OBJECT_ID('tempdb..#tmpOrders') IS NOT NULL)

    BEGIN

    DROP TABLE #tmpOrders ;

    END ;

    GO

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

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