Grouping a Query by Each n Minutes Using Read-Only Access

====================================================================

In this article, we will explore how to group a SQL query by each n minutes using read-only access. We will also discuss the challenges of working with limited privileges and provide a solution that generates a list of dates 5 minutes apart between 10:45 and 11:20.

Challenges with Read-Only Access

When working with read-only access, it can be challenging to perform certain operations, such as grouping data by specific intervals. In this case, we want to group our query by every n minutes (in our example, n = 5). However, the existing query uses a time range that is not necessarily aligned with the desired interval.

Generating Dates

To overcome this challenge, we can use a common table expression (CTE) to generate a list of dates. This will allow us to create a separate dataset that includes every n minute between two specified times.

WITH dg (d) AS (
  SELECT CONVERT(datetime, '2019-07-02 10:45:00', 120) AS d
  UNION ALL
  SELECT DATEADD(minute, 5, a.d) AS d
  FROM   dg a
  WHERE  a.d < CONVERT(datetime, '2019-07-02 11:20:01', 120)
)

This CTE uses the UNION ALL operator to combine two types of rows:

1. The first row is a single date and time value ('2019-07-02 10:45:00').
2. The second row is generated by adding 5 minutes to the previous date using the DATEADD function.

The WHERE clause ensures that the last row in the sequence does not exceed the specified end time.

Cross Joining with Values

Next, we need to cross join the generated dates with a set of values ('Z03', 'Z04', and 'Z05') to create a dataset with all possible combinations of date and value pairs.

SELECT * FROM
  dg
  CROSS JOIN
  (SELECT 'Z03' as z UNION ALL SELECT 'Z04' UNION ALL SELECT 'Z05') z

This cross join creates a Cartesian product of the dates and values, resulting in a large dataset with many rows.

Joining with Real Data

Finally, we need to left join our real data (CosMessage) onto the generated dataset. We use the following conditions:

• z.z = m.MessageType to match the value column with the message type.
• m.Handled >= dg.d to ensure that only records with a handled time greater than or equal to the current date are included.
• m.Handled < DATEADD(minute, 5, dg.d) to limit the records to those within the desired 5-minute interval.

SELECT dg.d, z.z, COUNT(m.MessageType) as c FROM
  dg
  CROSS JOIN
  (SELECT 'Z03' as z UNION ALL SELECT 'Z04' UNION ALL SELECT 'Z05') z
  LEFT JOIN dbo.CosMessage m
  ON
    z.z = m.MessageType AND
    m.Handled >= dg.d AND
    m.Handled < DATEADD(minute, 5, dg.d)
GROUP BY
  dg.d, z.z

Final Query

To see the final query in action, remove the GROUP BY clause and make it a SELECT \*.

SELECT * FROM
  dg
  CROSS JOIN
  (SELECT 'Z03' as z UNION ALL SELECT 'Z04' UNION ALL SELECT 'Z05') z
  LEFT JOIN dbo.CosMessage m
  ON
    z.z = m.MessageType AND
    m.Handled >= dg.d AND
    m.Handled < DATEADD(minute, 5, dg.d)

Conclusion

In this article, we demonstrated how to group a SQL query by each n minutes using read-only access. We used a common table expression (CTE) to generate a list of dates and then performed a cross join with values. Finally, we joined the generated dataset with real data using conditions that ensured only relevant records were included. By following these steps, you can create an efficient and effective solution for grouping your data by specific intervals even when working with limited privileges.

Last modified on 2025-04-23