Running sum from transactional records

Transact-SQL
1

Hi,

I hope someone can help me figure out how to do this as I am stuck. Basically, I have a table that contains some forecast items. Sample data below.

On 3 separate dates a forecast quantity was entered into the table. It is not an absolute value, which is what is making it hard for me to figure out. It is relative to what was already in the monthly ForecastBucket. To illustrate this, refer to the sample table below. On 1/15/2017, a forecast for the next 4 months was entered for an item. Since no earlier “DateEntered” values are present, the quantities are basically the forecast as of 1/15/2017.

Now, the next forecast update is on 2/13/2017 and the quantities on those rows are entered as an addition or subtraction to the quantity the ForecastBucket previously contained. (Hope that makes sense) . For example, the forecast entered on 2/13/2017 for bucket 3/1/2017 was -10. Basically, it’s saying we need 10 less than what was in the bucket the last time we forecast. So, if you were to SUM all the 3/1/2017 ForecastBuckets for DateEntered <= 2/13/2017 you would get 115 (125-10). This is basically what I am dealing with just on a much larger scale. Just more Items and more months.

DateEntered Item ForecastBucket Qty
1/15/2017 Bolt 2/1/2017 100
1/15/2017 Bolt 3/1/2017 125
1/15/2017 Bolt 4/1/2017 120
1/15/2017 Bolt 5/1/2017 105
2/13/2017 Bolt 3/1/2017 -10
2/13/2017 Bolt 4/1/2017 -17
2/13/2017 Bolt 5/1/2017 23
2/13/2017 Bolt 6/1/2017 150
3/16/2017 Bolt 4/1/2017 -25
3/16/2017 Bolt 5/1/2017 7
3/16/2017 Bolt 6/1/2017 12
3/16/2017 Bolt 7/1/2017 160

Here is an example of the format I would like to convert the data to. This way, I basically have a snapshot of what the absolute value of the ForecastBucket was on a particular DateEntered. I did the math in parenthesis to show where the number comes from. The boss wants to track/monitor what is going on with all the forecast changes that have been happening and wants to see it like this.

SnapshotDT Item ForecastBucket Qty
1/15/2017 Bolt 2/1/2017 100 (0+100)
1/15/2017 Bolt 3/1/2017 125 (0+125)
1/15/2017 Bolt 4/1/2017 120 (0+120)
1/15/2017 Bolt 5/1/2017 105 (0+105)
2/13/2017 Bolt 3/1/2017 115 (125-10)
2/13/2017 Bolt 4/1/2017 103 (120-17)
2/13/2017 Bolt 5/1/2017 128 (105+23)
2/13/2017 Bolt 6/1/2017 150 (0+150)
3/16/2017 Bolt 4/1/2017 78 (120-17-25)
3/16/2017 Bolt 5/1/2017 135 (105+23+7)
3/16/2017 Bolt 6/1/2017 162 (150+12)
3/16/2017 Bolt 7/1/2017 160 (0+160)

Currently I am stuck on how to approach this. I am not really a SQL guy, but rather a Jack of all trades IT guy that, well… is asked to do everything! Hope someone can help jumpstart me on how to do this. Some sample code based onmy example data would be very helpful.

Thanks,

Glenn

2

If you post useable sample data -- CREATE TABLE and INSERT statements -- I'll provide some SQL to do that.

3 
select dateentered
      ,item
      ,forecastbucket
      ,sum(qty) over(partition by item
                                 ,forecastbucket
                     order by dateentered
                     rows between unbounded preceding and current row
                    )
       as qty
  from yourtable
 order by item
         ,dateentered
         ,forecastbucket
;
2 Likes
4

In my opinion, this is a function of software developers pushing business logic into the database. This is not the place to figure this out. It should be done in the application (server-side).