Sql query with dynamic Time Interval-CodePudding

I'm trying to get the average values on a 15 min time interval. I'm following this post. I'm trying to figure out how can I keep the first timestamp as the user defined & next will be at a 15 minute interval from the Start Timestamp.

|TimeStamp|Tasks|VAL1|VAL2|
|---------|-----|----|----|
|11:12:30 |40   | 12 |16  |
|11:17:30 |40   | 26 |24  |

This is the code I'm using:

SELECT
   DateAdd(minute, 15*(DateDiff(minute, 0, NEW_LOG.[DT_Stamp]) / 15), 0) As Timestamp,
   Count(*) as Tasks,
   AVG(NEW_LOG.[CPU-400/TAGS_PLANT_A.IN9]) AS [VAL1],
   AVG(NEW_LOG.[CPU-400/TAGS_PLANT_A.IN10]) AS [VAL2]
FROM
   NEW_LOG
WHERE
   NEW_LOG.DT_Stamp BETWEEN @rptFromDateTime AND @rptToDateTime
GROUP BY
   (DateDiff(minute, 0, NEW_LOG.[DT_Stamp]) / 15)
ORDER BY Timestamp;

This works but the 15 minutes timestamp interval always starts from 0 like:

|TimeStamp|Tasks|VAL1|VAL2|
|---------|-----|----|----|
|11:00:30 |40   | 12 |16  |
|11:15:30 |40   | 26 | 24 |

CodePudding user response：

Just calculate the DATEADD() and DATEDIFF() relative to your desired start point; @rptFromDateTime rather than 0.

DATEADD(
  MINUTE,
  DATEDIFF(
    MINUTE,
    @rptFromDateTime,
    NEW_LOG.[DT_Stamp]
  )
  / 15
  * 15,
  @rptFromDateTime 
)

Demo : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=337f11d90e4b774b9b1d7a7dfd8b28f0