# Month on Month count to determine variance of table growth

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)
);

INSERT INTO #PS
VALUES
('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

• 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

## 1 Answer

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
FROM t;

+-------------------+
|COUNTN|MON  |RATIO |
+-------------------+
|1000  |Jan  |14.93%|
|1200  |Feb  |17.91%|
|1500  |Mar  |22.39%|
|3000  |April|44.78%|
+-------------------+
``````