dimanche 19 avril 2015

SQLite group by range of numbers with an arbitrary starting number and fixed interval

I have an SQLite table containing daily information. It's stored as year and day of year.


What I'm wondering is if there's a way within the query to group by week (which can start on any day) and sum a couple fields, or if I would have to resort to reading it and doing it programmatically.


the schema (a little outdated) is:



_id INTEGER PRIMARY KEY AUTOINCREMENT,hYear INTEGER,hDay INTEGER,hTime INTEGER,hMinutes INTEGER,lunch INTEGER,payWeek INTEGER,yearWeek INTEGER


and some sample data:



17|2015|96|27000000|630|30|13|15
18|2015|97|27000000|240|0|13|15
19|2015|98|27000000|510|30|13|15
20|2015|99|27000000|300|30|13|15
21|2015|101|26100000|645|30|13|15
31|2015|103|26100000|645|30|13|16
32|2015|104|27000000|555|30|14|16
33|2015|105|27000000|570|30|14|16
34|2015|106|27000000|420|30|14|16
35|2015|107|26100000|630|30|14|16
36|2015|108|26100000|495|30|14|16


What I want to be able to do is start the week on whatever day of the week, and add sum up two of the columns for 7 days and pass that to a CursorAdapter.


Aucun commentaire:

Enregistrer un commentaire