Balance of stock per day

Question

I'd like to have a bit of confirmation from you guys:

I have a view that has (among others) columns time_in, time_out, Company, weight, that shows when company put some weight into depot and when (if at all) took it out. So it looks like this:

time_in       time_out      company      weight
1-07-2013     3-07-2013       A            10
2-07-2013     NULL            A            15
2-07-2013     4-07-2013       B            5
3-07-2013     NULL            B            1

etc

Now I'd like to make a balance for each day for each company, so it would be something like:

Date               Company               Balance
1-07-2013            A                    10
1-07-2013            B                     0
2-07-2013            A                    25
2-07-2013            B                     5
3-07-2013            A                    15
3-07-2013            B                     6
4-07-2013            A                    15
4-07-2013            B                     1

etc

While I know how to run a basic running total, i.e.

SELECT a.time_IN, a.weight, (SELECT sum (b.weight) from DEPOT b 
where b.time_in <= a.time_in) as total_weight from DEPOT a

and I can guess that balance would be something like

SELECT a.time_IN, a.weight, ((SELECT sum (b.weight)
from DEPOT b where b.time_in <= a.time_in)
-(SELECT sum (c.weight) from DEPOT c
where a.time_out >= c.time_out) as balance from DEPOT a

Am I heading in right direction here? Also,do I have to simply add GROUP BY company to get info by company?

Hart CO · Answer 1 · 2013-09-20T05:08:12.407

If you want a daily record, you'll need a driver table that lists all dates, then to JOIN to that table using the time in/out ranges you have set. Then I think it makes sense to set the weight values at a daily level after joining, so you can do your running total simply. This works:

DECLARE @min_dt date = (SELECT MIN(time_in) FROM #Table1)
       ,@max_dt date = (SELECT MAX(time_out) FROM #Table1)
;WITH cte AS (SELECT @min_dt AS dt
              UNION ALL
              SELECT DATEADD(DAY,1,dt)
              FROM cte
              WHERE dt < @max_dt)
     ,cte2 AS (SELECT a.dt,b.company,CASE WHEN a.dt = b.time_in THEN b.weight
                             WHEN a.dt = b.time_out THEN b.weight *-1
                             ELSE 0
                        END AS signed_weight
               FROM cte a
               LEFT JOIN #Table1 b
                 ON a.dt BETWEEN b.time_in AND ISNULL(b.time_out,'2099-12-31')) 
SELECT dt,company,(SELECT SUM(b.signed_Weight) 
                   FROM cte2 b 
                   WHERE a.company = b.company
                     AND b.dt <= a.dt) 
FROM cte2 a
GROUP BY a.dt,company
ORDER BY a.company, dt
OPTION (MAXRECURSION 0);

Demo: SQL Fiddle

Note: If you wanted every day for each company (the version above only covers dates between the min/max entry for each company) you'd need the driver table to have all company/date combinations.

It looks good, but I keep hitting the [maximum recursion error](http://stackoverflow.com/questions/9650045/the-maximum-recursion-100-has-been-exhausted-before-statement-completion). 10 minutes later I'm still waiting for results... But I guess the theory was righ — Yasskier, Sep 20 '13 at 05:04
Added `OPTION (MAXRECURSION 0);` If the tables are large, I'd suggest building out the driver table first and adding indexes on dates for both tables. Also I noticed that you have a `datetime` in your sample, that can create problems and that's why I used all `DATE`. — Hart CO, Sep 20 '13 at 05:11

Balance of stock per day

1 Answers1