sql query for accumulated value calculation

Question

I am facing some design and logic issues in generating correct method/query for the below requirement.

My primary table is

select * from [table] where ID='XYZ'

Now i have calculate the accumulated riskweight by as following requirement for this i need to write a logic

conditions:

for each month in the same year

if(Jan) -> sum of RiskCategory weights in Jan of the same year

if(Feb) --> sum of RiskCategory weights in Jan and Feb of the same year

if(March)-- > sum of RiskCategory weights from Jan to March of the same year

if(April) --> sum of RiskCategory weights from Jan to April of the same year

.

.

.

if(Dec) --> sum of RiskCategory weights from January to December of the same year

**if multiple RiskCategories existed for any month then

case 1. if values are same then take only one value.

case 2: if not same take the maximum among them.

For example if we want to calculate riskweight for the month of November in 2016 then we should consider the below rows only

** since I do not have data from January to September in 2016 i have considered only October and November data for November month calculation

now the result should be

0.649 for Cardiovascular (case 1)+

1.037 for Pulmonary (case 2)+

0.666 for Diabetes type 2 +

0.798 for Psychiatric +

1.896 for Renal +

0.536 constant = 5.582

and the final result table should be

please check sqlfiddle for this

http://sqlfiddle.com/#!6/8448e/6 [updated]

http://sqlfiddle.com/#!6/d05fe/1

Answer 1

If I got it right you actually want this:

SELECT
    ID,
    Year,
    Month,
    RiskWeight = SUM(MaxRiskweight) + 0.536
FROM (
    SELECT
        t1.ID,
        t1.Year,
        t1.Month,
        t2.RiskCategory,
        MaxRiskweight = MAX(t2.Riskwight)
    FROM
        inputTable AS t1
        JOIN inputTable AS t2
        ON t1.ID = t2.ID AND
           t1.Year = t2.Year AND
           t2.Month <= t1.Month
    GROUP BY
        t1.ID,
        t1.Year,
        t1.Month,
        t2.RiskCategory
    ) AS MaxRiskWeights
--WHERE
--  ID = 'XYZ'
GROUP BY
    ID,
    Year,
    Month

I commented the WHERE clause out because I suppose you want to calculate it for each ID in your table. The constant 0.536 is added to each summarized row of the RiskWeight, as you gave it in the example.

Answer 2

You can use window functions for this. I believe you essentially want:

select t.*,
       sum(riskweight) over (partition by id, year, riskcategory
                             order by month
                            ) as accum_riskweight
from t;

This doesn't quite work, because you have the month names -- and these will be ordered alphabetically. SQL Server is pretty good about converting dates, so this should work:

select t.*,
       sum(riskweight) over (partition by year, riskcategory
                             order by convert(date, month + ' 01 2000')
                            ) as accum_riskweight
from t;