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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply