I'm having a problem with a database I'm using to track production numbers. I've come up with SQL code which performs the calculations, however, a spot check reveals that the code works properly for a time, and then starts returning values that are incorrect. Thus far, I've been unable to discern any rhyme or reason as to the source of my problem.
I'm trying to calculate a rolling, 30-day total of material processed. Here is what I've come up with:
SELECT t1.Date AS EDate,
t1.Wheat AS Mill,
(SELECT Sum(s1.Wheat)
FROM Process AS s1
WHERE s1.date<=t1.date AND s1.date>=(t1.date-29)) AS MillTot, MillTot/30 AS AvgMillTot
FROM Process AS t1
GROUP BY t1.date, t1.Wheat
ORDER BY t1.Date;
Any thoughts or more info needed that I'm forgetting to give?
I'm trying to calculate a rolling, 30-day total of material processed. Here is what I've come up with:
SELECT t1.Date AS EDate,
t1.Wheat AS Mill,
(SELECT Sum(s1.Wheat)
FROM Process AS s1
WHERE s1.date<=t1.date AND s1.date>=(t1.date-29)) AS MillTot, MillTot/30 AS AvgMillTot
FROM Process AS t1
GROUP BY t1.date, t1.Wheat
ORDER BY t1.Date;
Any thoughts or more info needed that I'm forgetting to give?
