diff options
| author | zberwaldt <17715430+zberwaldt@users.noreply.github.com> | 2024-03-21 11:25:44 -0400 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2024-03-21 11:25:44 -0400 |
| commit | 7f9a8d74d7c0c66e7bf69892fbf893d45f1bfb82 (patch) | |
| tree | 3e2355a4ec3d07df8337bcaf1ab9e500a1827b67 /db | |
| parent | 8cdb9b7f15afc69fe903f2be566bbc8f3605e53f (diff) | |
| parent | e370616213d92b66685e0fdb0e76c97e08d0b1b6 (diff) | |
Staging
Diffstat (limited to 'db')
| -rw-r--r-- | db/sql/views.sql | 6 |
1 files changed, 3 insertions, 3 deletions
diff --git a/db/sql/views.sql b/db/sql/views.sql index c56286d..3ca2cf9 100644 --- a/db/sql/views.sql +++ b/db/sql/views.sql | |||
| @@ -4,7 +4,7 @@ CREATE VIEW IF NOT EXISTS aggregated_stats AS | |||
| 4 | CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS | 4 | CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS |
| 5 | SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color | 5 | SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color |
| 6 | FROM users | 6 | FROM users |
| 7 | LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day') | 7 | LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now') |
| 8 | LEFT JOIN preferences ON users.id = preferences.user_id | 8 | LEFT JOIN preferences ON users.id = preferences.user_id |
| 9 | GROUP BY users.name; | 9 | GROUP BY users.name; |
| 10 | 10 | ||
| @@ -12,11 +12,11 @@ GROUP BY users.name; | |||
| 12 | CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS | 12 | CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS |
| 13 | WITH DateSequence(Dates) AS | 13 | WITH DateSequence(Dates) AS |
| 14 | ( | 14 | ( |
| 15 | SELECT Date(CURRENT_DATE, '-7 day') | 15 | SELECT Date(CURRENT_DATE, '-6 day') |
| 16 | UNION ALL | 16 | UNION ALL |
| 17 | SELECT Date(Dates, '+1 day') | 17 | SELECT Date(Dates, '+1 day') |
| 18 | FROM DateSequence | 18 | FROM DateSequence |
| 19 | WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE) | 19 | WHERE Date(Dates, '+1 day') <= Date(CURRENT_DATE) |
| 20 | ) | 20 | ) |
| 21 | SELECT DateSequence.Dates as 'date', | 21 | SELECT DateSequence.Dates as 'date', |
| 22 | IFNULL(SUM(statistics.quantity), 0) AS 'total' | 22 | IFNULL(SUM(statistics.quantity), 0) AS 'total' |