MERGING INTO ONE - 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)

MERGING INTO ONE

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-11-03 : 01:43:21

[code]
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,
ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRAppDetailsSuppHistory EA ON ET.ID_VALUE = EA.APPOINTMENT_ID
WHERE ET.TASK_ID IN (9,112)
UNION ALL
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,
ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRAppointmentDetails EA ON ET.ID_VALUE = EA.APPOINTMENT_ID
WHERE ET.TASK_ID IN (28)
UNION ALL
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME,
'1/1/1900' AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,
ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
WHERE ET.TASK_ID IN (4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 18, 19, 20, 21, 22, 23, 24, 28,
29, 30, 31, 32, 33, 58, 59, 60, 63, 64, 65, 66, 67, 96, 110, 113, 115, 116, 117, 118, 119, 120, 121, 123, 125, 150)

[/code]

how to merge all 3 select query into one select statement.help me in this

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 01:58:11

Are you posting ORACLE question here?

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-11-03 : 03:18:21

sorry for that but please help me in merging into a single select query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 04:15:10

Try this

SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 case when ET.TASK_ID IN (9,112,28) TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY')  ELSE '1/1/1900' END AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL 
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID 
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID 
INNER JOIN EMRAppointmentDetails EA ON 
(
	ET.ID_VALUE = EA.APPOINTMENT_ID  and  ET.TASK_ID IN (9,112,28)
	OR
	ET.TASK_ID IN (4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 18, 19, 20, 21, 22, 23, 24, 28, 
	29, 30, 31, 32, 33, 58, 59, 60, 63, 64, 65, 66, 67, 96, 110, 113, 115, 116, 117, 118, 119, 120, 121, 123, 125, 150)

)

WHERE   ET.TASK_ID IN (4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 18, 19, 20, 21, 22, 23, 24, 28, 
29, 30, 31, 32, 33, 58, 59, 60, 63, 64, 65, 66, 67, 96, 110, 113, 115, 116, 117, 118, 119, 120, 121, 123, 125, 150,9,112,28)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-13 : 05:10:58

Did it help?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources