title: how to make a column add based on a previous value a group of figures in another column sequentially on timeline
body: <p>What I am looking to do is create a column that looks at the Result value, adds them to the previous result and based on a group by DayStartDate.
  I have a timeline including hour of day that creates individual events.
  Basically so I can create a chronological view to show the total amount of the material moved at a specific point of the day.
  I am trying to see if I can do this in SQL rather than Power BI to reduce the load on the Power BI.</p>
  <p><a href="https://i.stack.imgur.com/g98X3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/g98X3.png" alt="current filtered out result" /></a></p>
  <p>I have been trying to create a new column that will sum each event based on the result in the other column. I have shown what I am trying to get in the new column and what I currently have.</p>
  <p><a href="https://i.stack.imgur.com/Ws4xy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Ws4xy.png" alt="desired result" /></a></p>
  <p>This is my current query.
  Any suggestions would be appreciated.</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
      c.*,
      YEAR(c.DayStartDate) * 10000 + MONTH(c.DayStartDate) * 100 + DAY(c.DayStartDate) AS DateID,
      DATEPART(hh, c.EndDate) * 10000 + DATEPART(mi, c.EndDate) * 100 + DATEPART(ss, c.EndDate) AS TimeId,
      42.5 AS Payload,
      CASE
        WHEN c.VehicleId != LAG(c.VehicleId) 
               OVER (ORDER BY c.VehicleId, c.EndDate)
           THEN CAST('00:00:00' AS time)
         WHEN c.DayStartDate != LAG(c.DayStartDate) OVER (ORDER BY c.VehicleId, c.EndDate)
           THEN CAST('00:00:00' AS time)
         WHEN LAG(c.DayStartDate) OVER (ORDER BY c.VehicleId, c.EndDate) IS NULL
           THEN CAST('00:00:00' AS time)
         WHEN c.ShiftNumber != LAG(c.ShiftNumber) OVER (ORDER BY c.VehicleId, c.EndDate)
           THEN CAST('00:00:00' AS time)
         ELSE
           CAST(CONVERT(varchar(22), c.EndDate - LAG(c.EndDate) OVER (ORDER BY c.VehicleId, c.EndDate), 114) AS time) 
         END AS Duration
  FROM   (
      SELECT
          b.*,
          CASE
            WHEN CAST(b.EndDate AS time) BETWEEN '00:00:00.000' AND '07:14:59.900'
              THEN CAST(CONVERT(date, b.EndDate - 1) AS date)
            ELSE
              CAST(CAST(b.EndDate AS date) AS date)
            END AS DayStartDate,
          CASE WHEN (CAST(b.EndDate AS time) BETWEEN 
                             '00:00:00.000' AND '07:14:59.900') OR
                             (CAST(b.EndDate AS time) BETWEEN '19:14:59.900' AND '23:59:59.900')
            THEN 1 ELSE 0 END AS ShiftNumber
       FROM    (
           SELECT
              e.EventId,
              ed.Name AS EventName,
              va.VehicleId,
              da.DriverId,
              dateadd(HH, DATEPART(TZ, e.EndDate AT time Zone si.TimeZoneInfoId) / 60, e.EndDate) AS EndDate,
              e.GPSLatEnd,
              e.GPSLongEnd
            FROM    Events e INNER JOIN
                    EventDefs ed ON ed.EventDefId = e.EventDefId INNER JOIN
                    Segments s ON s.SegmentId = e.SegmentId INNER JOIN
                    DriversAll da ON da.DriverId = s.DriverId INNER JOIN
                    Trips t ON t .TripId = s.TripId INNER JOIN
                    VehiclesAll va ON va.VehicleId = t .VehicleId INNER JOIN
                    Sites si ON si.SiteId = va.SiteId 
            WHERE  e.EventDefId = 1381
        ) b
  ) c
  </code></pre>