aboutsummaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
authorzberwaldt <17715430+zberwaldt@users.noreply.github.com>2024-03-21 11:25:44 -0400
committerGitHub <noreply@github.com>2024-03-21 11:25:44 -0400
commit7f9a8d74d7c0c66e7bf69892fbf893d45f1bfb82 (patch)
tree3e2355a4ec3d07df8337bcaf1ab9e500a1827b67 /db
parent8cdb9b7f15afc69fe903f2be566bbc8f3605e53f (diff)
parente370616213d92b66685e0fdb0e76c97e08d0b1b6 (diff)
Merge pull request #7 from zberwaldt/stagingHEADprod
Staging
Diffstat (limited to 'db')
-rw-r--r--db/sql/views.sql6
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
4CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS 4CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS
5SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color 5SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color
6FROM users 6FROM users
7LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day') 7LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now')
8LEFT JOIN preferences ON users.id = preferences.user_id 8LEFT JOIN preferences ON users.id = preferences.user_id
9GROUP BY users.name; 9GROUP BY users.name;
10 10
@@ -12,11 +12,11 @@ GROUP BY users.name;
12CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS 12CREATE 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 )
21SELECT DateSequence.Dates as 'date', 21SELECT DateSequence.Dates as 'date',
22 IFNULL(SUM(statistics.quantity), 0) AS 'total' 22 IFNULL(SUM(statistics.quantity), 0) AS 'total'