September 13, 2012 at 6:48 pm
I think what I need to do might a little more complicated than my subject title, but in essence it's what I need. So here is the deal.
I have the following table schema (Table Attendance):
PK[PatientID] [varchar](20) NOT NULL,
PK[AttendDate] [datetime] NOT NULL,
[CTPCodes] [varchar](10) NOT NULL,
more fields, but not needed for this question.
Now what I need to do is, based on a date range, return only the clients that happen to have 2 services (I'll call it a duel service for now) on the same day. For example if I have client A who has had the following
-Date- CTPCodes
2/3 Group
2/3 Individual
2/4 Treatment
2/5 Other
For this client I would only want the records for 2/3 returned. For the purposes of this report I also have to make sure that I have one group and one Individual for the duel service. What would be the best way to handle this? I sure I could handle the Group/Individual thing with a standard where clause, but not sure how best to do the rest. Thanks for any suggestions.
September 13, 2012 at 7:11 pm
Hi Again,
I'm experimenting some using the find dupe queries, but have run into a result I don't want. Here is the query:
SELECT APGAttendance.[PatientID], APGAttendance.[AttendDate], APGAttendance.[CTPCodes]
FROM APGAttendance
WHERE (((CTPCodes = 'G0396' or CTPCodes = '90804' or CTPCodes = 'G0397' or
CTPCodes = '90806' or CTPCodes = '90849' or CTPCodes = 'H0005' or CTPCodes = '90853') and
(APGAttendance.[PatientID])
In (SELECT [PatientID]
FROM [APGAttendance] As Tmp
GROUP BY [PatientID],[AttendDate]
HAVING Count(*)>1 And [AttendDate] = [APGAttendance].[AttendDate]
)))
ORDER BY APGAttendance.[PatientID], APGAttendance.[AttendDate]
The it does find the duel service, but still not quite whqat I was looking for. Here here is the first few results from the above query:
PatientID AttendDate CTPCodes
-------------------- ----------------------- ----------
44441 2010-08-12 00:00:00.000 90804
44441 2010-08-12 00:00:00.000 90853
44441 2012-08-18 00:00:00.000 90853
The first two records is what I want, but the last one is not part of a duel service and on top of that it happened on a different day, so I don't want that included in the result. Any questions.
Thanks
September 13, 2012 at 7:29 pm
The DDL you posted doesn't seem to fully match your second post's query.
I suggest you post complete DDL and sample data in a readily consumable format (i.e., INSERT/SELECTs) that can be run without errors. With that, we can provide you a fully tested solution.
The HAVING clause is the right approach. But my suggestion is that you first identify the date records you need using it (e.g., in a CTE) and then JOIN that back to the table to get whatever other fields you need off of it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 14, 2012 at 1:19 am
As Dwain.c mentions DDL and sample data would help a lot, however this may be what you are looking for as you need to select based on the PatienId AND AttendDate, so removing the IN clause and replacing it with a join should work.
SELECT APGAttendance.[PatientID], APGAttendance.[AttendDate], APGAttendance.[CTPCodes]
FROM APGAttendance att1
JOIN (SELECT [PatientID],[AttendDate]
FROM [APGAttendance] As Tmp
GROUP BY [PatientID],[AttendDate]
HAVING Count(*)>1) grpatt
on grpatt.PatientID=att1.PatientID
and grpxatt.AttendDate=att1.AttendDate
WHERE
(CTPCodes = 'G0396' or CTPCodes = '90804' or CTPCodes = 'G0397' or
CTPCodes = '90806' or CTPCodes = '90849' or CTPCodes = 'H0005' or CTPCodes = '90853')
ORDER BY APGAttendance.[PatientID], APGAttendance.[AttendDate]
This needs testing against data to be certain that it produces the results you need and may need tweaking.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply