0

I am trying to calculate a moving average in my sql script. It is a moving average because users supply a date range. For example,

Select a.hospital, avg(a.count) as 'Averages', a.date
from Records as a
group by a.hospital, a.date
having a.date >= @StartDate and a.date <=@EndDate

So in the SSRS graph, how do I create a straight line across bars (each bar represents the count) to simply show the average for all hospitals between the two dates?

Or in my query, how do I include the average calculation? I think if you just do avg(a.counts), then how will it be the average for date x and date y?

Say users want to see the average count for each hospital between 10/19/2015 to 11/30/2015. How will I get it to show just a straight line on the graph for avg between the two dates?

Thanks

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
JGF
  • 19
  • 3
  • 3
    Which DBMS are you using? –  Sep 22 '16 at 22:18
  • 2
    http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 –  Sep 22 '16 at 22:18
  • 2
    I think I am following but as a_horse_with_no_name is suggesting you should tag with database sql-server, mysql, etc answers could differ, also You are asking for our help so don't post an image of data and expect us to retype make it easy create DML statements or at least post in tabular format. Also include your desired results they really help us understand what you are saying and it can help make up for shortcomings in narratives! – Matt Sep 22 '16 at 22:27
  • Also so you are saying you only want the average for that date range but still want all of the results? Interestingly per hospital all but one in your example data would simply be its own count......so I would suggest make sure you include test cases that will cover your issue! – Matt Sep 22 '16 at 22:29
  • I have made changes to my enquiry. Is it still too confusing? Sorry my 1st time to post. – JGF Sep 22 '16 at 23:01
  • I'm using sql server 2012 – JGF Sep 22 '16 at 23:04
  • just remove date field from select and group by. – Kostya Sep 22 '16 at 23:30

1 Answers1

0

If you wanted to get the average count against the hospital in a date range , change your script like below.

    Select a.hospital, avg(a.count) as 'Averages'
    from Records as a
    Where a.date >= @StartDate and a.date <=@EndDate
    group by a.hospital
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
  • I still want the graph to show the date as columns across the x-axis on the graph though. Without date in the select, how do I show it? – JGF Oct 12 '16 at 15:42