Select rolling math problem 1

[ZZaletel]

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?

 

[PHV]

You can try this:
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, (SELECT Avg(s2.Wheat) FROM Process AS s2
WHERE s2.date<=t1.date AND s2.date>=(t1.date-29)) AS AvgMillTot
FROM Process AS t1
ORDER BY t1.Date;
Or this:
SELECT t1.Date AS EDate, t1.Wheat AS Mill,
Sum(s1.Wheat) AS MillTot, Avg(s1.Wheat) AS AvgMillTot
FROM Process AS t1 INNER JOIN Process AS s1
ON t1.Date BETWEEN s1.Date And s1.Date + 29
GROUP BY t1.Date, t1.Wheat
ORDER BY t1.Date;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZZaletel]

PH -

The first proposed solution breaks down in a fashion similar to the previous calculation method after about six months of data.

The second proposed solution gives me an alert:
"Between operator without And in query expression 't1.Date BETWEEN s1.date'"

 

[PHV]

Try to replace this:
ON t1.Date BETWEEN s1.Date And s1.Date + 29
By this:
ON t1.Date >= s1.Date And t1.Date <=( s1.Date + 29)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZZaletel]

PH,

The second solution now works - and is an improvement, though still spits out incorrect numbers after about 2 years of correct answers. I've checked the table the data is being drawn from and don't see any apparent problems.

Barring another solution, perhaps I just need to restrict the query (or final report, more appropriately) to only a year's worth of user-choosable data.

Thanks for your help. Let me know if anything else occurs to you, or else I'll follow up with an eventual answer here.

Cheers,
Zach