Getting min value over a period of time for each record

Question

I have a table which lists the Departments and total count of incidents for each calendar month, as seen below. Data is ordered by Dept and Date fields.

What I am trying to do is to get the minimum value of the 'Count' field for the past 3 months, for each company and display it for each row in the original table.

For instance, the first record has Count=2 and there is no data prior to 2011-1-1 for Dept-001, so the min(Count) is 2. The second record has Count=9 and the min(Count) for the past 3 months is 2. and so forth...

How would you write a query to give this output? Thanks,

TABLE

Dept Count    Date
001  2        2011-1-1
001  9        2011-2-1
001  1        2011-3-1
001  4        2011-4-1
001  5        2011-5-1
001  3        2011-6-1
002  2        2011-1-1
002  7        2011-2-1
002  1        2011-3-1
002  9        2011-4-1
002  6        2011-5-1
002  1        2011-6-1

OUTPUT

Dept Min(Last 3 Month)    Date
001  2                    2011-1-1
001  2                    2011-2-1
001  1                    2011-3-1
001  1                    2011-4-1
001  1                    2011-5-1
001  3                    2011-6-1
002  2                    2011-1-1
002  2                    2011-2-1
002  1                    2011-3-1
002  1                    2011-4-1
002  1                    2011-5-1
002  1                    2011-6-1

Does this help? http://stackoverflow.com/questions/5662545/how-do-i-group-on-continuous-ranges — Kyle B., Dec 10 '13 at 19:44

score 2 · Accepted Answer · answered Dec 10 '13 at 20:22

You need to self-join the table with the period:

SELECT t1.dept, MIN(t2.cnt) min_last_3_month, t1.dat FROM
  tbl t1
INNER JOIN tbl t2 ON t1.dept = t2.dept
                 AND t2.dat > DATEADD(MONTH, -3, t1.dat)
                 AND t2.dat <= t1.dat
GROUP BY
  t1.dept, t1.dat
ORDER BY
  t1.dept, t1.dat

you can try it on SQLFiddle

score 0 · Answer 2 · answered Dec 10 '13 at 22:03

SELECT  x.[Dept], x.[Date], x.[Count],
        (
            SELECT  MIN(y.[Count]) FROM dbo.MyTable y 
            WHERE   y.[Dept] = x.[Dept]
            AND     y.[Date] BETWEEN DATEADD(DAY, 1, DATEADD(MONTH, -3, x.[Date])) AND  x.[Date] -- Assumption: [Date]'s type is DATE
        ) AS MIN_Count
FROM    dbo.MyTable x;

Getting min value over a period of time for each record

2 Answers2