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]