Good Afternoon Team,

Hoping you can help me .

Every month i get a count of records associated to a table. What i would like to do is month on month calculate the % variance (between jan vs feb, feb vs march etc). if the % variance is greater than the threshold of 10% then a new column called result should say "fail" we estimate that that the table should only grow 1/2% per month, if its over 10% then we need to investigate as we believe this is an issue with the loading of the table

Threshold = 10% Variance = Result = PASS or fail

Table PS & Data

create table #PS
CountN nvarchar(80),
Month nvarchar(80)

('1000', 'Jan'),
('1100', 'Feb'),
('1500', 'Mar'),
('3000', 'April');

Table PS Result :

CountN  Month
1000    Jan
1100    Feb
1500    Mar
3000    April

Expect Outcome

 CountN     Month     Variance    Result
    1000    Jan
    1100    Feb       10%         P
    1500    Mar       36%         F
    3000    April     100%        F

whats the best way to achieve this.

looking forward to your help

  • 1
    What about multi-year data? – Michael Kutz Aug 20 '19 at 11:20
  • Why on earth do you store numbers as nvarchar? (which actually does not exist) And why do you store Month as nvarchar? Date values have to be stored as DATE or TIMESTAMP data type - nothing else. – Wernfried Domscheit Aug 21 '19 at 12:26

This query should help you:

WITH t(CountN, mon) AS
    (SELECT 1000, 'Jan' FROM dual
    UNION ALL SELECT 1200, 'Feb' FROM dual
    UNION ALL SELECT 1500, 'Mar' FROM dual
    UNION ALL SELECT 3000, 'April' FROM dual)
SELECT CountN, mon, 
    ROUND(100*RATIO_TO_REPORT(CountN) OVER (), 2)||'%' AS ratio

|1000  |Jan  |14.93%|
|1200  |Feb  |17.91%|
|1500  |Mar  |22.39%|
|3000  |April|44.78%|
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.