Calculating the rate auth units used per month

Transact-SQL
1

I am trying to calculate the rate the units in an authorization are used per month so I can determine the rate at which all the units on the auth will get used up.

I have tried using the lag function which gives me the difference from the previous month but I don't know how to take it a step further and get the rate at which the auth units are being used.
For example I have
Month Monthly_units Diff_to_prev
9/1 650 NULL
10/1 675 25
11/1 725 50
12/1 775 50
1/1 935 160

Trying to figure out the growth(?) rate? Any help would be much appreciated. I've done a lot of research but haven't found anything other than lag that resembles what I am trying to do.

Thanks!

Here is my code
select
month,
[Medicaid No#],
[external auth no],
total_monthly_units,
total_monthly_units - lag(total_monthly_units) over (partition by [Medicaid No#], [external auth no] order by month) as diff_to_prev
from #temp4

2

can you please provide some sample data as follows. please add sample [Medicaid No#],

create table #rateauthunits(ratemonth date, total_monthly_units int, diff int)

insert into #rateauthunits
select '2017-09-01', 650, NULL union
select '2017-10-1', 675, 25 union
select '2017-11-1', 725, 50 union
select '2017-12-1', 775, 50 union
select '2018-01-01', 935 ,160 


select *,
total_monthly_units - lag(total_monthly_units) 
over (partition by [Medicaid No#], [external auth no] order by month) as diff_to_prev
 From #rateauthunits

Drop table #rateauthunits
3

Here is an update with the requested information:
create table #rateauthunits(ratemonth date, [Medicaid No#] varchar(12), [external auth no] varchar(25), total_monthly_units int, diff int)

insert into #rateauthunits
select '2017-09-01', 100612009A, 606708616, 650, NULL union
select '2017-10-1', 100612009A, 606708616, 675, 25 union
select '2017-11-1', 100612009A, 606708616, 725, 50 union
select '2017-12-1', 100612009A, 606708616, 775, 50 union
select '2018-01-01', 100612009A, 606708616, 935 ,160

select *,
total_monthly_units - lag(total_monthly_units)
over (partition by [Medicaid No#], [external auth no] order by ratemonth) as diff_to_prev
From #rateauthunits

4

how do you want to define growth rate? as a percentage? or ?

5

percentage growth.

create table #rateauthunits(ratemonth date, [Medicaid No#] varchar(12), [external auth no] varchar(25), total_monthly_units int, diff int)

insert into #rateauthunits
select '2017-09-01', 100612009A, 606708616, 650, NULL union
select '2017-10-1', 100612009A, 606708616, 675, 25 union
select '2017-11-1', 100612009A, 606708616, 725, 50 union
select '2017-12-1', 100612009A, 606708616, 775, 50 union
select '2018-01-01', 100612009A, 606708616, 935 ,160

select *,
       total_monthly_units - lag(total_monthly_units)
       over (partition by [Medicaid No#], [external auth no] order by ratemonth) as diff_to_prev
  From #rateauthunits

  ;with cte as (

SELECT month(ratemonth) as mon,
       total_monthly_units,LAG(g.total_monthly_units) OVER (ORDER BY ratemonth) PrevValue
FROM #rateauthunits g
)

select mon as Month,
       total_monthly_units as TransactionValue, 
	   (cast(round(cast((((total_monthly_units-PrevValue)*100.0)/PrevValue) as float),2) as varchar) + '%') as MonthlyGrowthRate 
 from cte


drop table #rateauthunits
6

That is amazing, worked perfectly! Can you tell me how I would go about finding the average rate for that group of values? Actually I tried to run it on the real data and I am getting a divide by zero error. Where is the (cast(round(cast((((total_monthly_units-PrevValue)*100.0)/PrevValue) as float),2) as varchar) + '%') as MonthlyGrowthRate statement can I put an ISNULL or something to eliminate the issue? The data comes up momentarily and it looks like only the first record has a NULL and that's where I am getting the error.

7

please provide that row that fails and we can sort it out

8

The results appear only for a second and then disappear with the Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered. error. I can keep running it and the results show up for a second but then go away.

THis is what it looks like ;with cte as (

SELECT [month],[Medicaid No#], [external auth no],
total_monthly_units,LAG(g.total_monthly_units) OVER (ORDER BY [month],[Medicaid No#], [external auth no] ) PrevValue
FROM #temp4 g
)

select Month,[Medicaid No#], [external auth no],
total_monthly_units as TransactionValue,
(cast(round(cast((((total_monthly_units-PrevValue)*100.0)/PrevValue) as float),2) as varchar) + '%') as MonthlyGrowthRate
from cte

9

find the offending row by doing the
;with cte as (

SELECT [month],[Medicaid No#], [external auth no],
total_monthly_units,LAG(g.total_monthly_units) OVER (ORDER BY [month],[Medicaid No#], [external auth no] ) PrevValue
FROM #temp4 g

and see what row might have the offending data

10

This is the row
2017-08-01 1009613810 606748716 920 NULL

Seems to be only the first row in the result set that has the null.

11

works for me with that new data, you must not be providing all the data, also be very careful not to be posting real Medicaid No# just FYI

12

I altered the medicaid number, thanks for the heads up. Do you not get that row? I am trying to put in a where statement to eliminate that one null row but I don't know where I can place it.

13

I have this list of data and the last one you provided and I do not get any sort of divide by zero error.