How can I calc weekly totals?
(1) By David Jackson (davidjackson) on 2020-12-04 02:27:29 [source]
My covid table contains one row for each day, Each row contains looks like this: date,cases, deaths. I want to calc the weekly totals for cases and deaths. The query result would have the Sunday YMD, TotalCases and TotaldDeaths. TIA, David
(2) By Keith Medcalf (kmedcalf) on 2020-12-04 13:30:04 in reply to 1 [link] [source]
How about something like:
select enddate,
sum(...),
...
from (
select date(cast(julianday(datecolumn)-1721061.5 as integer)/7*7+1721067.5) as enddate,
*
from theTable
)
group by enddate
order by enddate;
(3.1) Originally by Ryan Smith (cuz) with edits by Richard Hipp (drh) on 2020-12-04 15:48:01 from 3.0 in reply to 1 [link] [source]
Spam post deleted
(4) By David Jackson (davidjackson) on 2020-12-04 18:27:31 in reply to 2 [link] [source]
Thanks, I give it a try. David
(5) By David Jackson (davidjackson) on 2020-12-04 22:00:13 in reply to 2 [link] [source]
Keith, Thanks that was exactly what I was looking for.
(6) By Keith Medcalf (kmedcalf) on 2020-12-05 00:43:39 in reply to 2 [link] [source]
The constant 1721061.5 is the JulianDay of '0000-01-03' which is the first Monday in the "round trippable" dates supported by the SQLite3 datetime functions.
cast(julianday(datecolumn)-1721061.5 as integer)/7
gives us the Week Number (as an integer) based on a week starting on a Monday (base 0).
You then multiple this by 7 to convert back to days, and add 1721067.5 (which is the JulianDay value corresponding to '0000-01-09'), which is the JulianDay value on which the first week ends, and convert the result back to a Gregorian date.
Thus given any Gregorian date in the Common Era you can now compute the Week Ending date, for a week spanning Monday -> Sunday. You could (for example) return the "starting date" of each week by changing the last constant to 1721061.5.