I have the following data:
ID | username | Cost | time |
---|---|---|---|
1 | test1 | 1 | 2021-05-22 11:48:36.000 |
2 | test2 | 2 | 2021-05-20 12:55:22.000 |
3 | test3 | 5 | 2021-05-21 00:00:0-0.000 |
I would like to count the costs for the username with a daily figure and a month to date figure in once table
I have got the following
SELECT
username,
COUNT(*) cost
FROM
dbo.sent
WHERE
time BETWEEN {ts '2021-05-01 00:00:00'} AND {ts '2021-05-22 23:59:59'}
GROUP BY
username
ORDER BY
cost DESC;
This will return the monthly figures and changing the time to ‘2021-05-22 00:00:00’} AND {ts ‘2021-05-22 23:59:59’} will give the the daily however I would like a table to show daily and MTD together
username | Daily | MTD |
---|---|---|
test1 | 1 | 1012 |
test2 | 2 | 500 |
test3 | 5 | 22 |
Any help or pointers would be fantastic, I am guessing that I need a temp table and then run again using the MTD range updating the temp table where the username is the same then to export and delete – but i have no idea where to start.
Advertisement
Answer
First, you need one row per user and date, not just one row per user.
Second, you should fix your date arithmetic so you are not missing a second.
Then, you can use aggregation and window functions:
SELECT username, CONVERT(DATE, time) as dte,
COUNT(*) as cost_per_day,
SUM(COUNT(*)) OVER (PARTITION BY username ORDER BY CONVERT(DATE, time)) as mtd
FROM dbo.sent s
WHERE time >= '2021-05-01' AND
time < '2021-05-23'
GROUP BY username, CONVERT(DATE, time)
ORDER BY username, dte;
You can learn more about window functions in a SQL tutorial (or, ahem, a book on SQL) or in the documentation.
EDIT:
If you only want the most recent date and MTD, then you can either filter the above query for the most recent date or use conditional aggregation:
SELECT username,
SUM(CASE WHEN CONVERT(DATE, time) = '2021-05-22' THEN 1 ELSE 0 END) as cost_per_most_recent_day,
COUNT(*) as MTD
FROM dbo.sent s
WHERE time >= '2021-05-01' AND
time < '2021-05-23'
GROUP BY username
ORDER BY username;
And, you can actually express the query using the current date so it doesn’t have to be hardcoded. For this version:
SELECT username,
SUM(CASE WHEN CONVERT(DATE, time) = CONVERT(DATE, GETDATE()) THEN 1 ELSE 0 END) as cost_per_most_recent_day,
COUNT(*) as MTD
FROM dbo.sent s
WHERE time >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AND
time < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
GROUP BY username
ORDER BY username;