CODEWITHSUNDEEP
sqlsql-server
Chetan_Vasudevan
Chetan_Vasudevan

Reputation: 2414

Sum of other ID's based on date in SQL Server CTE

My sample CTE which is coming from multiple sources and drawing data from:

with sample_cte as 
(
    ae.spvr as col1,
    ae.startDate as start_dt,
    ae.END_DT as end_dt,
    round(ae.Scre, 2, 1) as Scre,
from 
    diff_sources ae

I have a CTE which produces me an output like below:

col1 | start_dt | end_dt | score
-----+----------+--------+-------
a    | 10-01-19 |10-02-19| 50.50
a    | 10-02-19 |10-03-19| 55.50
b    | 10-01-19 |10-02-19| 60.50
b    | 10-02-19 |10-03-19| 65.50
c    | 10-01-19 |10-02-19| 70.50
c    | 10-02-19 |10-03-19| 75.50

I am looking for to add new column which gets the sum of scores based on their dates (sum of scores b and c for a, similarly sum of a and b for c).

The way it looks is something like below

col1 | start_dt | end_dt | new_sum_score_column
-----+----------+--------+----------------------------------------------
a    | 10-01-19 |10-02-19| sum of b and c on these dates = (60.50+70.50)
a    | 10-02-19 |10-03-19| sum of b and c on these dates = (65.50+75.50)
b    | 10-01-19 |10-02-19| sum of a and c on these dates = (50.50+70.50)
b    | 10-02-19 |10-03-19| sum of a and c on these dates = (55.50+75.50)
c    | 10-01-19 |10-02-19| similar logic
c    | 10-02-19 |10-03-19|

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

Assuming the date reference is to the first column, I think this works:

select t.*,
       sum(score) over (partition by start_dt) - score
from t;

That is . . . calculate the overall sum and subtract the current value.

EDIT:

If there are multiple startdates, then:

select t.*,
       (sum(score) over (partition by start_dt) - 
        sum(score) over (partition by start_dt, col1)
       )
from t;

Upvotes: 2

Related Questions