0

I'm trying to have average during a period (for example : last 6 months, last month, last week, ...).

For that I created 4 queries. Can I do something clearly? 

/** ON SELECTED DATE*/
SELECT AVG(RUN_TIME)
FROM (SELECT (DATEDIFF(minute,MIN(CAST(START_DATE AS DATETIME)), MAX(CAST(END_DATE AS DATETIME)))) as RUN_TIME FROM TableTest where DATE_EXPECTED = DATE_SELECTED ) f

/**ON 6 last month*/
SELECT AVG(RUN_TIME)
FROM (SELECT (DATEDIFF(minute,MIN(CAST(START_DATE AS DATETIME)), MAX(CAST(END_DATE AS DATETIME)))) as RUN_TIME  FROM TableTest 
where Month(CAST(DATE_SELECTED AS DATETIME)) BETWEEN Month(CAST(DATE_SELECTED AS DATETIME) ) AND Month(CAST(DATE_SELECTED AS DATETIME) )+6 ) f

/** on 30 last days */
SELECT AVG(RUN_TIME)
FROM (SELECT (DATEDIFF(minute,MIN(CAST(START_DATE AS DATETIME)), MAX(CAST(END_DATE AS DATETIME)))) as RUN_TIME  FROM TableTest where DATE_EXPECTED between DATE_SELECTED and DATE_SELECTED+30) f

/* on 5 last days */
SELECT AVG(RUN_TIME)
FROM (SELECT (DATEDIFF(minute,MIN(CAST(START_DATE AS DATETIME)), MAX(CAST(END_DATE AS DATETIME)))) as RUN_TIME  FROM TableTest where DATE_EXPECTED between DATE_SELECTED and DATE_SELECTED+5 ) f

It's working, but I would like to know if I can do this thing in a best way.

Thanks,

Bob
  • 529
  • 1
  • 7
  • 28
  • Yes, I would like an average. I'm still learning so : I shouldn't use AVG for average? – Bob Mar 21 '17 at 16:49
  • You should take a look at this article about using shortcuts with dates. https://sqlblog.org/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations – Sean Lange Mar 21 '17 at 16:51

1 Answers1

2

You can use conditional aggregation:

SELECT AVG(CASE WHEN DATE_EXPECTED = DATE_SELECTED THEN RUN_TIME END) as avg_1,
       AVG(CASE WHEN Month(CAST(DATE_SELECTED AS DATETIME)) BETWEEN Month(CAST(DATE_SELECTED AS DATETIME) ) AND Month(CAST(DATE_SELECTED AS DATETIME) )+6 THEN RUN_TIME END) as avg_2,
       AVG(CASE WHEN DATE_EXPECTED between DATE_SELECTED and DATE_SELECTED+30 THEN RUN_TIME END) as avg_3,
       AVG(CASE WHEN DATE_EXPECTED between DATE_SELECTED and DATE_SELECTED+5 THEN RUN_TIME END) as avg_4
FROM (SELECT (DATEDIFF(minute,MIN(CAST(START_DATE AS DATETIME)), MAX(CAST(END_DATE AS DATETIME)))) as RUN_TIME
      FROM TableTest
     ) f;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I don't have the same result as my different queries. I've the same result for all the average (and it shouldn't) – Bob Mar 22 '17 at 09:32
  • @FannyV . . . The data would have to be very particular for these four `avg()` calls to return the same value. – Gordon Linoff Mar 22 '17 at 12:12