I have the following code that shows me the below results. I need to take these results and then SUM the number in the Count column and then GROUP BY the Week Of so that I have a sum of intervals per Week Of . Is this done via a sub query? I am not sure how to accomplish this.
DECLARE @interval as INT = 30;
DECLARE @StartDate as datetime = '2023-01-01 00:00:00';
SELECT
owneridname [Owner],
CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date) [Week Of],
/* Calculates the amount of minutes from the start date and the Created On date using the DATEDIFF function, then divide by 30 to get the total number of 30min increments in that time frame. */
/* This could end up as a decimal so we use FLOOR() to get the closest integer value. */
/* We then multiply by 30 to bring it back to the Created On's date but at the appropriate interval */
CAST(DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @StartDate, createdon) / CAST(@interval as decimal)) * @interval, @StartDate) as TIME(0)) [createdon_datetime_30_min_interval],
/* Counting the number of case creation dates per interval */
COUNT(*) [Count]
FROM incident
WHERE
createdon > DATEADD(day, -5, GETDATE())
GROUP BY
/* Grouping by the 30min intervals */
CAST(DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @StartDate, createdon) / CAST(@interval as decimal)) * @interval, @StartDate) as TIME(0)),
owneridname,
CAST(DATEADD(week, DATEDIFF(week, 0, createdon), 0) as date)
ORDER BY [Owner] ASC;| Owner | Week Of | Interval | Count |
|---|---|---|---|
| John Smith | 2023-07-24 | 21:00:00 | 12 |
| Brad Pitt | 2023-07-24 | 00:30:00 | 1 |
| Brad Pitt | 2023-07-24 | 00:00:00 | 1 |
| Neil Young | 2023-07-31 | 16:30:00 | 1 |
| Neil Young | 2023-07-24 | 11:00:00 | 10 |
| Neil Young | 2023-07-24 | 15:30:00 | 5 |
| Neil Young | 2023-07-24 | 12:30:00 | 1 |
| Neil Young | 2023-07-24 | 11:00:00 | 1 |
| Neil Young | 2023-07-24 | 11:30:00 | 1 |
| Mark Johnson | 2023-07-31 | 22:00:00 | 1 |
| Mark Johnson | 2023-07-31 | 22:30:00 | 1 |
| Mark Johnson | 2023-07-24 | 17:30:00 | 1 |
| Mark Johnson | 2023-07-24 | 21:30:00 | 3 |
| Mark Johnson | 2023-07-31 | 23:00:00 | 1 |
Next set of results I am looking for that show the sum of intervals per the `Week Of:
| Owner | Week Of | Sum |
| ------------ | ------------ | ------------ |
| John Smith | 7/24/2023 | 12 |
| Brad Pitt | 7/24/2023 | 2 |
| Neil Young | 7/24/23 | 18 |
| Neil Young | 7/31/2023 | 1 |
| Mark Johnson | 7/24/2023 | 4 |
| Mark Johnson | 7/31/2023 | 3 |
2条答案
按热度按时间cygmwpex1#
You can just wrap your query with outer query.
vwoqyblh2#