none
Calculation Sales per week RRS feed

  • Pergunta

  • Hi Guys,

    How can I get Sales per week from the below table? 

    Create test_Table (id int not null, starting_date date, ending_date date, sales int)

    insert into Test_Table 

    (1001,2018-10-17,2018-12-02, 100)

    ,(1002,2018-10-17,2018-12-02,203)

    I need to get sales for the below dates for each id. e.g. the total sales for id=1001=100  from 2018-10-17 till 2018-12-02, I need to break down sales to week, same the below result.

    id,starting_date,ending_date,sales

    1001,2018-10-17,2018-10-21,?

    1001,2018-10-22 2018-10-28,?

    1001,2018-10-29 2018-11-04,?

    1001,2018-11-05 2018-11-11,?

    1001,2018-11-12 2018-11-18,?

    1001,2018-11-19 2018-11-25,?

    1002,2018-11-26 2018-12-02,?

    1002,2018-10-17,2018-10-21,?

    1002,2018-10-22 2018-10-28,?

    1002,2018-10-29 2018-11-04,?

    1002,2018-11-05 2018-11-11,?

    1002,2018-11-12 2018-11-18,?

    1002,2018-11-19 2018-11-25,?

    1002,2018-11-26 2018-12-02,?

    Thank you for your help



    <g class="gr_ gr_9 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="9" id="9">zj</g>


    • Editado ZaraJ domingo, 12 de janeiro de 2020 15:05
    • Tipo Alterado Naomi NModerator domingo, 12 de janeiro de 2020 23:04 Seems a question to me
    domingo, 12 de janeiro de 2020 15:05

Todas as Respostas

  • I need to get sales for the below dates for each id. e.g. the total sales for id=1001=100  from 2018-10-17 till 2018-12-02, I need to break down sales to week, same the below result.

    What exactly do you mean by break down the sales by week? Do you want to divide the total sales for each id evenly over the number of weeks (7)? E.g. . id 1001 would be 100 / 7 = 14.285714 and id 102 would be 203 / 7 = 29


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    domingo, 12 de janeiro de 2020 17:35
  • Hi Dan,

     I need to get weekly sales and weekly date( startring_date,ending_date) from 2018-10-17,2018-10-21

    I want to divide the total sales for each id and each week(starting_date,ending_date),

    In my post I put similar result and I need that result


    zj

    domingo, 12 de janeiro de 2020 18:23
  • Here is a query for this:

    ; WITH CTE AS (
        SELECT t.id, datepart(iso_week, d.thedate) AS weekno, COUNT(*) AS daycnt, 
               convert(float, t.sales) as sales, SUM(COUNT(*)) OVER(PARTITION BY t.id) AS totaldaycnt
        FROM   dates d
        JOIN   test_Table t ON d.thedate BETWEEN t.starting_date AND t.ending_date
        GROUP  BY t.id, t.sales, datepart(iso_week, d.thedate)
    )
    SELECT id, weekno, convert(decimal(8,2), sales * daycnt /totaldaycnt) AS sales
    FROM   CTE
    ORDER  BY id, weekno

    dates is a table with one row per date for long period of date. Here is a script to create that table:

    SELECT TOP 80001 n = IDENTITY(int, 0, 1)
    INTO   #numbers
    FROM   sysobjects o1
    CROSS  JOIN sysobjects o2
    CROSS  JOIN  sysobjects o3
    CROSS  JOIN  sysobjects o4
    OPTION (MAXDOP 1)
    go
    -- Make sure we have unique numbers.
    CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
    go
    -- Verify that table does not have gaps.
    IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
       (SELECT MIN(n) FROM #numbers) = 0 AND
       (SELECT MAX(n) FROM #numbers) = 80000
    BEGIN
       DECLARE @msg varchar(255)
    
       -- Insert the dates:
       INSERT dates (thedate)
          SELECT dateadd(DAY, n, '19800101')
          FROM   #numbers
          WHERE  dateadd(DAY, n, '19800101') < '21500101'
    
       SELECT @msg = 'Inserted ' + ltrim(str(@@rowcount)) + ' rows into #numbers'
       PRINT @msg
    END
    ELSE
       RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
    go
    DROP TABLE #numbers
    


    (This script is very old and written for SQL 2000. One of these days I should brush it up.)

    If you want to show dates rather than week numbers, you could extend the dates table with week numbers and look up the Monday from that table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    domingo, 12 de janeiro de 2020 19:45
  • Below is an example to get you started. I suggest you create a calendar table with materialized dates, weeks, etc. to facilitate date range queries like this as that will perform better.

    WITH 
    	 t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    	,t1k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1 AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    	,weekly_calendar AS (SELECT DATEADD(week, num, '2018-01-01') AS week_start_date, DATEADD(day, 6, DATEADD(week, num, '2018-01-01')) AS week_end_date FROM t1k)
    	,weekly_sales AS (
    		SELECT
    			  test_Table.id
    			, weekly_calendar.week_start_date AS starting_date
    			, weekly_calendar.week_end_date AS ending_date
    			, sales
    		FROM weekly_calendar
    		JOIN test_Table ON
    				weekly_calendar.week_end_date >= test_Table.starting_date
    			AND weekly_calendar.week_start_date <= test_Table.ending_date
    	)
    SELECT
    	  id
    	, starting_date
    	, ending_date
    	, sales / (SELECT COUNT(*) FROM weekly_sales AS ws WHERE ws.id = weekly_sales.id) AS sales
    FROM weekly_sales
    ORDER BY
    	id, starting_date;
    


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    domingo, 12 de janeiro de 2020 19:57
  • https://www.merriam-webster.com/dictionary/contiguous

    Is it another British example or just a typo?

    Also, probably the message should be in plural.

    :)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    domingo, 12 de janeiro de 2020 23:06
    Moderador
  • Hi ZaraJ,

    Creating a calendar table will be useful for you. Here is a script:

    Creating a date dimension or calendar table in SQL Server

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    segunda-feira, 13 de janeiro de 2020 08:50
  • Hi ZaraJ,

    Do the answers above help you? Please feel free to let us know if you have any other question.

    It's so kind of you to mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    terça-feira, 14 de janeiro de 2020 01:40