Custom calculation for amount

I have the following table

Date      Value promo   item
01/01/2011  626 0   1230
01/02/2011  231 1   1230
01/03/2011  572 1   1230
01/04/2011  775 1   1230
01/05/2011  660 1   1230
01/06/2011  662 1   1230
01/07/2011  541 1   1230
01/08/2011  849 1   1230
01/09/2011  632 1   1230
01/10/2011  906 1   1230
01/11/2011  961 1   1230
01/12/2011  361 0   1230
01/01/2012  461 0   1230
01/02/2012  928 1   1230
01/03/2012  855 0   1230
01/04/2012  605 0   1230
01/05/2012  83  0   1230
01/06/2012  44  0   1230
01/07/2012  382 0   1230
01/08/2012  862 0   1230
01/09/2012  549 0   1230
01/10/2012  632 0   1230
01/11/2012  2   0   1230
01/12/2012  26  0   1230

I try to calculate average sum (SoldAmt)/number od days between the min date and max date rolling back the first 28 rows(4 weeks ) in which promo =1 by Article

  • How to insert data into SQL Server
  • SSIS - Odbc source don't take dynamic variable
  • Populate custom tree with records from database
  • linq query Sum and group by
  • How do you increase the limit of an SQL query on click of a button?
  • How to create sequential ID's per user in Postgresql
  • The smoothing period is 4 weeks on the back regardless of the day discount.

    That is to say, if such item is in promotion for a week during the last 4 weeks smoothing is over 5 weeks without regard to the promotion of sales week.

    How to calculate the first 4 weeks/28rows data order by time for promo =1?

    I try

             CREATE TABLE #RollingTotalsExample
    (
        [Date]     DATE 
        ,[Value]   INT
        ,promo float 
        ,item int 
    );
    
    INSERT INTO #RollingTotalsExample
    SELECT '2011-01-01',626,1,1230
    UNION ALL SELECT '2011-02-01',231,1,1230 UNION ALL SELECT '2011-03-01',572,1,1230
    UNION ALL SELECT '2011-04-01',775,1,1230 UNION ALL SELECT '2011-05-01',660,1,1230
    UNION ALL SELECT '2011-06-01',662,1,1230 UNION ALL SELECT '2011-07-01',541,1,1230
    UNION ALL SELECT '2016-08-01',849,1,1230 UNION ALL SELECT '2016-09-01',632,1,1230
    UNION ALL SELECT '2016-10-01',906,1,1230 UNION ALL SELECT '2016-11-01',961,1,1230
    UNION ALL SELECT '2016-04-01',775,1,1230 UNION ALL SELECT '2016-05-01',660,1,1230
    UNION ALL SELECT '2016-06-01',662,1,1230 UNION ALL SELECT '2016-07-01',541,1,1230
    UNION ALL SELECT '2016-08-01',849,1,1230 UNION ALL SELECT '2016-09-01',632,1,1230
    UNION ALL SELECT '2016-10-01',906,1,1230 UNION ALL SELECT '2016-11-01',961,1,1230
    UNION ALL SELECT '2016-12-01',361,0,1230 UNION ALL SELECT '2012-01-01',461,0,1230
    UNION ALL SELECT '2012-02-01',928,0,1230 UNION ALL SELECT '2012-03-01',855,0,1230
    UNION ALL SELECT '2012-04-01',605,0,1230 UNION ALL SELECT '2012-05-01',83,0,1230
    UNION ALL SELECT '2012-06-01',44,0,1230 UNION ALL SELECT '2012-07-01',382,0,1230
    UNION ALL SELECT '2012-08-01',862,0,1230 UNION ALL SELECT '2012-09-01',549,0,1230
    UNION ALL SELECT '2012-10-01',632,0,1230 UNION ALL SELECT '2012-11-01',2,0,1230
    UNION ALL SELECT '2012-12-01',26,0,1230;
     SELECT * FROM #RollingTotalsExample;
    
        -- Rolling twelve month total by using INNER JOIN
        SELECT a.[Date]
            ,Value=MAX(CASE WHEN a.[Date] = b.[Date] THEN a.Value END)
            ,Rolling12Months=CASE
                                WHEN ROW_NUMBER() OVER (ORDER BY a.[Date]) < 12
                                THEN NULL
                                ELSE SUM(b.Value)
                                END
        FROM #RollingTotalsExample a
        JOIN #RollingTotalsExample b ON b.[Date] BETWEEN DATEADD(month, -11, a.[Date]) AND a.[Date]
        GROUP BY a.[Date]
        ORDER BY a.[Date];
    

    Now how to modify the query in order to calculate average sum (SoldAmt)/number od days between the min date and max date rolling back the 28 first rows data order by time for promo =1 by Article

    3 Solutions collect form web for “Custom calculation for amount”

    Here is an alternate approach that requires LAG() which is available from SQL 2012, but note the sample data does not contain “28 distinct days” prior to each date. Also the actual data type being used isn’t known (date/smalldatetime/datetime/datetime2) nor is it known if truncating time from date is needed. So with some caveats, this approach creates a series of date ranges for the 28 distinct dates (but is the data doesn’t provide these then they are 28 elapsed days). Here is a is a sqlfiddle demo

    PostgreSQL 9.3 Schema Setup:
    (as SQL Server not operating at sqlfiddle)

    CREATE TABLE Table1
        (theDate timestamp, Value int, promo int, item int)
    ;
    
    INSERT INTO Table1
        (theDate, Value, promo, item)
    VALUES
        ('2011-01-01 00:00:00', 626, 0, 1230),
        ('2011-01-02 00:00:00', 231, 1, 1230),
        ('2011-01-03 00:00:00', 572, 1, 1230),
        ('2011-01-04 00:00:00', 775, 1, 1230),
        ('2011-01-05 00:00:00', 660, 1, 1230),
        ('2011-01-06 00:00:00', 662, 1, 1230),
        ('2011-01-07 00:00:00', 541, 1, 1230),
        ('2011-01-08 00:00:00', 849, 1, 1230),
        ('2011-01-09 00:00:00', 632, 1, 1230),
        ('2011-01-10 00:00:00', 906, 1, 1230),
        ('2011-01-11 00:00:00', 961, 1, 1230),
        ('2011-01-12 00:00:00', 361, 0, 1230),
        ('2012-01-01 00:00:00', 461, 0, 1230),
        ('2012-01-02 00:00:00', 928, 1, 1230),
        ('2012-01-03 00:00:00', 855, 0, 1230),
        ('2012-01-04 00:00:00', 605, 0, 1230),
        ('2012-01-05 00:00:00', 83, 0, 1230),
        ('2012-01-06 00:00:00', 44, 0, 1230),
        ('2012-01-07 00:00:00', 382, 0, 1230),
        ('2012-01-08 00:00:00', 862, 0, 1230),
        ('2012-01-09 00:00:00', 549, 0, 1230),
        ('2012-01-10 00:00:00', 632, 0, 1230),
        ('2012-01-11 00:00:00', 2, 0, 1230),
        ('2012-01-12 00:00:00', 26, 0, 1230)
    ;
    

    Query 1:

    select
          t1.item
        , ranges.theStart
        , ranges.theEnd
        , sum(t1.value)
        , sum(t1.value) / 28 avg
    from (
          select
                coalesce(lag(theDay,28) over(order by theDay) , theDay - INTERVAL '28 DAYS') as theStart
              , theDay as theEnd
          from (
                select distinct cast(thedate as date) theDay from Table1
                ) days
          ) ranges
    inner join table1 t1 on theDate between ranges.theStart and ranges.theEnd
    group by
          t1.item
        , ranges.theStart
        , ranges.theEnd
    

    Results:

    | item |                   thestart |                    theend |  sum | avg |
    |------|----------------------------|---------------------------|------|-----|
    | 1230 | December, 04 2010 00:00:00 | January, 01 2011 00:00:00 |  626 |  22 |
    | 1230 | December, 05 2010 00:00:00 | January, 02 2011 00:00:00 |  857 |  30 |
    | 1230 | December, 06 2010 00:00:00 | January, 03 2011 00:00:00 | 1429 |  51 |
    | 1230 | December, 07 2010 00:00:00 | January, 04 2011 00:00:00 | 2204 |  78 |
    | 1230 | December, 08 2010 00:00:00 | January, 05 2011 00:00:00 | 2864 | 102 |
    | 1230 | December, 09 2010 00:00:00 | January, 06 2011 00:00:00 | 3526 | 125 |
    | 1230 | December, 10 2010 00:00:00 | January, 07 2011 00:00:00 | 4067 | 145 |
    | 1230 | December, 11 2010 00:00:00 | January, 08 2011 00:00:00 | 4916 | 175 |
    | 1230 | December, 12 2010 00:00:00 | January, 09 2011 00:00:00 | 5548 | 198 |
    | 1230 | December, 13 2010 00:00:00 | January, 10 2011 00:00:00 | 6454 | 230 |
    | 1230 | December, 14 2010 00:00:00 | January, 11 2011 00:00:00 | 7415 | 264 |
    | 1230 | December, 15 2010 00:00:00 | January, 12 2011 00:00:00 | 7776 | 277 |
    | 1230 | December, 04 2011 00:00:00 | January, 01 2012 00:00:00 |  461 |  16 |
    | 1230 | December, 05 2011 00:00:00 | January, 02 2012 00:00:00 | 1389 |  49 |
    | 1230 | December, 06 2011 00:00:00 | January, 03 2012 00:00:00 | 2244 |  80 |
    | 1230 | December, 07 2011 00:00:00 | January, 04 2012 00:00:00 | 2849 | 101 |
    | 1230 | December, 08 2011 00:00:00 | January, 05 2012 00:00:00 | 2932 | 104 |
    | 1230 | December, 09 2011 00:00:00 | January, 06 2012 00:00:00 | 2976 | 106 |
    | 1230 | December, 10 2011 00:00:00 | January, 07 2012 00:00:00 | 3358 | 119 |
    | 1230 | December, 11 2011 00:00:00 | January, 08 2012 00:00:00 | 4220 | 150 |
    | 1230 | December, 12 2011 00:00:00 | January, 09 2012 00:00:00 | 4769 | 170 |
    | 1230 | December, 13 2011 00:00:00 | January, 10 2012 00:00:00 | 5401 | 192 |
    | 1230 | December, 14 2011 00:00:00 | January, 11 2012 00:00:00 | 5403 | 192 |
    | 1230 | December, 15 2011 00:00:00 | January, 12 2012 00:00:00 | 5429 | 193 |
    

    NB: For SQL Server

    • instead of theDay - INTERVAL '28 DAYS' use dateadd(day,-28,theDay)

    Because there is no stated/shown “expected result” I am guessing quite a bit. While I have read the words of this question, and the duplicate, I really don’t understand the description. With these reservations I suggest the following:

    Result:

    | item | promo_start |    max_date |  sum | count | avg |
    |------|-------------|-------------|------|-------|-----|
    | 1230 | Jan 02 2011 | Jan 12 2011 | 7150 |    11 | 650 |
    | 1230 | Jan 02 2012 | Jan 12 2012 | 4968 |    11 | 451 |
    

    Produced by the following query (using Postgres):

    select
          item
        , promo_start
        , max(thedate) max_date
        , sum(value)
        , count(distinct thedate)
        , sum(value) / count(distinct thedate) avg
    from (
          select *
          from table1 t1
          cross join lateral (
                select min(t2.thedate) promo_start
                from table1 t2
                where promo = 1
                and t1.item = t2.item
                and t2.thedate <= t1.thedate
                and t2.thedate >= t1.thedate - INTERVAL '28 DAYS'
                ) a
          ) d
    where promo_start is NOT NULL
    group by
          item
        , promo_start
    ;
    

    See this as a demo at sqlfiddle (nb: SQL Server is/has not working at that site for several weeks so Postgres used in lieu)

    NB: For SQL Server

    1. instead of `cross join lateral’ use CROSS APPLY
    2. instead of t1.thedate - INTERVAL '28 DAYS' use dateadd(day,-28,t1.thedate)

    The logic is as follows:

    1. the cross join lateral (cross apply) sub-query locates the minimum date in which an item has been on promotion in the prior 28 days
    2. that minimum date is repeated on each row for a promotion affected period
    3. only rows that have been affected by a promotion are then selected
    4. then the item and promotion start date are used for grouping the selected rows, with aggregates used to calculate average
    5. the number of distinct days is used in calculation of the average as sales may not occur on each day within the 28 day period.

    + I used “theDate” as the column name simply because “date” is too confusing in both code and describing code. I really cannot recommend using “date” as a column name.

    if you are looking for cumulative sum for all days excluding first 12 days from mindate…

    with cte
    as
    (
    select dateadd(day,12,min(date)) as mindate,max(date) as maxdate,
    datediff(day,dateadd(day,12,min(date)),max(date)) as n
    
    from #RollingTotalsExample
    )
    select 
    date,
    (select sum(value) from #RollingTotalsExample t2 where t2.date<=t1.date) as rollingsum,
    promo,
    item,
    mindate,
    maxdate
    from 
    #RollingTotalsExample t1
    join
    cte c
    on  t1.date >=c.mindate and t1.date<= c.maxdate
    order by date
    

    –this matches your exact output

    with cte
    as
    (
    select dateadd(day,12,min(date)) as mindate,max(date) as maxdate,
    datediff(day,dateadd(day,12,min(date)),max(date)) as n
    
    from #RollingTotalsExample
    )
    select 
    date,
    sum(value) as avgsum,
    promo,
    item
    from 
    #RollingTotalsExample t1
    join
    cte c
    on  t1.date >=c.mindate and t1.date<= c.maxdate
    where promo=1
    group by date,promo,item
    order by date
    

    —if you are looking for avg: sum/no of days

    with cte
    as
    (
    select dateadd(day,12,min(date)) as mindate,max(date) as maxdate,
    datediff(day,dateadd(day,12,min(date)),max(date)) as n
    
    from #RollingTotalsExample
    )
    select 
    date,
    sum(value)/n*1.0 as avgsum,
    promo,
    item
    from 
    #RollingTotalsExample t1
    join
    cte c
    on  t1.date >=c.mindate and t1.date<= c.maxdate
    where promo=1
    group by date,promo,item,n
    order by date
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.