please help with subqueries

[tnguyen315]

I'm using SQL 2008 R2, i have a query


SELECT Mpfile.PATIENT_NO AS [Patient No.], Padfile.CLINIC AS Clinic, Tbclinic.DESCRIPTION AS ClinicDesc, Padfile.APPT_DATE, Padfile.TYPE AS Type,
Padfile.DISPOSITION AS Status
FROM dbo.MPFILE AS Mpfile INNER JOIN
dbo.PADFILE AS Padfile ON Mpfile.PATIENT_NO = Padfile.PATIENT_NO INNER JOIN
dbo.TBCLINIC AS Tbclinic ON Padfile.CLINIC = Tbclinic.CLINIC
WHERE (Padfile.DISPOSITION = 'M')
GROUP BY Mpfile.PATIENT_NO, Padfile.CLINIC, Padfile.DISPOSITION, Padfile.APPT_DATE, Padfile.TYPE, Tbclinic.DESCRIPTION
ORDER BY Clinic, Padfile.APPT_DATE

If i run the time frame of Appt_Date from 12/5/2013 to 12/10/2013:
My result is:
Patient No. Clinic ClinicDesc Appt_Date Type Status
1 T1 Test1 12/5/2013 0 M
2 T1 Test1 12/10/2013 0 M
3 T2 Test2 12/8/2013 N M


However, patient 1 will have a pending appt in a next couple day

Patient No. Clinic ClinicDesc Appt_Date Type Status
1 T1 Test1 12/16/2013 0 P


How to modify the query to exclude those patients who will have pending appts (status = P) within the next 30 days.


Final result would be:
Patient No. Clinic ClinicDesc Appt_Date Type Status
2 T1 Test1 12/10/2013 0 M
3 T2 Test2 12/8/2013 N M

Please help
Thank you very much

 

[gk53]

SELECT Mpfile.PATIENT_NO AS [Patient No.], 
		Padfile.CLINIC AS Clinic, 
		Tbclinic.DESCRIPTION AS ClinicDesc, 
		Padfile.APPT_DATE, 
		Padfile.TYPE AS Type, 
		Padfile.DISPOSITION AS Status
FROM dbo.MPFILE AS Mpfile 
INNER JOIN dbo.PADFILE AS Padfile 
	ON Mpfile.PATIENT_NO = Padfile.PATIENT_NO 
INNER JOIN dbo.TBCLINIC AS Tbclinic 
	ON Padfile.CLINIC = Tbclinic.CLINIC
WHERE Padfile.DISPOSITION = 'M' and Padfile.DISPOSITION <> 'P'
	GROUP BY Mpfile.PATIENT_NO, Padfile.CLINIC, Padfile.DISPOSITION, Padfile.APPT_DATE, Padfile.TYPE, Tbclinic.DESCRIPTION
	ORDER BY Clinic, Padfile.APPT_DATE

 

[gmmastros]

Can you clarify what you mean by, "Next 30 days". We could use today's date for next 30 days calculation or we could use 30 days from the appt_date (where status = M). Or we could use 30 days from something else entirely.

Just out of curiosity, can you run this query?

Select	*
From    PADFILE As M_PADFILE
        Inner JOin PADFILE As P_PADFILE
	  On  M_PADFILE.PATIENT_NO = P_PADFILE.PATIENT_NO
	  And M_PADFILE.DISPOSITION = 'M'
	  And P_PADFILE.DISPOSITION = 'P'
	  And DateDiff(Day, M_PADFILE.Appt_Date, P_PADFILE.Appt_Date) <= 30
Where   M_PADFILE.Appt_Date >= '20131205'
        And M_PADFILE.Appt_Date < '20131211'

If I am not mistaken, this should return data where a pending appointment is within 30 days of the M row. In other words, this should list patients that you want to remove from your query. Is this correct?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tnguyen315]

Thanks all the the quick respond.

gmmastros, yes that's exactly what i'm looking for. however i need to join with other tables as well.

Again, thank you so much

 

[gmmastros]

I didn't intend for my previous post to be "the solution". I just wanted to confirm that I was on the right track. In fact... my previous post should be exactly wrong in that it shows patients that you want excluded.

Now try this:

SELECT  Mpfile.PATIENT_NO AS [Patient No.], 
        Padfile.CLINIC AS Clinic, 
		Tbclinic.DESCRIPTION AS ClinicDesc, 
		Padfile.APPT_DATE, 
		Padfile.TYPE AS Type, 
        Padfile.DISPOSITION AS Status
FROM    dbo.MPFILE AS Mpfile 
        INNER JOIN dbo.PADFILE AS Padfile 
          ON Mpfile.PATIENT_NO = Padfile.PATIENT_NO 
        INNER JOIN dbo.TBCLINIC AS Tbclinic 
          ON Padfile.CLINIC = Tbclinic.CLINIC
        [!]LEFT JOIN PADFILE As Pending
	  On  PADFILE.PATIENT_NO = Pending.PATIENT_NO
	  And PADFILE.DISPOSITION = 'M'
	  And Pending.DISPOSITION = 'P'
	  And DateDiff(Day, PADFILE.Appt_Date, Pending.Appt_Date) <= 30[/!]
WHERE   (Padfile.DISPOSITION = 'M')
        [!]And Pending.PATIENT_NO Is NULL[/!]
GROUP BY Mpfile.PATIENT_NO, Padfile.CLINIC, Padfile.DISPOSITION, Padfile.APPT_DATE, Padfile.TYPE, Tbclinic.DESCRIPTION
ORDER BY Clinic, Padfile.APPT_DATE

Notice that I used a left join this time. Also notice that I remove the rows where the join condition is true. This should produce the results you want.

More importantly... this is a situation where a left join is used to effectively filter out unwanted data. This is an important concept. If there's anything about this that you don't understand then speak up and I will explain it in more detail.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tnguyen315]

gmmastros, thank you so much for your help. I'm so appreciated.

I'm about to say that the first code is showing all patients with P status when testing.
I tested your second script and everything worked so fine. It showed all M patients with no P patients in the next 30 days booked.
Again, thank you so much.
NT

 

[gmmastros]

You're welcome.

Do you understand why this works? If not, let me know and I will explain.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom