Creating averages in a sql statement

1

I have the following sql producing a list of records:

Select Convert(date,Oppo_Opened) as Date, count(distinct o.Oppo_OpportunityId) as Enquiries , Max(data.[3Hour]) as [3Hour], Max(data1.[24Hour]) as [24Hour]

From Opportunity o Inner Join Company On Comp_CompanyId = o.Oppo_PrimaryCompanyId
Left Outer Join
(Select distinct Convert(date,o.Oppo_Opened) as Opened, --Convert(date,op.Oppo_CreatedDate) as Created,
Count(distinct op.Oppo_OpportunityId) as [3Hour]
From OpportunityProgress op Inner Join Opportunity o On o.Oppo_OpportunityId = op.Oppo_OpportunityId
Where o.Oppo_Deleted is Null and o.Oppo_ChannelId=7 and Convert(date,op.Oppo_CreatedDate) > DateAdd(Day, -60, Getdate()) and op.Oppo_Stage In ('IQ','HQ','3Q')
Group By Convert(date,o.Oppo_Opened) --, Convert(date,op.Oppo_CreatedDate)
--Order By Convert(date,o.Oppo_Opened) desc
) data On data.Opened=Convert(date,Oppo_Opened) --and data.Created=Convert(date,Oppo_Opened)

  Left Outer Join
 (Select distinct Convert(date,o.Oppo_Opened) as Opened, --Convert(date,op.Oppo_CreatedDate) as Created,
       Count(distinct op.Oppo_OpportunityId) as [24Hour]
  From OpportunityProgress op Inner Join Opportunity o On o.Oppo_OpportunityId = op.Oppo_OpportunityId
  Where o.Oppo_Deleted is Null and o.Oppo_ChannelId=7 and Convert(date,op.Oppo_CreatedDate) > DateAdd(Day, -60, Getdate()) and op.Oppo_Stage In ('SDQ','24Q')
  Group By Convert(date,o.Oppo_Opened) --, Convert(date,op.Oppo_CreatedDate)
  --Order By Convert(date,o.Oppo_Opened) desc
  ) data1 On data1.Opened=Convert(date,Oppo_Opened) --and data1.Created=Convert(date,Oppo_Opened)

Where Oppo_Deleted Is Null and Convert(date,Oppo_Opened) > DateAdd(Day, -60, Getdate()) and Comp_sector In ('b','p')
Group By Convert(date,Oppo_Opened)--, data.[3Hour], data1.[24Hour]

I need to add 3 more columns that will hold 5 day averages, I have shown what I am trying to achieve below, is this possible ?

Excel 2010

A B C D E F G
1 5 Day Average
2 Date Enquiries 3Hour 24Hour Enquiries 3Hour 24Hour
3 03/05/2016 18 2 7 18 2 7
4 04/05/2016 17 4 7 35 6 14
5 05/05/2016 24 6 4 59 12 18
6 06/05/2016 18 4 4 77 16 22
7 09/05/2016 23 6 5 100 22 27
8 10/05/2016 32 5 14 114 25 34
9 11/05/2016 21 4 10 118 25 37
10 12/05/2016 25 1 11 119 20 44
11 13/05/2016 24 0 6 125 16 46
12 16/05/2016 18 7 7 120 17 48
13 17/05/2016 34 2 20 122 14 54

Sheet2



2

3

The screen shot in your second posting seems to indicate that you are trying to calculate running total rather than the average. If you are on SQL 2012 or later, you can use windowing functions like shown below

SELECT
	*,
	sum(Enquiries) over (order by Date rows between 4 preceding and current row) as [5DayAvgEnquiries],
	sum([3Hour]) over (order by Date rows between 4 preceding and current row) as [5DayAvg3Hour],
	sum([24Hour]) over (order by Date rows between 4 preceding and current row) as [5DayAvg24Hour]
from
(
	---- Your existing query here
) as S
1 Like
4

This alternative to @JamesK solution, will take date gaps into account:

with cte
  as (select opened
            ,dateadd(day,-4,opened) as opened5
            ,sum([3hour])+sum([24hour]) as enquiries
            ,sum([3hour]) as [3hour]
            ,sum([24hour]) as [24hour]
        from (select convert(date,o.oppo_opened) as opened
                    ,op.oppo_opportunityid
                    ,sign(sum(case when op.oppo_stage in ('IQ','HQ','3Q') then 1 else 0 end)) as [3hour]
                    ,sign(sum(case when op.oppo_stage in ('SDQ','24Q') then 1 else 0 end)) as [24hour]
                from opportunity as o
                     inner join company as c
                             on c.comp_companyid=o.oppo_primarycompanyid
                            and c.comp_sector in ('b','p')
                     inner join opportunityprogress as op
                             on op.oppo_opportunityid=o.oppo_opportunityid
                            and op.oppo_createddate>=dateadd(day,-59,getdate())
                            and op.oppo_stage in ('IQ','HQ','3Q','SDQ','24Q')
               where o.oppo_deleted is null
                 and o.oppo_channelid=7
               group by convert(date,o.oppo_opened)
                       ,op.oppo_opportunityid
             ) as a
       group by opened
     )
select a.opened
      ,a.enquiries
      ,a.[3hour]
      ,a.[24hour]
      ,sum(a.enquiries) as enquiries5
      ,sum(a.[3hour]) as [3hour5]
      ,sum(a.[24hour]) as [24hour5]
  from cte as a
       inner join cte as b
               on b.opened>=a.opened5
              and b.opened<=a.opened
 group by a.opened
         ,a.enquiries
         ,a.[3hour]
         ,a.[24hour]
 order a.opened
;