SQL Puzzle :)

1

Given a Table with the following Schema

itemDATE - Text (stored in SQLDATE format)
itemVALUE - Double

return the AVERAGE of itemVALUE for the 90 days previous to and including itemDATE

the 90days is relative to itemDATE, not to “today”

So the returned table would have

itemDATE
itemVALUE
item90DayAvg

2

Assuming this is in SQLite, something like the following (not tested):

select itemDate, itemValue, (select Avg(itemValue) from tblname where itemDate between Date(itemDate, '-90 Days') and Date(itemDate, '1 day')) as item90DayAvg from tblname

Edit: need to add 1 day to the second itemDate in the between clause to include all rows with that date. Corrected above. Note that the Date function only returns the date portion, even if the source date was actually a datetime value. So no need to worry about the time part causing an issue. There is an identical DateTime function which does include the time.

3

well dang… I thought the fact that the date was a TEXT field would mess things up… but I guess that is not the case… sweet… .thanks

and yes… its SQLite (under Swift on an iPhone) :slight_smile:

4

Uhmmm something is wrong here… :frowning:
my table has 40 entries… and it returned the EXACT same answer for every row

the AVG for the first row should equal the 1st row, the 2nd should be the sum of the 1st two rows divided by 2, and for the data I currently have the last row should be the sum of all 40 rows / 40… once I have more than 90 days… it should be the previous 90 rows / 90

What this query is giving me is the current table average
the same as if I had said

SELECT avg(itemValue) from Table

5

here is the correct query

select date_time, glucose, 
(select Avg(glucose) 
   from glucose b
  where b.date_time between Date(a.date_time, '-90 Days') 
    and Date(a.date_time, '1 day')) as item90DayAvg 
    from glucose a

Thanks

2 Likes
6

Ah yes, forgot to pin the date to the outer query (as I said, not tested).

Would have responded with the same correction, but I never got any notification of activity on this thread. :man_shrugging:

7

No worries… you pointed me in the right direction… Thanks!