There is a database of incoming telephone numbers:
ID (unique) | Date | Week | Department | Phone number
Example lines:
('201604231905293465628284551', '2016-04-23', 16, 'zch', 89110145664)
I need to make a graph on the amount of unique numbers for 50 weeks (by weeks). To do this, i'm doing this request:
c.execute("SELECT count(DISTINCT num) FROM calls WHERE department == (?) AND datetime LIKE '{}%' AND week == (?)".format(year), (dept, week))
Because the database has data for several years, so it is necessary to specify the number of the week and year. column data is a text, because it shows a date of a call, but not a date of adding to the database.
The problem occurs in the last week of the year, because this week may be the part of two years (for example 53 week of 2015 and 2016). With such request i have 51 values instead of 50 (last week is displayed twice with a value of first and second years). To plot I need to sum two values of this week to one value.
How is it better to do? Is it possible to do it in SQL - query?
Aucun commentaire:
Enregistrer un commentaire