Query w/ Sub-Query... Boggles the Mind

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

General SQL Server Forums

New to SQL Server Programming

Query w/ Sub-Query... Boggles the Mind

Author

Topic

CTJohn23
Starting Member

2 Posts

Posted - 2009-12-17 : 11:45:44

SELECT tptf.PID, tptf.AppointmentDate, tptf.RecruitmentDate, tptf.VisitType, tptf.NextAppointmentDate, tptf.AppointmentShow, tptf.ID
FROM tptf
WHERE (((tptf.PID) In (SELECT [PID] FROM [tptf] As Tmp GROUP BY [PID],[AppointmentDate] HAVING Count(*)>1 And [AppointmentDate] = [tptf].[AppointmentDate])))
ORDER BY tptf.PID, tptf.AppointmentDate;

The purpose of this query is to find duplicate PIDS and AppointmentDates... there will be many PIDS, but should be only one Appointment Date per. Someone provided this query, way over my head. Specially the sub-query which compares the appointment dates from the first query and sub-query. Can someone break this down?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-12-17 : 11:59:17

hi try this

SELECT T.*
FROM
(
SELECT tptf.PID, tptf.AppointmentDate, tptf.RecruitmentDate, tptf.VisitType, tptf.NextAppointmentDate, tptf.AppointmentShow, tptf.ID,
ROW_NUMBER() OVER( PARTITION BY tptf.PID, tptf.AppointmentDate ORDER BY tptf.PID, tptf.AppointmentDate ) AS RowNum
FROM tptf
) T WHERE T.RowNum > 1
ORDER BY T.PID, T.AppointmentDate

CTJohn23
Starting Member

2 Posts

Posted - 2009-12-17 : 12:07:25

Well even more complex, but the above query does not work. The first query does work, but I have no understanding as to why it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources