Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 merging select queries into one
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 08/14/2009 :  01:03:49  Show Profile  Reply with Quote
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')

Edited by - rajasekhar857 on 08/14/2009 01:06:01

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 08/14/2009 :  01:37:44  Show Profile  Reply with Quote
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
Go to Top of Page

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 08/14/2009 :  01:46:43  Show Profile  Reply with Quote
can you provide me the EA.CREATED_DATE also iam getting only 68,16 values please
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 08/14/2009 :  01:54:04  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000