# Help with querying moving averages of a few types including fiscal year moving averages **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [March 1, 2023, 2:25pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475 "2023-03-01T14:25:27Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [March 1, 2023, 2:25pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/1 "2023-03-01T14:25:27Z")
I am interested in general tips around moving averages and techniques to display them. **First question - general technique to display moving averages:** I expect to bind a query beyond the range desired to display. I expect to use a script to trim the dataset. This is typical? If showing 60 days with a 200ma, I get 260 days of data and trim the first 200? Or is it better to do a union, and self contain the query? **Second question** If I am displaying the last 60 days as stacked bars in a chart, what is the way to address days where I have no data without disturbing my average? Can I do it in the query in a good way? **Third question** Handling overlaps, like I have a moving average from the beginning of the fiscal year, how do I address the display of time over the fiscal start date? Just let the group by function address it? So my subquery groups days, then I have an outer query that groups on my fiscal year? **Fourth question** Is this good technique? I am not sure how I can filter days in an over() function either. Use a script to fill in the 0 dates afterwards? (0 dates, like Sundays or holidays where I have no data) Or there is a way to union 0 dates in the query? ```auto avg(Price) OVER(ORDER BY Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) as moving_average ``` I am pretty sure that I can accomplish this task, but if I can get some tips from experience, it is appreciated. Thank you in advance. ---
**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\ **Post date:** [March 2, 2023, 3:48am UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/2 "2023-03-02T03:48:49Z")
> [@zacharyw.larson](#): > > If showing 60 days with a 200ma, I get 260 days of data and trim the first 200? Why are the additional 200 days needed? Can't the calculations be done in the query, so only what is needed is returned? > [@zacharyw.larson](#): > > what is the way to address days where I have no data without disturbing my average The days with no data could be filtered out within the query itself > [@zacharyw.larson](#): > > Third question [...] Fourth question Can you provide some pseudo sample of the data you will be pulling from along with an expected result? I imagine that would help a bunch with getting a satisfactory answer to the third and fourth questions. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [March 2, 2023, 1:16pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/3 "2023-03-02T13:16:52Z")
I am still looking at techniques and collecting experience information. I thought because stock market uses so many of these charts, that it might be likely someone with experience from that would post saying what worked best typically. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 2, 2023, 1:29pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/4 "2023-03-02T13:29:32Z")
Reply to edit #2: 1. Yes, trim. No reason not to trim with an outer query. Maybe with a second outer query to return the order to ascending. ```auto Select Top 60 ..... Order by t_stamp Desc ``` 1. Use a table-generating function that yields all possible dates in your analysis range. Outer join to your daily-grouped data subquery to deliver zeros for those days if appropriate, or nulls for those days (which will make them count for the row offsets in your window function range, but will not affect the result). 2. Do all of your calculating and grouping with actual datetime types. Conversion to displayable strings is a task for the UI. Do not format dates in SQL. 3. Yes, window functions with explicit row ranges are your friend. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [March 3, 2023, 3:55pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/5 "2023-03-03T15:55:01Z")
One important note I discovered: ```auto avg(case when Price = 0 then null else Price end) OVER(ORDER BY Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) as moving_average ``` @pturmel They asked for the most recent date to be on the right side of my graph, so the bottom row. So I order by a date ascending. How do I trim for the bottom 60 instead of the top 60? I attempted to use limit 60 offset 200, but it did not work. Limit doesn't seem to be a keyword. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [March 3, 2023, 4:21pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/6 "2023-03-03T16:21:01Z")
Oh I can use rowNumber and where cause. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 3, 2023, 4:23pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/7 "2023-03-03T16:23:17Z")
> [@zacharyw.larson](#): > > use rowNumber No, don't. Wrap with a second outer query to order by t\_stamp. That's why I included that statement in the answer I gave. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [March 3, 2023, 4:26pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/8 "2023-03-03T16:26:44Z")
I can have two orders? For my graph, I need `order by t_stamp asc` effectively. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 3, 2023, 4:27pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/9 "2023-03-03T16:27:48Z")
Yes, but only because the one on the inner subquery is associated with a TOP clause. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [March 3, 2023, 7:22pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/10 "2023-03-03T19:22:18Z")
Thanks very much * * * I am trying to use a variable range now. I am getting an error when I try to set the rows preceding as a variable. Error syntax near t1 `avg(case when OEE=0 then null else OEE end) OVER(ORDER BY Start asc ROWS BETWEEN t1.test PRECEDING AND CURRENT ROW )` If I try setting a `datediff()` in there, I also get an error incorrect syntax near 'datediff'. `avg(case when OEE=0 then null else OEE end) OVER(ORDER BY Start asc ROWS BETWEEN (153-1) PRECEDING AND CURRENT ROW )` This one gives me an error like " Incorrect syntax near '('." I tried declaring an int as the value. Getting an error of incorrect syntax near `@number` * * * I can kind of fudge it I think. I can kind of set the window function to shorter than my preceding rows. Then I set my total range to be shorter in days than my window. I think it works. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 3, 2023, 9:19pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/11 "2023-03-03T21:19:30Z")
> [@zacharyw.larson](#): > > I am trying to use a variable range now. Not sure that is supported by the `ROWS BETWEEN` clause. Why do you think you need to do this? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [March 6, 2023, 1:16pm UTC](https://forum.inductiveautomation.com/t/help-with-querying-moving-averages-of-a-few-types-including-fiscal-year-moving-averages/70475/12 "2023-03-06T13:16:19Z")
I wanted to do a 50 day moving average, 200 day moving average, year to date moving average, and a fiscal year to date moving average. Right now, I can only really do the fiscal moving average because in order to dynamically adjust the range, I used a where clause. If I could use a variable in the number of preceding rows, it would be pretty easy. Thought maybe on the case side, I could adjust for the date range. ```auto avg(case when OEE=0 or datediff(day,start,getdate())> datediff(day,DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0),getdate()) then null else OEE end) OVER(ORDER BY Start asc ROWS BETWEEN (364) PRECEDING AND CURRENT ROW ) ```