SQLTeam.com | Weblogs | Forums

Calculating the rate auth units used per month


#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.