How to write code to see if number is greater than or within a limit -healthcare

Question

How to write code to see if number is greater than or within a limit -healthcare

Mr or Mrs. 500

Points: 502

April 23, 2019 at 1:37 pm

I am tasked with writing a query to determine if members are being approved for DME (medical equipment) for more than the monthly or yearly limit. The problem is the table has a column that is for example 3/month, 5/60 months, 1/year, etc.

So far I have collected member numbers, service code, and the number of times the DME was received per month using a row_number (orderby) and taking the max times of a service code per month.I am not sure what the next step is. One idea I had was to compare the number per month allowed to the number in the max orderby column. If the benefit is per year then maybe convert 5 years into 60 months? I am not sure if I have to somehow separate the 3/month into 2 columns for number and month. Unfortunately, there are 2500 rows in the spreadsheet that created the table.

Here is the code I have so far. I will create sample data in just a minute.

DECLARE @DOSFROM AS DATE
SET @DOSFROM = '1/1/2017'

IF OBJECT_ID ('TEMPDB.DBO.#ALL') IS NOT NULL DROP TABLE #ALL
IF OBJECT_ID ('TEMPDB.DBO.#DMELIST') IS NOT NULL DROP TABLE #DMELIST
IF OBJECT_ID ('TEMPDB.DBO.#COUNT') IS NOT NULL DROP TABLE #COUNT

SELECT DISTINCT SERVCODE
INTO #DMELIST
FROM VCUHSHMOEDM01.FIN01.SHANKLEY.SHANKLEYDME

SELECT 
C.ENROLLID,
CAST(EK.EFFDATE AS DATE) AS EFFDATE,
CAST(EK.TERMDATE AS DATE) AS TERMDATE,
EK.CARRIERMEMID,
M.FULLNAME AS MEMBER_NAME,
EK.PROGRAMID AS PROGRAM_ID,
--CAST(CD.DOSFROM AS DATE) AS DOSFROM,
CAST(DATEADD(mm,DATEDIFF(mm,0,CD.DOSFROM),0) AS DATE) AS 'MONTH_YEAR',
--CAST(CD.DOSTO AS DATE) AS DOSTO,CAST(DATEADD(mm,DATEDIFF(mm,0,CD.DOSTO),0) AS DATE) AS 'Year'
--CASE WHEN DATEDIFF(D,CD.DOSFROM,CD.DOSTO) = 0 THEN 1 ELSE DATEDIFF(D,CD.DOSFROM,CD.DOSTO) END AS DURATION,
C.PROVID AS PROVID,
C.CLAIMID AS CLAIM_NUM,
C.STATUS,
RIGHT(CD.CLAIMID,2) AS CLAIMREVERSED, 
CD.CLAIMLINE,
SUBCAT,
APPROVEDSERVCODE,
CD.AUTHUNITS AS AUTHUNITS,
CD.SERVUNITS AS SERVUNITS,
COALESCE(NULL,CD.MODCODE,CD.MODCODE2,MODCODE3,MODCODE4,MODCODE5) AS MODCODE,
CD.AMOUNTPAID,
CASE WHEN CD.AMOUNTPAID < 0 THEN 'Y' ELSE 'N' END AS REVERSAL
INTO #ALL
FROM VCUHSHMOEDW01.QNXT.DBO.ENROLLKEYS EK (NOLOCK) 
JOIN VCUHSHMOEDW01.QNXT.DBO.MEMBER M (NOLOCK) ON EK.MEMID = M.MEMID
JOIN VCUHSHMOEDW01.QNXT.DBO.CLAIM C (NOLOCK) ON M.MEMID = C.MEMID AND EK.ENROLLID = C.ENROLLID
JOIN VCUHSHMOEDW01.QNXT.DBO.CLAIMDETAIL CD (NOLOCK) ON C.CLAIMID = CD.CLAIMID
WHERE DOSFROM >= @DOSFROM
AND EFFDATE<>TERMDATE
AND C.RESUBCLAIMID = ''
AND C.STATUS NOT LIKE 'VOID'
AND C.CLAIMID NOT IN (SELECT ORGCLAIMID FROM QNXT.DBO.CLAIM WHERE ORGCLAIMID <> '')
AND APPROVEDSERVCODE IN (SELECT SERVCODE FROM #DMELIST)

SELECT DISTINCT
CARRIERMEMID,
APPROVEDSERVCODE,
COUNT(DISTINCT APPROVEDSERVCODE) AS SERVCOUNT,
MONTH_YEAR,
MODCODE
INTO #COUNT
FROM #ALL
WHERE CARRIERMEMID <> ''
GROUP BY CARRIERMEMID, APPROVEDSERVCODE, MONTH_YEAR

--DROP TABLE #COUNT
SELECT DISTINCT
MAX(ORDERBY) AS MAXORDERBBY,
MONTH_YEAR,
CARRIERMEMID,
APPROVEDSERVCODE
--SUM(SERVCOUNT) AS SERVCOUNTSUM
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY CARRIERMEMID, APPROVEDSERVCODE, MONTH_YEAR
ORDER BY CARRIERMEMID, APPROVEDSERVCODE, MONTH_YEAR) AS ORDERBY, * FROM #COUNT) ad
GROUP BY MONTH_YEAR,CARRIERMEMID, APPROVEDSERVCODE
ORDER BY CARRIERMEMID, MONTH_YEAR

Thanks for any help or suggestions.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ben.brugman SSChampion Points: 13369 More actions · Answer 1

You have provided a query. Without the data the query has to be reverse enginered to understand both the query and the data.

So providing data might help to understand te problem.

ben

shel 29143 Mr or Mrs. 500 Points: 502 More actions · Answer 2

Here is some sample code to see if that helps.

CREATE TABLE DMELIST(

MAXORDERBY INT,

MONTH_YEAR DATE,

CARRIERMEMID VARCHAR(20),

SERVCODE VARCHAR(10),

LIMIT VARCHAR(50))

INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('2','1/1/2018','123456789','E0720','1 60 months');INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('4','2/1/2018','123456789','A4556','3 month');

INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('2','1/1/2018','345678901','A4558','3 month');

INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('2','2/1/2018','345678901','A4558','3 month');

INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('1','4/1/2018','456789012','B4088','2 months');

INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('2','5/1/2018','456789012','B4088','2 months')

So my problem is the Limit has '3' as the number of DME pieces and 'months' as the limit. Those need to be broken out from 3 months to 3 and 2 months. Another issue is summing up for that servcode where there are more than one month, so for the last 2 lines in the table, the number to compare against should be 3 (summing up both MAXORDERBY for MONTH_YEAR 4/1/2018 and 5/1/208 and SERVCODE B4088. Lastly how do I compare the number of DME against the Limit, would I use case statements (there are 2500 rows in the actual DMELIST table) or a where clause? This is totally stumping me.

Thank you for any help at all!

This reply was modified 5 years, 7 months ago by shel 29143.

sgmunson SSC Guru Points: 110640 More actions · Answer 3

I think the problem here is that you haven't really defined exactly what you have for data. You are asking to break out a given column, but then don't specify EXACTLY what that column actually means. You need to be anally specific about what conditions will change the meaning, and how any particular set of rows relate to each other and are part of a set that needs to be treated as a set. Until that's clear, helping here is impractical because we really don't know exactly what you need to accomplish. Break it down into specifics. Include ALL the detail needed. And for your sample data, we also need to know exactly what the result should look like.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Ed B Hall of Fame Points: 3665 More actions · Answer 4

I don't think you will be able to do it in a single query.

Here's how I would approach it. This is fairly vague, but the details will depend on the nature of the data.

Get a distinct list of services contained within the data.
Get or specify a date range that the data covers.
Lookup the limit type for each service (units per month, visits per calendar year or benefit year etc). This assumes all members have the same limit types for the same service. This will involve defining unique limit types based on your data, or knowledge.
Identify the date units to be used for each limit type (year, month, Forever etc).
Identify all the potential date spans for each limit type + date unit. If you have data for 2017 and 2018 and a limit of units per month with a date unit of calendar month, then you have 24 date spans. If benefit year is not the same as calendar year, then you need to account for that. If that varies by member, then everything must be done at the benefit group level.
Join services to limit types to date spans and assign a max unit count. If the max units varies by benefit group, then again this must be done at the group or member level.
Join a distinct list of member + service to the above. Ideally just joining on service, but it will depends on the granularity of your coverage limits.
Look up the number of approved/paid claims for each member + service + limit + date range.
Compare approved units to max units. If you have any rolling date ranges it's more complex, so I hope you don't.
Get detail for comparisons of interest. This might include members who are within x% of the limit for an open time period. Health insurance morality aside, this might enable proactive interventions to deny coverage.

ben.brugman SSChampion Points: 13369 More actions · Answer 5

The test data:

drop table dmelist

CREATE TABLE DMELIST(
MAXORDERBY INT,
MONTH_YEAR DATE,
CARRIERMEMID VARCHAR(20),
SERVCODE VARCHAR(10),
LIMIT VARCHAR(50))
INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('2','1/1/2018','123456789','E0720','1 60 months');
INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('4','2/1/2018','123456789','A4556','3 month');
INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('2','1/1/2018','345678901','A4558','3 month');
INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('2','2/1/2018','345678901','A4558','3 month');
INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('1','4/1/2018','456789012','B4088','2 months');
INSERT INTO DMELIST (MAXORDERBY,MONTH_YEAR,CARRIERMEMID,SERVCODE,LIMIT) VALUES ('2','5/1/2018','456789012','B4088','2 months')
select * from DMELIST

Create Periods and count occurences for each period.

-- ben brugman
-- 20190425
-- General setup.
-- Create a 'named' period table, for 100 period for each type. (Can vary per period type).
-- Add to each row a period (or multiple period in case of overlapping periods).
-- Count the number of occurences within the period for a specific id.
-- Show the table with results (total), or select on results with specific counts.

-- L9 is a tally table, containing up to 4 Giga of 'tallies'.
-- Month3, is a row for each three month's so it is year quarters.
-- Month2, is a row for each two month's so it is 1/6 of a year. (6 periods each year)
-- Month2Overlap, is a row for each two month's, starting each month (12 periods each year).
-- Month60, is a row for each 60 month's so periods of 5 years.
-- Week4, is a row of each 4 weeks so periods of 4 weeks.
-- Periods is a combination of all periods.
-- A is a join between the DMEList table and for each type of period in which period it falls.
-- B is the count added so for each period type the number of rows in this period is counted.


;
WITH
L0 AS(SELECT 0 AS c UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0) -- 4
, L1 AS(select 0 as x from L0 A, L0 B, L0 C, L0 D) -- 4 ^4 = 256
, L2 AS(select 0 as x from L1 A, L1 B, L1 C, L1 D) -- (4 ^ 4) ^4 = 4 Giga
, L9 AS(Select row_number() OVER(PARTITION BY 1 order by x ) as P from L2) -- voeg rijnummers toe
, Month3 AS (SELECT '3 month' Plimit, P, 3 maxnr, DATEADD(M,3*(p-1), '20170101') starttime,DATEADD(M,3*p, '20170101') endtime FROM L9 WHERE P<100)
, Month2 AS (SELECT '2 months' Plimit, P, 2 maxnr, DATEADD(M,2*(p-1), '20170101') starttime,DATEADD(M,2*p, '20170101') endtime FROM L9 WHERE P<100)
, months60 AS (SELECT '1 60 months' Plimit, P, 1 maxnr, DATEADD(M,60*(p-1),'20170101') starttime,DATEADD(M,60*p,'20170101') endtime FROM L9 WHERE P<100)
, week4 AS (SELECT '4 week' Plimit, P, 2 maxnr, DATEADD(Week,4*(p-1), '20170101') starttime,DATEADD(Week,4*p, '20170101') endtime FROM L9 WHERE P<100)
, Month2Overlap AS (SELECT '2 monthsOver' Plimit, P, 2 maxnr, DATEADD(M,(p-1), '20170101') starttime,DATEADD(M,(p-1)+2, '20170101') endtime FROM L9 WHERE P<100)
, year5Overlap AS (SELECT '5 years over' Plimit, P, 1 maxnr, DATEADD(Year,(p-1), '20170101') starttime,DATEADD(Year,p+4,'20170101') endtime FROM L9 WHERE P<100)
, Periods AS (SELECT * FROM Month3 UNION 
select * from Month2 UNION 
select * from months60 UNION 
select * from week4 UNION
select * from Month2Overlap UNION 
select * from year5Overlap ) 
,A AS (SELECT periods.*, DMELIST.* FROM Periods INNER JOIN DMELIST ON Plimit = LIMIT and MONTH_YEAR >= Starttime and MONTH_YEAR < Endtime)
,B AS (SELECT COUNT(*) OVER (PARTITION BY Plimit, P, carriermemid) CountNr, * FROM A)
-- SELECT * FROM Periods ORDER BY Plimit, P -- 1 Show the period table.
SELECT * FROM b Order by Plimit, P -- 2 Show the count for each row in each period.
-- SELECT * FROM b WHERE nr >= maxnr -- 3 Show the rows which have to many rows within a period
-- choose 1, 2 or 3

-- The example does not contain enough data to do sufficient tests.
-- The question does not how the periods should be constructed. (For example can 2 month periods overlap eachother).
-- (The construction does allow for differen sorts of periods).

-- The Periods table.
-- Plimit should have the same values as limit in the DMELIST.
-- P The period number.
-- Maxnr The maximum allowed number of occurences within a period.
-- Starttime The start of the period, including the start.
-- Endtime The endtime of the period, excluding the end.

-- IMPORTANT:
-- CountNr The counted number of occurences in the specified period for the CarrierMeMid.

-- 
-- It is assumed that the number of occurences should be counted for the 'named' pariod for each CarrierMemid.
--

Please inform us if this helps.

And please anwser the unanswerd questions. (Which sort of periods. How to count. etc.).

Greetings,

Ben