SQL BAQ help, rolling average

,

Hi,

I would like to learn how to create a rolling averages within BAQ. This would be a very helpful in many kinds of business analysis that we are trying to do. I’ve been googling the SQL language to try to figure out how to get it to work within a BAQ, but I don’t where to put this in the BAQ or what needs to be adjusted for translation.

This is the site that I was using to try to figure it out.

They have listed a couple different SQL examples, which I listed below. Forgive my ignorance about SQL, I don’t know what type of SQL E-10 is using. I’m assuming it’s server because I’ve used the dateadd function as it’s show in that example.

My SQL
select signups.date, signups.count, avg(signups_past.count)
from signups
join signups as signups_past
on signups_past.date between signups.date - 6 and signups.date
group by 1, 2

or

Server SQL

select signups.date, signups.count, avg(signups_past.count)
from signups
join signups as signups_past
on signups_past.date
between dateadd(day, -6, signups.date) and signups.date
group by signups.date, signups.count

In previous attempts and the select statements, I ended up just created sub queries and used the filtering in those to get the different things I needed, but for a rolling average, I would need a subquery for each date. I could create a sub query to do something like past 7 days, 30 days 90 days. But that doesn’t get me something that I can graph out so that someone could start looking at trends, only current states.

Here’s what I have, but it won’t let me run it because it says possible unauthorized query. I tried putting the group by information in the advanced group by section of the display fields, but I’m sure I’m doing something wrong.

select
[dailytotal].[ShipHead_ShipDate] as [ShipHead_ShipDate],
(avg( dailytotal.Calculated_RevDay )) as [Calculated_Ave]
from (select
[ShipHead].[ShipDate] as [ShipHead_ShipDate],
(sum( OrderDtl.ExtPriceDtl )) as [Calculated_RevDay]
from Erp.OrderDtl as OrderDtl
cross join Erp.ShipHead as ShipHead
inner join Erp.ShipDtl as ShipDtl on
OrderDtl.Company = ShipDtl.Company
And
OrderDtl.OrderNum = ShipDtl.OrderNum
And
OrderDtl.OrderLine = ShipDtl.OrderLine

inner join Erp.ShipDtl as ShipDtl and
ShipHead.Company = ShipDtl.Company
And
ShipHead.PackNum = ShipDtl.PackNum

group by [ShipHead].[ShipDate]) as dailytotal
group by select dailytotal.ShipHead_ShipDate, dailytotal.Calculated_RevDay , avg(dailytotal_past.Calculated_RevDay)
from dailytotal
join dailytotal as dailytotal_past
on dailytotal.ShipHead_ShipDate
between dateadd(day, -6,dailytotal.ShipHead_ShipDate) and dailytotal.ShipHead_ShipDate
Group by dailytotal.ShipHead_ShipDate

Again, sorry for my ignorance in the topic, and thanks for any help.

Brandon

Do you have query that works in SQL management studio?

This part is not possible in t-sql: group by select …

No I do not. I don’t know how to use SQL on the back end. I’ve been learning everything in the BAQ and Dasboards within Epicor designer, which has led to some SQL knowledge, but not to where I can create my own code or anything. The code above is copy/pasted from the the query phrase pane in the BAQ designer. I was hoping that it was something that I could get to within that tool.

It seems the link you gave is not good for Ms SQL server
As far as I understand the case, you need to create CTE subquery and reference it from main subquery,
Here is an example for t-sql

what SQL Server version you use?

I think we are on SQL 2012, my IT guy has been talking about the upgrade to 2016.

The CTE query makes sense that it could loop through and each pass could do the averaging based on the date which would incremented every time it loops through. However, I still wouldn’t be able to grab groups of records to average them within the calculated field, but maybe I could reference the incremented date in the filter box below.

I’ll play around with that and see if I can get it to work.

I’m guessing though that the performance for a query like that would be horrible though?

Thanks for the suggestions.

create view RollingAverage
as
select sh1.shipDate, isnull(avg(dailytoal.amt),0)
from erp.shiphead sh1
join (select sh.ShipDate, sum( OrderDtl.ExtPriceDtl ) ‘amt’
from erp.OrderDtl od
join erp.shipdtl sd on sd.company = od.company and sd.ordernum =
od.ordernum and sd.orderline = od.orderline
join erp.shiphead sh on sh.company = sd.company and sh.packnum =
sd.packnum
group by sh.ShipDate ) ‘dailyTotal’
where dailyTotal.Shipdate between dateadd(d, -6, sh1.shipDate ) and
sh1.shipDate
group by sh1.ShipDate
order by sh1.ShipDate desc
This should work for you fo dates when you shipped products.
If you need it for every day, you need to create a table and populate it
with all the dates.

To finish, you can create and external BAQ pointing to the view.