SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Readmit Logic
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

singularity
Posting Yak Master

151 Posts

Posted - 08/13/2008 :  17:03:32  Show Profile  Reply with Quote
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          



  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000