Sql query to calculate accumulated value
I ran into some design and logic issues while creating the correct method / query for the requirement below.
My main table
select * from [table] where ID = 'XYZ'
Now I have calculated the accumulated risk-weight as follows: for this I need to write logic
terms:
for each month in the same year
if (Jan) -> sum of RiskCategory weights in January of the same year
if (Feb) -> sum of RiskCategory weights in January and February of the same year
if (March) -> sum of RiskCategory weights from January to March of the same year
if (April) -> sum of RiskCategory weights from January to April of the same year
...
...
...
if (Dec) -> sum of RiskCategory weights from January to December of the same year
** if several RiskCategories existed during any month, then
case 1. If the values are the same, then only accept one value.
case 2: if not the same maximum among them.
For example, if we want to calculate the profitability in the month of November in 2016, we only have to consider the following lines
** since I don't have data from January to September in 2016, I only looked at October and November data to calculate the November month
now the result should be
0.649 for the cardiovascular system (case 1) +
1.037 for pulmonary (case 2) +
0.666 for type 2 + diabetes
0.798 for psychiatric +
1,896 for kidneys +
0.536 constant = 5.582
and the final table should be
please check the sqlfiddle for this
http://sqlfiddle.com/#!6/8448e/6 [updated]
source to share
If I understood correctly, you really 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 on the suggestion WHERE
because I suppose you want to calculate it for everyone ID
in your table. The constant is 0.536
added to each line summed RiskWeight
as you did in the example.
source to share
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;
It doesn't work because you have the names of the months - and they'll be alphabetically ordered. SQL Server is very good at 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;
source to share
You can use Sum with windows function like below:
Select *, RiskWeight = Sum(Riskwight) over (Partition by Id, [Year] order by [Month])
from (
Select Id, [Year], [Month], RiskWight = Sum(riskWight) from inputtable
Group by Id, [Year], [Month]
) a
order by [year], [Month]
But here [Month] is ordered, as happens in a different order, as in alphabetical order, Better to have the month number in this place
source to share