I'm working on my personal diabetic monitoring software. One of the reports I want to be able to generate is the daily and weekly average readings from my testing.

Coming up with the SQL for the daily averages is no problem, but adding the weeks average is beyond my skill. This is where I'm stalled.


SELECT
CAST(_Timestamp AS DATE) AS _Date,
AVG(_mmolPL) AS _Blood,
0 AS _HTest,
0 AS _Carbs, 0 AS _Energy,

IF(EXTRACT(DAYOFWEEK FROM Z._Timestamp) = 7
THEN
(
SELECT
AVG(_mmolPL)
FROM BloodTests W WHERE CAST(W._Timestamp AS DATE) BETWEEN CAST(Z._Timestamp AS DATE) AND CAST(Z._Timestamp AS DATE) - INTERVAL '7' DAY
GROUP BY CAST(Z._Timestamp AS DATE)
)
ELSE
null
)
AS _WeeksBlood

FROM BloodTests Z
WHERE
CAST(_Timestamp AS DATE) BETWEEN :Start AND :Finish
GROUP BY CAST(_Timestamp AS DATE)

It doesn't seem possible to pass the timestamp from the outset sql into the inner one.


I'll be very happy for someone to prove me wrong

Roy Lambert

AAAAAAAARGH


I was wrong - surprise eh!

Its the BETWEEN function. The parameters have to be

low value AND high value

otherwise it just doesn't work.

Suggestion coming on.


Roy Lambert