get data for first 3 months that have claims

user_8651028 0
Viewed 71

I have a table of members and their claims value, I'm interested in getting the claims values for the first 3 months for each member. Here's what I've tried so far:

 WITH START as
      (SELECT [HEALTH_ID]
          ,MIN([CLM_MONTH]) as DOS
      FROM [TEST]
      GROUP BY
     [HEALTH_PLAN_ID])

SELECT HEALTH_ID
,DOS
,FORMAT(DATEADD(month, +1, DOS), 'MM/dd/yyyy')
,FORMAT(DATEADD(month, +2, DOS), 'MM/dd/yyyy')
FROM START

My plan is to get the dates of the first 3 months with claims then join the claim amounts to ID and dates. The problem here is not every member has claims in consecutive months and the dateadd function gives me consecutive months. For example if a member has claims in jan, feb, april, may etc...I'm interested in the claims for jan, feb and april since there were no claims in march. Using the dateadd function would give me dates jan, feb, march excluding april.

In summary, I need help getting the first 3 months that have claims values(months may or may not be consecutive).

sql-server sql
1 Answers

Using dense_rank() to rank the months, partitioned by Health_Id, in order to filter for the first three months of each Health_Id.

;with cte as (
  select *
    , dr = dense_rank() over (
        partition by Health_ID 
        order by dateadd(month, datediff(month, 0, CLM_Month)  , 0) /* truncate to month */
        )
  from test
)
select *
from cte
where dr < 4 -- dense rank of 1-3

test data:

create table test (health_id int, clm_month date)
insert into test values 
 (1,'20170101'),(1,'20170201'),(1,'20170301'),(1,'20170401')
,(2,'20170101'),(2,'20170201'),(2,'20170401'),(2,'20170501') -- no March
,(3,'20170101'),(3,'20170115'),(3,'20170201'),(3,'20170215') -- Multiple per month
,(3,'20170401'),(3,'20170415'),(3,'20170501'),(3,'20170515')

rextester demo: http://rextester.com/MTZ16877

returns:

+-----------+------------+----+
| health_id | clm_month  | dr |
+-----------+------------+----+
|         1 | 2017-01-01 |  1 |
|         1 | 2017-02-01 |  2 |
|         1 | 2017-03-01 |  3 |
|         2 | 2017-01-01 |  1 |
|         2 | 2017-02-01 |  2 |
|         2 | 2017-04-01 |  3 |
|         3 | 2017-01-01 |  1 |
|         3 | 2017-01-15 |  1 |
|         3 | 2017-02-01 |  2 |
|         3 | 2017-02-15 |  2 |
|         3 | 2017-04-01 |  3 |
|         3 | 2017-04-15 |  3 |
+-----------+------------+----+
user_23334990
Related