SQL Server query - how to calculate cumulative column X

Question

I have a testDB with 2 tables.

Table 1:

Create Table Table1 (
  id1 varchar(32) Not Null Primary Key,
  des varchar(100),
  btmdepth int
);

Table 2:

Create Table Table2 (
  id1 varchar(32) Foreign Key References Table1(id1),
  id2 varchar(32) Not Null Primary Key,
  des varchar(100),
  leng int
);

I have data in each table as below.

Table 1:

id1    des         btmdepth
111    Production  2000

Table 2:

id1   id2    des      leng
111   200    Tubing1  500
111   201    Tubing2  300
111   202    Tubing3  400

I want to create query to get a result as shown below:

id1   id2    des      leng   cumLeng  bottomdepth            topdepth
111   200    Tubing1  500    500      1300 (1600-300)        800 (1300-500)
111   201    Tubing2  300    800      1600(2000-400)         1300(1600-300)
111   202    Tubing3  400    1200     2000(Table1 btmdepth)  1600(2000-400)

The question is in the bracket.

You might want to change your subject title a little. 🤣 — Thom A
– Thom A ♦, Commented Aug 14, 2018 at 13:36
your best bet could be Cross Apply, where the second query sums the lengths from the earlier rows. BTW, the original wording was not inappropriate, it was standard jargon - it does make me wonder what other stuff people could be looking at. — Cato
– Cato, Commented Aug 14, 2018 at 13:42

LukStorms · Accepted Answer · 2018-08-14 14:39:38Z

1

You can do this using 3 cumulative sums.
And with a bit of trickery for the bottomdepth.

Test on SQL Fiddle here

SELECT t2.id1, t2.id2, t2.[des], t2.leng, 
SUM(t2.leng) OVER (PARTITION BY t2.id1 ORDER BY t2.id2) AS cumLeng,
t1.btmdepth - (SUM(t2.leng) OVER (PARTITION BY t2.id1 ORDER BY t2.id2 DESC) - t2.leng) AS bottomdepth,
t1.btmdepth - SUM(t2.leng) OVER (PARTITION BY t2.id1 ORDER BY t2.id2 DESC) AS topdepth
FROM Table2 t2
JOIN Table1 t1 ON t1.id1 = t2.id1
ORDER BY t2.id1, t2.id2;

edited Aug 14, 2018 at 14:39

answered Aug 14, 2018 at 13:59

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harzio Over a year ago

Thanks very much @LukStorms, your query works very good and give a result as I expected.

UnhandledExcepSean · Accepted Answer · 2018-08-14 13:53:25Z

While not the most efficient method (I think a CTE could work much better for performance), this works.

declare @Table1 table (
id1 varchar(32) Not Null Primary Key,
des varchar(100),
btmdepth int
);

declare @Table2 table (
id1 varchar(32) ,
id2 varchar(32) Not Null,
des varchar(100),
leng int
);

INSERT INTO @Table1 SELECT '111','Production',2000
INSERT INTO @Table2 SELECT '111','200','Tubing1',500
INSERT INTO @Table2 SELECT '111','201','Tubing2',300
INSERT INTO @Table2 SELECT '111','202','Tubing3',400

SELECT
    *
    ,(SELECT SUM(t2a.leng) FROM @Table2 t2a WHERE t2a.id1=t1.id1 AND t2a.id2<=t2.id2) AS [CumLength]
    ,(SELECT t1.btmdepth-ISNULL(SUM(t2b.leng),0) FROM @Table2 t2b WHERE t2b.id1=t1.id1 AND t2b.id2>t2.id2) AS [BottomDepth]
    ,(SELECT t1.btmdepth-ISNULL(SUM(t2c.leng),0) FROM @Table2 t2c WHERE t2c.id1=t1.id1 AND t2c.id2>=t2.id2) AS [TopDepth]
FROM @Table1 t1
INNER JOIN @Table2 t2 ON t2.id1=t1.id1
ORDER BY t2.id1,t2.id2

Thanks @UnhandledExcepSean, I tested your query and it gives a correct result too.

Michał Turczyn · Accepted Answer · 2018-08-14 13:55:44Z

Try this query, it can be easily accomplished with use of windowed function SUM with OVER(ORDER BY...):

Sample data:

declare @tbl1 table (id1 int, [des] varchar(100), btmdepth int);
insert into @tbl1 values (111, 'Production', 2000);

declare @tbl2 table (id1 int, id2 int, [des] varchar(100), leng int);
insert into @tbl2 values
(111, 200, 'Tubing1', 500),
(111, 201, 'Tubing2', 300),
(111, 202, 'Tubing3', 400);

Query:

select t2.id1,
       t2.id2,
       t2.[des],
       t2.leng,
       sum(t2.leng) over (order by t2.id2) cumLeng,
       t1.btmdepth - sum(t2.leng) over (order by t2.id2 desc) + t2.leng bottomdepth,
       t1.btmdepth - sum(t2.leng) over (order by t2.id2 desc) topdepth
from @tbl2 t2
left join @tbl1 t1 on t2.id1 = t2.id1
order by t2.id2

Collectives™ on Stack Overflow

SQL Server query - how to calculate cumulative column X

3 Answers 3

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Related