merging select queries into one

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 select queries into one

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-14 : 01:03:49

hi,sorry for posting my topic once again.i am having three select queries which i want to make it in a single once.please help me out in making this.


SELECT     ET.EMR_TRANSACTION_ID, ET.TASK_ID, 'FINAL DIAGNOSIS' AS TASK_NAME, '0' AS NODE_DEFINITION_ID, 
           NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID, 
                       TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, 
		       TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,  
	               TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS  AS FRONTDESK_COMMENTS, 
                      '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, 
                      EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID,ECD.CONSULTANT_NAME, ET.ROLE_NAME, 
                      EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMREncounterDetails EED 
  LEFT JOIN EMRAppointmentDetailsHistory EA  ON EA.APPOINTMENT_ID = EED.APPOINTMENT_ID AND EA.APPOINTMENT_STATUS_ID = 2 
  LEFT JOIN EMRConsultantDetails ECD  ON ECD.CONSULTANT_ID = EED.CONSULTANT_ID 
  LEFT JOIN EMRTransactions ET  ON ET.ID_VALUE = EED.ENCOUNTER_ID 
  LEFT JOIN EMRPatientsMaster EPM ON EED.PATIENT_ID = EPM.PATIENT_ID
WHERE ET.TASK_ID = 68
union all
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, 'SAVED ENCOUNTER' AS TASK_NAME, '0' AS NODE_DEFINITION_ID, 
           NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
                         TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, 
			 TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME, 
			 TO_CHAR(EED.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID, EA.REASON || ' ' || EA.FRONTDESK_COMMENTS  AS FRONTDESK_COMMENTS,  
                      '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
                      EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME,
                      EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMREncounterDetails EED  
   LEFT JOIN EMRAppointmentDetailsHistory EA ON EA.APPOINTMENT_ID = EED.APPOINTMENT_ID AND EA.APPOINTMENT_STATUS_ID = 2 
   LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EED.CONSULTANT_ID 
   LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EED.ENCOUNTER_ID 
   LEFT JOIN EMRPatientsMaster EPM ON EED.PATIENT_ID = EPM.PATIENT_ID  
WHERE  ET.TASK_ID = 16 
union all
SELECT     ET.EMR_TRANSACTION_ID, ET.TASK_ID, 'SAVED DM  ENCOUNTER' AS TASK_NAME, '0' AS NODE_DEFINITION_ID, 
          NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID, 
                        TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,  
	   TO_CHAR(EED.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.FRONTDESK_COMMENTS, 
                      '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, 
                      EPM.PATIENT_ID,EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, 
                      EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMREncounterDetails EED 
  LEFT JOIN EMRAppointmentDetailsHistory EA  ON EA.APPOINTMENT_ID = EED.APPOINTMENT_ID AND EA.APPOINTMENT_STATUS_ID = 2 
  LEFT JOIN EMRConsultantDetails ECD  ON ECD.CONSULTANT_ID = EED.CONSULTANT_ID 
  LEFT JOIN EMRTransactions ET  ON ET.ID_VALUE = EED.ENCOUNTER_ID 
  LEFT JOIN EMRPatientsMaster EPM ON EED.PATIENT_ID = EPM.PATIENT_ID

WHERE ET.TASK_ID = 17

2 and 3 queries are identical only change is
in the first line task_name differs.

2,3 and change between 1 is task-name and at 3 line
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY')
and
TO_CHAR(EED.CREATED_DATE,'DD/MM/YYYY')

Sachin.Nand

2937 Posts

Posted - 2009-08-14 : 01:37:44

Cant u use a case statement
eg
case ET.TASK_ID when 68 then 'FINAL DIAGNOSIS'
when 16 then 'SAVED ENCOUNTER'
when 17 then 'SAVED DM ENCOUNTER'
end as TASK_NAME,
and put this condition in the where clause
where ET.TASK_ID in(68,16,17)
u can use the case condition for EA.CREATED_DATE to.

Ar you using SQL server because TO_CHAR is an oracle function.

PBUH

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-14 : 01:46:43

can you provide me the EA.CREATED_DATE also iam getting only 68,16 values please

Sachin.Nand

2937 Posts

Posted - 2009-08-14 : 01:54:04

case ET.TASK_ID when 68 then TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY')
when 16 then TO_CHAR(EED.CREATED_DATE,'DD/MM/YYYY')
when 17 then TO_CHAR(EED.CREATED_DATE,'DD/MM/YYYY')
end AS CREATED_DATE

Still dont know what is that TO_CHAR function.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources