Accumulation Query by hour

Discussion in 'General MSSQL Discussion' started by bonlec, Mar 16, 2022.

  1. bonlec


    Mar 16, 2022
    Likes Received:
    Hi all, I'm hoping someone can lead me in the right direction, I have a current working query which totals Monday values, however I would like to break this down into hours to show the accumulated value of a specific hour (9:01am to 9:59am), I'm not looking to show the hours of the whole day just specific hours. The hours can be satic in the query however the date will need to by dynamic to show the current day. The purpose is to present kwh usage over hourly periods from an accumulation power meter.

    current Monday query

    SELECT ISNULL(MAX(AccumValue)-Min(AccumValue), 0)
    FROM ReportingPointValue
    WHERE PointID=16 AND Status=1 AND CONVERT(DATE, timestamp, 103) = CONVERT(DATE, DATEADD(wk, (DATEDIFF(wk, 0, '1/1/' + CAST(YEAR(GETDATE()) as NVARCHAR)) + (DATEPART(isowk,GETDATE())-1)), 0)+0, 103)

    Results is
    bonlec, Mar 16, 2022
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.