query help - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-01-11 : 06:12:48

hi my query is like this


SELECT EAD.APPOINTMENT_ID,EAD.REFERED_BY,EAD.REASON,ECS.SPECIALTY_NAME,ECD.CONSULTANT_NAME,ESA.APPOINTMENT_TYPE,USERS.USER_NAME, EVT.VISIT_TYPE, rtrim(ltrim(convert(varchar,EAD.APPOINTMENT_DATE,110))) AS APPOINTMENT_DATE, rtrim(ltrim(replace(replace(substring(convert(varchar,EAD.APPOINTMENT_TIME,0),13,7),'AM',' AM'),'PM',' PM'))) AS APPOINTMENT_TIME, EAD.FRONTDESK_COMMENTS,EAD.APPOINTMENT_STATUS_ID FROM EMRAppointmentDetails EAD 
INNER JOIN USERS ON EAD.USER_LOGIN = USERS.USER_LOGIN 
INNER JOIN EMRConsultantDetails ECD ON  EAD.CONSULTANT_ID = ECD.CONSULTANT_ID 
INNER JOIN EMRVisitTypeLkup EVT ON EAD.VISIT_TYPE_ID = EVT.VISIT_TYPE_ID 
LEFT OUTER JOIN EMRSchdlrAppointmentTypes ESA ON EAD.APPOINTMENT_TYPE_ID = ESA.APPOINTMENT_TYPE_ID  
LEFT OUTER JOIN EMRConsultantSpecialtiesLkup ECS ON EAD.SPECIALTY_ID = ECS.SPECIALTY_ID 
WHERE EAD.PATIENT_ID ='2219' AND EAD.APPOINTMENT_STATUS_ID = 3 
UNION ALL 
SELECT EAD.APPOINTMENT_ID,EAD.REFERED_BY,EAD.REASON,ECS.SPECIALTY_NAME,ECD.CONSULTANT_NAME,ESA.APPOINTMENT_TYPE,USERS.USER_NAME,EVT.VISIT_TYPE,  rtrim(ltrim(convert(varchar,EAD.APPOINTMENT_DATE,110))) AS APPOINTMENT_DATE, rtrim(ltrim(replace(replace(substring(convert(varchar,EAD.APPOINTMENT_TIME,0),13,7),'AM',' AM'),'PM',' PM'))) AS APPOINTMENT_TIME,EAD.FRONTDESK_COMMENTS,EAD.APPOINTMENT_STATUS_ID FROM EMRAppointmentDetailsHistory EAD 
INNER JOIN USERS ON EAD.USER_LOGIN = USERS.USER_LOGIN 
INNER JOIN EMRConsultantDetails ECD ON EAD.CONSULTANT_ID = ECD.CONSULTANT_ID  
INNER JOIN EMRVisitTypeLkup EVT ON EAD.VISIT_TYPE_ID = EVT.VISIT_TYPE_ID 
LEFT OUTER JOIN EMRSchdlrAppointmentTypes ESA ON EAD.APPOINTMENT_TYPE_ID = ESA.APPOINTMENT_TYPE_ID 
LEFT OUTER JOIN EMRConsultantSpecialtiesLkup ECS ON EAD.SPECIALTY_ID = ECS.SPECIALTY_ID WHERE EAD.PATIENT_ID = '2219' AND 
EAD.APPOINTMENT_STATUS_ID = 3 
order by  APPOINTMENT_DATE DESC, APPOINTMENT_TIME DESC

in the above query if EAD.APPOINTMENT_ID and EAD.APPOINTMENT_STATUS_ID FROM EMRAppointmentDetails AND
EMRAppointmentDetailsHistory Matches same records then row shoud come from EMRAppointmentDetails table only.how to add condition in second query.please suggest me

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-01-11 : 06:53:02

hi if columns APPOINTMENT_ID and APPOINTMENT_STATUS_ID are for example 2109 and 3 in both tables then i need it from only
EMRAppointmentDetails table how to do this.help me out

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-11 : 08:52:00

Your code is sort of messy, but from I got out of it you want uniqueness to your rows, if you change UNION ALL to UNION you will remove duplicate rows of data.

Edit: You may also have to try joining the 2nd Select statement to the first table where NOT(AppointmentID = AppointmentIDHistory AND AppointmentStatus = AppointmentStatusHistory)

mikr0s
Starting Member

1 Post

Posted - 2010-01-11 : 09:37:05

SELECT APPOINTMENT_ID, REFERED_BY, REASON, SPECIALTY_NAME, CONSULTANT_NAME, APPOINTMENT_TYPE, USER_NAME, VISIT_TYPE, APPOINTMENT_DATE, APPOINTMENT_TIME, FRONTDESK_COMMENTS, APPOINTMENT_STATUS_ID
FROM
(
SELECT *, DENSE_RANK() OVER (PARTITION BY APPOINTMENT_ID, APPOINTMENT_STATUS_ID ORDER BY source) AS ranking
FROM
(
SELECT EAD.APPOINTMENT_ID,EAD.REFERED_BY,EAD.REASON,ECS.SPECIALTY_NAME,ECD.CONSULTANT_NAME,ESA.APPOINTMENT_TYPE,USERS.USER_NAME, EVT.VISIT_TYPE, rtrim(ltrim(convert(varchar,EAD.APPOINTMENT_DATE,110))) AS APPOINTMENT_DATE, rtrim(ltrim(replace(replace(substring(convert(varchar,EAD.APPOINTMENT_TIME,0),13,7),'AM',' AM'),'PM',' PM'))) AS APPOINTMENT_TIME, EAD.FRONTDESK_COMMENTS,EAD.APPOINTMENT_STATUS_ID, 1 as source
FROM EMRAppointmentDetails EAD
INNER JOIN USERS ON EAD.USER_LOGIN = USERS.USER_LOGIN
INNER JOIN EMRConsultantDetails ECD ON EAD.CONSULTANT_ID = ECD.CONSULTANT_ID
INNER JOIN EMRVisitTypeLkup EVT ON EAD.VISIT_TYPE_ID = EVT.VISIT_TYPE_ID
LEFT OUTER JOIN EMRSchdlrAppointmentTypes ESA ON EAD.APPOINTMENT_TYPE_ID = ESA.APPOINTMENT_TYPE_ID
LEFT OUTER JOIN EMRConsultantSpecialtiesLkup ECS ON EAD.SPECIALTY_ID = ECS.SPECIALTY_ID
WHERE EAD.PATIENT_ID ='2219' AND EAD.APPOINTMENT_STATUS_ID = 3
UNION ALL
SELECT EAD.APPOINTMENT_ID,EAD.REFERED_BY,EAD.REASON,ECS.SPECIALTY_NAME,ECD.CONSULTANT_NAME,ESA.APPOINTMENT_TYPE,USERS.USER_NAME,EVT.VISIT_TYPE, rtrim(ltrim(convert(varchar,EAD.APPOINTMENT_DATE,110))) AS APPOINTMENT_DATE, rtrim(ltrim(replace(replace(substring(convert(varchar,EAD.APPOINTMENT_TIME,0),13,7),'AM',' AM'),'PM',' PM'))) AS APPOINTMENT_TIME,EAD.FRONTDESK_COMMENTS,EAD.APPOINTMENT_STATUS_ID, 2 as source
FROM EMRAppointmentDetailsHistory EAD
INNER JOIN USERS ON EAD.USER_LOGIN = USERS.USER_LOGIN
INNER JOIN EMRConsultantDetails ECD ON EAD.CONSULTANT_ID = ECD.CONSULTANT_ID
INNER JOIN EMRVisitTypeLkup EVT ON EAD.VISIT_TYPE_ID = EVT.VISIT_TYPE_ID
LEFT OUTER JOIN EMRSchdlrAppointmentTypes ESA ON EAD.APPOINTMENT_TYPE_ID = ESA.APPOINTMENT_TYPE_ID
LEFT OUTER JOIN EMRConsultantSpecialtiesLkup ECS ON EAD.SPECIALTY_ID = ECS.SPECIALTY_ID WHERE EAD.PATIENT_ID = '2219' AND
EAD.APPOINTMENT_STATUS_ID = 3
) AS the_data
) AS rank_table
WHERE ranking = 1
ORDER BY APPOINTMENT_DATE DESC, APPOINTMENT_TIME DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources