c++ - SQLite - get last day of the week available when there are missing fridays -


in this question trying filter database day of week , found solution. however, when there gaps in data, in order @ least 1 registry per week, need registry of last day available in week.

as example statement new problem returns:

select strftime('%w', date), date, stock sales id=123 , cast (strftime('%w', date) integer) = 5; 

since run statement through c++ api appreciate solution sticks 1 statement when possible.

note: if run statement above through c++ api please note when preparing statement variable '%w' in strftime should "%w" otherwise not work (it took me while until found problem).

edit: these example records:

daily data (week index|date|record):

09|2016-03-01|1219.0 09|2016-03-02|1255.0 09|2016-03-03|1298.0 09|2016-03-04|1309.0 10|2016-03-07|1344.0 10|2016-03-08|1305.0 10|2016-03-09|1304.0 10|2016-03-10|1258.0 10|2016-03-11|1298.0 11|2016-03-14|1304.0 11|2016-03-15|1274.0 11|2016-03-16|1283.0 11|2016-03-17|1315.0 11|2016-03-18|1296.0 12|2016-03-21|1286.0 12|2016-03-22|1280.0 12|2016-03-23|1246.0 12|2016-03-24|1223.0 13|2016-03-29|1214.0 13|2016-03-30|1239.0 13|2016-03-31|1220.0 13|2016-04-01|1183.0 

weekly (from query above):

09|2016-03-04|1309.0 10|2016-03-11|1298.0 11|2016-03-18|1296.0 13|2016-04-01|1183.0 

i following:

09|2016-03-04|1309.0 10|2016-03-11|1298.0 11|2016-03-18|1296.0 12|2016-03-24|1223.0 13|2016-04-01|1183.0 

just use record largest date each week index:

select week_index,        max(date),        value mytable group week_index; 

Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -