Running Total

  • I have a table that captures rainfall data approx every 10 minutes. I want to calculate a sort of a running total of the Last Hour's Rainfall and the Last 8 Hours Rainfall by Time.

    Last Hour's Rainfall = Sum(PrecipitationAmount) for the previous 60 minutes (PrecipitationMinutes is the number of minutes over which the PrecipitationAmount was recorded from the weather station. This will usually be the last 6 values

    Last 8 Hour's Rainfall = Sum(PrecipitationAmount) for the previous 480 minutes. This will usually be the last 48 values

    Is there a way to do this using OVER (partition by ...)?

    Here is the table:

    Here is the table:
    CREATE TABLE [aData].[tblWeather](
    [WeatherID] [int] IDENTITY(1,1) NOT NULL,
    [MessageID] [varchar](50) NOT NULL,
    [FK_BaseUnitID] [int] NOT NULL,
    [PrecipitationAmount] [decimal](6, 2) NULL,
    [PrecipitationMinutes] [int] NOT NULL,
    [DataTime] [datetime] NOT NULL,
    CONSTRAINT [PK_tblWeather] PRIMARY KEY CLUSTERED
    (
    [WeatherID] ASC
    )
    )

    and here is some sample data:

    INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('6c4f5129-ddf5-5aa5-a8eb-07c36388dc2d', 0.72, 10, '17-Mar-2020 12:02:30')
    INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('e16aa602-a153-5f11-8477-f0e861b740b6', 0.48, 10, '17-Mar-2020 12:12:44')
    INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('b81b36f8-8c62-5923-9593-60124f286fc5', 0.23, 10, '17-Mar-2020 12:22:59')
    INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('c4c64555-17ac-5e25-98f6-13bfd21df182', 0.17, 11, '17-Mar-2020 12:33:13')
    INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('c8b99d6c-45b5-57ba-abe1-cb868da297fe', 0.16, 10, '17-Mar-2020 12:43:27')
    INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('34d4730f-4c01-5f91-becf-25e5867fe008', 0.53, 10, '17-Mar-2020 12:53:42')
    INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('71d18557-fc82-51f6-9eb4-d3bd306181b2', 0.54, 10, '17-Mar-2020 13:03:56')
    INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('70e3e9f7-707b-5375-9de7-bf1dbf315593', 0.41, 11, '17-Mar-2020 13:14:11')
    INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('7e96fd3e-1dda-5a5b-a86a-abd4cc6b465d', 0.07, 10, '17-Mar-2020 13:24:25')
    INSERT INTO tblWeather(MessageID, PrecipitationAmount, PrecipitationMinutes, DataTime) VALUES ('8aaf45a9-5eda-5f80-985e-13438acde1a5', 0.63, 10, '17-Mar-2020 13:34:40')

     

  • I think you've figured it out, you just haven't tried it yet. Google the usage of SUM() OVER (PARTITION BY ORDER BY ) and use ROWS BETWEEN 5 PRECEDING AND CURRENT ROW

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Be careful of having exact numbers in the ROWS clause if your data isn't clean. You can partition by some datepart(hour), but be aware this might not run quickly. If you're running this often, that could be an issue.

  • Thanks Guys,

    I think I can get it from that. My data isn't exact but I can get a good approximation from it (as weather data is anyway). I've made a CTE to put together the summary data for 1 hour and 8 hour data. By summing the minutes for each data point, I can see whether the data is valid or not: for 1 hour data if the precipitationminutes is not between 50 & 70 minutes, disregard. For 8 hour data, if the precipitationminutes > 460 and 500 minutes, disregard. It seems to work pretty well. Does it look efficient to the experts?

    With RainfallAccumulated (WeatherID, FK_BaseUnitID, OneHourRainfall, OneHourDuration, EightHourRainfall, EightHourDuration)
    AS
    (
    selectWeatherID
    ,FK_BaseUnitID
    ,Sum(PrecipitationAmount) OVER (PARTITION BY FK_BaseUnitID ORDER BY DataTime DESC ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
    ,Sum(PrecipitationMinutes) OVER (PARTITION BY FK_BaseUnitID ORDER BY DataTime DESC ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
    ,Sum(PrecipitationAmount) OVER (PARTITION BY FK_BaseUnitID ORDER BY DataTime DESC ROWS BETWEEN 46 PRECEDING AND CURRENT ROW)
    ,Sum(PrecipitationMinutes) OVER (PARTITION BY FK_BaseUnitID ORDER BY DataTime DESC ROWS BETWEEN 46 PRECEDING AND CURRENT ROW)
    FROMadata.tblWeather
    )

    selectW.WeatherID
    ,MessageID
    ,DataTime
    ,OneHourDuration
    ,EightHourDuration
    ,CASEWHEN R.OneHourDuration between 50 and 70 THEN OneHourRainfall
    ELSE NULL
    END AS OneHourRainfall
    ,CASEWHEN R.EightHourDuration between 460 and 500 THEN EightHourRainfall
    ELSE NULL
    END AS EightHourRainfall
    fromadata.tblweather W INNER JOIN RainfallAccumulated R On W.WeatherID = R.WeatherID
  • A possible solution!

  • USE master;

    GO

    DROP DATABASE IF EXISTS TestDB;

    GO

    CREATE DATABASE TestDB;

    GO

    USE TestDB;

    CREATE TABLE dbo.tblWeather

    (

    WeatherID   INT IDENTITY(1,1)  NOT NULL

    CONSTRAINT PK_tblWeather PRIMARY KEY,

    FK_BaseUnitID  INT      NOT NULL,

    PrecipitationAmount DECIMAL(6, 2)   NULL,

    DataTime   DATETIME    NOT NULL

    );

    GO

    INSERT INTO dbo.tblWeather (FK_BaseUnitID, PrecipitationAmount, DataTime) VALUES

    (27, 0.72, '17-Mar-2020 12:02:30'),

    (27, 0.48, '17-Mar-2020 12:12:44'),

    (27, 0.23, '17-Mar-2020 12:22:59'),

    (27, 0.17, '17-Mar-2020 12:33:13'),

    (27, 0.16, '17-Mar-2020 12:43:27'),

    (27, 0.53, '17-Mar-2020 12:53:42'),

    (27, 0.54, '17-Mar-2020 13:03:56'),

    (27, 0.41, '17-Mar-2020 13:14:11'),

    (27, 0.07, '17-Mar-2020 13:24:25'),

    (27, 0.63, '17-Mar-2020 13:34:40');

    GO

    WITH MaxTime

    AS

    (

    SELECT MAX(DataTime) AS Endtime

    FROM dbo.tblWeather

    ),

    Times

    AS

    (

    SELECT DATETIMEFROMPARTS(YEAR(MIN(DataTime)), MONTH(MIN(DataTime)), DAY(MIN(DataTime)),

    DATEPART(Hour, MIN(DataTime)), 0, 0, 0) AS AggTime

    FROM dbo.tblWeather

    UNION ALL

    SELECT DATEADD(HOUR, 1, AggTime)

    FROM Times

    WHERE AggTime < (SELECT EndTime FROM MaxTime)

    ),

    SumHour

    AS

    (

    SELECT FK_BaseUnitID,

    Times.AggTime,

    SUM(PrecipitationAmount) AS SumPrecipitationAmount

    FROM Times LEFT JOIN dbo.tblWeather ON tblWeather.DataTime >= Times.AggTime AND

    tblWeather.DataTime < DATEADD(HOUR, 1, Times.AggTime)

    GROUP BY FK_BaseUnitID, Times.AggTime

    )

    SELECT FK_BaseUnitID,

    AggTime AS TimeFromOneHour,

    DATEADD(SECOND, 3599, AggTime) TimeToOneHour,

    SumPrecipitationAmount AS SumOneHour,

    MIN(AggTime) OVER(PARTITION BY FK_BaseUnitID ORDER BY AggTime ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS TimeFromEightHour,

    DATEADD(SECOND, 3599, AggTime) AS TimeToEightHour,

    SUM(SumPrecipitationAmount) OVER(PARTITION BY FK_BaseUnitID ORDER BY AggTime ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS SumEightHour

    FROM SumHour

    WHERE FK_BaseUnitID = 27

    ORDER BY FK_BaseUnitID, AggTime

    OPTION (MAXRECURSION 0);

    GO

    -- Many Data

    INSERT INTO dbo.tblWeather (FK_BaseUnitID, PrecipitationAmount, DataTime)

    SELECT FK_BaseUnitID,

    PrecipitationAmount,

    DATEADD(MINUTE,

    DATEDIFF(MINUTE,

    (SELECT MIN(DataTime) FROM dbo.tblWeather),

    (SELECT MAX(DataTime) FROM dbo.tblWeather)) + 10,

    DataTime)

    FROM dbo.tblWeather;

    GO 8

    -- More data

    SELECT (SELECT MIN(DataTime) FROM dbo.tblWeather),

    (SELECT MAX(DataTime) FROM dbo.tblWeather);

    UPDATE dbo.tblWeather

    SET PrecipitationAmount = NULL

    WHERE Datatime BETWEEN DATEADD(HOUR, 4, (SELECT MIN(DataTime) FROM dbo.tblWeather)) AND

    DATEADD(HOUR, 7, (SELECT MIN(DataTime) FROM dbo.tblWeather));

    UPDATE dbo.tblWeather

    SET PrecipitationAmount = NULL

    WHERE Datatime BETWEEN DATEADD(HOUR, 201, (SELECT MIN(DataTime) FROM dbo.tblWeather)) AND

    DATEADD(HOUR, 338, (SELECT MIN(DataTime) FROM dbo.tblWeather));

    UPDATE dbo.tblWeather

    SET PrecipitationAmount = PrecipitationAmount + 0.3

    WHERE Datatime BETWEEN DATEADD(HOUR, 47, (SELECT MIN(DataTime) FROM dbo.tblWeather)) AND

    DATEADD(HOUR, 211, (SELECT MIN(DataTime) FROM dbo.tblWeather));

  • Thanks, I'll have a play and report back on how it goes over the weekend.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply