view to be merged using one select query

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)

view to be merged using one select query

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-11 : 05:01:51

hi i want view with two select queries into one query hoe can i do that.help me out in this.



CREATE VIEW emrVWTodaysTaskList  
AS    
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME, '0' AS NODE_DEFINITION_ID, '0' AS ID_VALUE, '0' AS FLOW_ID,   
		'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID,   
		'' AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB,EPM.PATIENT_HOMEPHONE,   
		EPM.PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME,   
		'' AS CONSULTANT_ID, '' AS CONSULTANT_NAME,  ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID,   
		ISNULL(GEA.IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 0 AS IS_ACTIVE  
FROM  EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM ,EMRTRANSACTIONS ET,(SELECT MAX(IS_EXPRESS) AS IS_EXPRESS,PATIENT_ID
 FROM EMRAPPOINTMENTDETAILS EA LEFT JOIN EMRSERVICEITEMS SRV ON EA.TP_ITEM_ID=SRV.TP_ITEM_DEAL_ID 
 WHERE EA.IS_LAB_APMT != 1 AND EA.APPOINTMENT_DATE<=(SELECT SERVER_DATE FROM EMRSERVERDATE) GROUP BY EA.PATIENT_ID ) GEA ,EMRPatientTaskInfo PTI 
WHERE  ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (1,2) AND ET.PATIENT_ID = GEA.PATIENT_ID
       AND ET.PATIENT_ID = PTI.PATIENT_ID AND PTI.TASK_ID IN (1,2) AND PTI.TASK_COUNT>1  
UNION ALL 
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME, '0' AS NODE_DEFINITION_ID, '0' AS ID_VALUE, '0' AS FLOW_ID,   
		EAD.APPOINTMENT_DATE, EAD.APPOINTMENT_TIME, EAD.CREATED_DATE, EAD.APPOINTMENT_ID,   
		EAD.FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB,EPM.PATIENT_HOMEPHONE,   
		EPM.PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME,   
		EAD.CONSULTANT_ID, ECD.CONSULTANT_NAME,  ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID,   
		ISNULL(GEA.IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE 
FROM  EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM ,EMRTRANSACTIONS ET,(SELECT MAX(IS_EXPRESS) AS IS_EXPRESS,PATIENT_ID
 FROM EMRAPPOINTMENTDETAILS EA LEFT JOIN EMRSERVICEITEMS SRV ON EA.TP_ITEM_ID=SRV.TP_ITEM_DEAL_ID 
WHERE EA.IS_LAB_APMT != 1 AND EA.APPOINTMENT_DATE<=(SELECT SERVER_DATE FROM EMRSERVERDATE) GROUP BY EA.PATIENT_ID ) GEA,
 EMRAPPOINTMENTDETAILS EAD,EMRCONSULTANTDETAILS ECD,EMRPatientTaskInfo PTI WHERE  ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID 
AND ET.TASK_ID IN (1,2)AND ET.PATIENT_ID = GEA.PATIENT_ID AND ET.PATIENT_ID = EAD.PATIENT_ID AND ECD.CONSULTANT_ID = EAD.CONSULTANT_ID 
AND ET.PATIENT_ID = PTI.PATIENT_ID AND PTI.TASK_ID IN (1,2) AND PTI.TASK_COUNT=1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-11 : 05:03:54

What is the problem with that view?

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-11 : 05:05:29

NO using case condition they asjked to merge but iam not well aware of it.please help out

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-11 : 05:10:49

quote:
Originally posted by rajasekhar857

NO using case condition they asjked to merge but iam not well aware of it.please help out

You always give less information
Where do you want to use CASE?
Do you want to remove UNION ALL?

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-11 : 05:13:27

where ever both the field vary like
for example

SELECT DISTINCT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME, '0' AS NODE_DEFINITION_ID, '0' AS ID_VALUE, '0' AS FLOW_ID,
(Case When PTI.TASK_COUNT>1 Then '1/1/1900'
When PTI.TASK_COUNT=1 Then EAD.APPOINTMENT_DATE End) AS APPOINTMENT_DATE,
(Case when PTI.TASK_COUNT>1 Then '12:00:00'
When PTI.TASK_COUNT=1 Then EAD.APPOINTMENT_TIME End) AS APPOINTMENT_TIME,
(Case When PTI.TASK_COUNT>1 Then '1/1/1900'
When PTI.TASK_COUNT=1 Then EAD.CREATED_DATE End) AS APPOINTMENT_DATE,

-------------
but not sure

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-11 : 05:19:19

The approach is correct
Do it for all the columns

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-11 : 05:33:41

[code]
SELECT
EMR_TRANSACTION_ID,
ETL.TASK_ID,
TASK_NAME,
'0' AS NODE_DEFINITION_ID,
'0' AS ID_VALUE,
'0' AS FLOW_ID,
CASE WHEN PTI.TASK_COUNT = 1
THEN EAD.APPOINTMENT_DATE
ELSE '19000101'
END AS APPOINTMENT_DATE,
CASE WHEN PTI.TASK_COUNT = 1
THEN EAD.APPOINTMENT_TIME
ELSE '12:00:00'
END AS APPOINTMENT_TIME,
CASE WHEN PTI.TASK_COUNT = 1
THEN EAD.CREATED_DATE
ELSE '19000101'
END AS CREATED_DATE,
CASE WHEN PTI.TASK_COUNT = 1
THEN EAD.APPOINTMENT_ID
ELSE '0'
END AS APPOINTMENT_ID,
CASE WHEN PTI.TASK_COUNT = 1
THEN EAD.FRONTDESK_COMMENTS
ELSE ''
END AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME,
EPM.PATIENT_LASTNAME,
EPM.PATIENT_DOB,
EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID,
'' AS PATIENT_TYPE,
'' AS PATIENT_GENDER,
'' AS RACE_ETHNICITY_ID,
'' AS MARITAL_STATUS,
'' AS NAME,
CASE WHEN PTI.TASK_COUNT = 1
THEN EAD.CONSULTANT_ID
ELSE ''
END AS CONSULTANT_ID,
CASE WHEN PTI.TASK_COUNT = 1
THEN ECD.CONSULTANT_NAME
ELSE ''
END AS CONSULTANT_NAME,
ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID,
EPM.REGISTER_ID AS REGISTER_ID,
ISNULL(GEA.IS_EXPRESS,0) AS IS_EXPRESS,
0 AS LAB_REQUEST_ID,
0 AS SPECIMEN_ID ,
0 AS IS_ACTIVE
FROM
EMRTRANSACTIONS ET
INNER JOIN EMRTASKLISTLKUP ETL ON ET.TASK_ID = ETL.TASK_ID
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN
(
SELECT MAX(IS_EXPRESS) AS IS_EXPRESS,
PATIENT_ID
FROM EMRAPPOINTMENTDETAILS EA
LEFT JOIN EMRSERVICEITEMS SRV ON EA.TP_ITEM_ID=SRV.TP_ITEM_DEAL_ID
WHERE EA.IS_LAB_APMT <> 1
AND EA.APPOINTMENT_DATE <= (SELECT SERVER_DATE FROM EMRSERVERDATE)
GROUP BY EA.PATIENT_ID
) GEA ON ET.PATIENT_ID = GEA.PATIENT_ID
INNER JOIN EMRPatientTaskInfo PTI ON ET.PATIENT_ID = PTI.PATIENT_ID
LEFT JOIN EMRAPPOINTMENTDETAILS EAD ON ET.PATIENT_ID = EAD.PATIENT_ID
LEFT JOIN EMRCONSULTANTDETAILS ECD ON EAD.CONSULTANT_ID = ECD.CONSULTANT_ID
WHERE ET.TASK_ID IN (1,2)
AND PTI.TASK_ID IN (1,2)
AND PTI.TASK_COUNT >= 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-11 : 07:08:05

using your query iam getting 476 rows as my both select queries giving 230 rows.why

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-11 : 07:24:14

iits working thank you distinct missed in select so what difference

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources