Find average of last three months

• vavfam

  SSC Veteran

  Points: 259

  More actions

  January 5, 2011 at 12:17 pm

  #230132

  I generate a report of our average sales by product group for the last 3 months. I have been using a view where I filter the results based on the last 90 days using the command Date>{fn NOW()(}-90.

  After noticing that the 3 month sales average was a moving target, the manager would like to change it.(This is I am sure very surprising to many of you)

  Here is what they want. They would like to have the average based on the three months prior to the current month. So in a perfect world, I would just determine the month and have the average based on <month(NOW())-4 and >month(NOW())-1. The hard part is in the first quarter where the months are 1,2,and 3. Can anyone help me with the best way to do this?

  I am running a stored procedure that calculates a ton of other things and could easily include it at that time or include it at the view level. The data field does have the year so if that needs to be included let me know.

  Thanks in advance.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 5, 2011 at 2:48 pm

  #1269771

  This is how I'd build something for that in T-SQL:

  -- Gets prior three months

  SELECT

  DATEADD(MONTH, -1, GETDATE()),

  DATEADD(MONTH, -2, GETDATE()),

  DATEADD(MONTH, -3, GETDATE()) ;

  -- First Day of Month

  SELECT

  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

  -- Combined query

  SELECT

  DATEADD(MONTH, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)),

  DATEADD(MONTH, -2, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)),

  DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) ;

  -- Date Range

  SELECT

  DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) as StartDate,

  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) as EndDate ;

  The first two queries are combined into the third query. The final query gets you a start and end date. I'm not sure which you're more comfortable with using, but my preference would be to build a Where clause into your query that uses greater or equal to the StartDate and less than the EndDate from the final query.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon