Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 2 of 2 total |
Weekly average from within daily average |
Sun, Aug 24 2014 9:00 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Aug 24 2014 9:20 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |