From fd6f6f169f9ff9a1247228fb34dc9654a9584915 Mon Sep 17 00:00:00 2001 From: Zach Berwaldt Date: Thu, 21 Mar 2024 11:23:42 -0400 Subject: fix bugs, redo layout, reorg. --- db/sql/views.sql | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'db') 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 CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color FROM users -LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day') +LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now') LEFT JOIN preferences ON users.id = preferences.user_id GROUP BY users.name; @@ -12,11 +12,11 @@ GROUP BY users.name; CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS WITH DateSequence(Dates) AS ( - SELECT Date(CURRENT_DATE, '-7 day') + SELECT Date(CURRENT_DATE, '-6 day') UNION ALL SELECT Date(Dates, '+1 day') FROM DateSequence - WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE) + WHERE Date(Dates, '+1 day') <= Date(CURRENT_DATE) ) SELECT DateSequence.Dates as 'date', IFNULL(SUM(statistics.quantity), 0) AS 'total' -- cgit v1.1