CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Karen Schaefer
Karen Schaefer

Reputation: 109

7 day average in SQL Server 2014

I need to modify the following T-SQL statement to include a rolling 7 day average of the revenue.

What do I need to include in the following code to achieve that?

SELECT 
    CAST(create_dtg AS DATE) DATE, 
    SUM([agent_rev] + [anchor_rev] + [corp_rev] + [offsite_rev]) AS RevenueTotals, 
    SUM([media_est_cost] + [other_cost]) AS COSTTOTALS 
FROM   
    [dbo].[dw_rpt_traffic] 
WHERE  
    [create_dtg] >= ( Getdate() - 90 ) 
--GROUP BY CREATE_DTG 
--ORDER BY CREATE_DTG ASC

I also tried using Parttion by, however, this returns the same value as the Revenuetotals.

Select a.dte, a.revenuetotals, a.COSTTOTALS, AVG(A.RevenueTotals) OVER     (PARTITION BY a.dte ORDER BY a.dte ROWS 7 PRECEDING) as Day7Avg
from
(
   select CAST(CREATE_DTG AS DATE) as dte, 
          SUM([AGENT_REV]+[ANCHOR_REV]+[CORP_REV]+[OFFSITE_REV]) as RevenueTotals,
          SUM([MEDIA_EST_COST]+[OTHER_COST]) as COSTTOTALS
    FROM [dbo].[dw_rpt_traffic]
    where [CREATE_DTG] >= (GetDate() - 90)
    GROUP BY CREATE_DTG
    ) as A
 Group by a.dte, a.revenuetotals, a.COSTTOTALS
 order by a.dte

Thanks, Karen

Upvotes: 0

Views: 85

Answers (2)

nrussell
nrussell

Reputation: 18602

For rolling aggregates I typically use an OVER clause with ROWS [...] PRECEDING [...]. 

WITH    cte
          AS ( SELECT   x.Date
                       ,x.Revenue
                       ,AVG(x.Revenue) OVER ( ORDER BY x.Date 
                                              ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 
                        ) AS [MA7]
               FROM     ( SELECT    CAST(t.Date AS DATE) AS [Date]
                                   ,SUM(t.Revenue) AS [Revenue]
                          FROM      #tmp t
                          WHERE     CAST(t.Date AS DATE) > CAST(GETDATE() - 96 AS DATE)
                          GROUP BY  CAST(t.Date AS DATE)
                        ) x
             )
    SELECT  c.Date
           ,c.Revenue
           ,c.MA7
    FROM    cte c
    WHERE   c.Date > CAST(GETDATE() - 90 AS DATE)
    ORDER BY c.Date;

The table above was generated with the following: 

IF ( OBJECT_ID('tempdb..#tmp') IS NOT NULL )
    DROP TABLE #tmp;
CREATE TABLE #tmp
    (
     [Date] DATETIME
    ,[Revenue] DECIMAL(18, 2)
    );
--
DECLARE @first INT = 0
   ,@last INT = 200;
WHILE @first < @last
    BEGIN
        INSERT  INTO #tmp
                ( Date, Revenue )
        VALUES  ( GETDATE() - @first * 0.5, RAND() * 100000 );

        SET @first = @first + 1;
    END;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Probably the easiest way uses outer apply:

with rt as (
       select CAST(CREATE_DTG AS DATE) as dte, 
              SUM([AGENT_REV]+[ANCHOR_REV]+[CORP_REV]+[OFFSITE_REV]) as RevenueTotals,
              SUM([MEDIA_EST_COST]+[OTHER_COST]) as COSTTOTALS
       from [dbo].[dw_rpt_traffic]
       where [CREATE_DTG] >= (GetDate() - 90)
      )
select rt.*, rolling.avgrt
from rt outer apply
     (select avg(rt2.RevenueTotals) as avgrt
      from rt rt2
      where rt2.dte >= dateadd(day, -6, rt.dte) and
            rt2.dte <= rt.dte
     ) rolling
order by dte;

Upvotes: 1

Related Questions