Readmit Logic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Readmit Logic

Author

Topic

singularity
Posting Yak Master

153 Posts

Posted - 2008-08-13 : 17:03:32

Hi,

I have a table as follows:

IP_Claims:


Mbr_ID Claim_Nbr    Admit_Dt  Discharge_Dt
1       1001        1/1/2008  1/3/2008
1       1002        1/7/2008  1/14/2008
1       1003        2/25/2008 2/28/2008
1       1004        3/1/2008  3/4/2008
1       1005        3/7/2008  3/8/2008
2       2001        5/4/2008  5/7/2008
2       2002        5/9/2008  5/10/2008
3       3001        4/5/2008  4/7/2008
3       3002        6/1/2008  6/2/2008

For my first query, I need to pull out all the readmits from this table. A claim is considered a readmit if the admit_dt is within 30 days of the most recent discarge_dt on a previous claim. The tricky part is that I need to keep the corresponding initial admit. However, if there aren't any readmits (e.g. Mbr 3), I don't want to display the member at all. The expected output would be:


Mbr_ID Claim_Nbr Days_Between_Admissions     Type
1      1001        			    Initial	
1      1002        4			    Readmit

1      1003       			    Initial 
1      1004        2                        Readmit
1      1005        3                        Readmit

2      2001       			    Initial
2      2002        2                        Readmit

For my second query, I need to bump up the IP claims against another table called ER_Claims which looks like this:


Mbr_ID  Claim_Nbr  Serv_Dt
1       1006       1/17/2008
1       1007       3/10/2008
1       1008       3/20/2008
2       2003       1/7/2008
2       2004       7/15/2008
3       3003       6/20/2008

For each member, I want to find instances where there was an ER claim 30 days or less after the discharge_dt of an IP claim. I need to display both the IP and the ER Claim. Again, if there aren't any readmits, I don't want to display the member at all. The expected output is as follows:


Mbr_ID   Claim_Nbr  Day_Between_Admissions      Type
1        1002 					IP
1        1006 		3			ER

1        1005					IP
1        1007           2                       ER
1        1008           10                      ER

3        3002                                   IP
3        3003           18 			ER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources