view to be split into multiple views

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 split into multiple views

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-08-19 : 01:23:34

[code]
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
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, ET.NODE_DEFINITION_ID, ET.ID_VALUE, ET.FLOW_ID, EA.APPOINTMENT_DATE,
EA.APPOINTMENT_TIME, EA.CREATED_DATE, EA.APPOINTMENT_ID, EA.FRONTDESK_COMMENTS, EAL.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, ETN.NAME, EA.CONSULTANT_ID, ECD.CONSULTANT_NAME,
ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, Isnull(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRAPPOINTMENTDETAILS EA, EMRPATIENTSMASTER EPM,
EMRAPPOINTMENTSTATUSLKUP EAL, EMRTEMPNODEDETAILS ETN, EMRCONSULTANTDETAILS ECD
WHERE ETL.TASK_ID = ET.TASK_ID AND EA.APPOINTMENT_ID = ET.ID_VALUE AND EPM.PATIENT_ID = EA.PATIENT_ID AND
EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_STATUS_ID AND ETN.NODE_DEFINITION_ID = ET.NODE_DEFINITION_ID
AND ET.TASK_ID IN (3,28) AND EA.CONSULTANT_ID = ECD.CONSULTANT_ID
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, ET.NODE_DEFINITION_ID, ET.ID_VALUE, ET.FLOW_ID,
EA.APPOINTMENT_DATE, EA.APPOINTMENT_TIME, EA.CREATED_DATE, EA.APPOINTMENT_ID, EA.REASON + ' ' + EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS,
EAL.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, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID,
Isnull(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID,
CASE WHEN ACTIVECHECK.PATIENT_ID IS NULL THEN 0
ELSE 1
END AS IS_ACTIVE
FROM EMRAPPOINTMENTDETAILSHISTORY EA
LEFT JOIN EMRTRANSACTIONS ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPATIENTSMASTER EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAPPOINTMENTSTATUSLKUP EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRCONSULTANTDETAILS ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
LEFT JOIN EMRTASKLISTLKUP ETL ON ETL.TASK_ID = ET.TASK_ID
LEFT JOIN ( SELECT PATIENT_ID AS PATIENT_ID,ID_VALUE AS ID_VALUE FROM EMRTRANSACTIONS WHERE TASK_ID=108)
ACTIVECHECK ON ACTIVECHECK.PATIENT_ID = ET.PATIENT_ID AND ACTIVECHECK.ID_VALUE = ET.ID_VALUE
WHERE ET.TASK_ID IN (9,26) AND EA.APPOINTMENT_STATUS_ID = 2

[/code]

hi sorry for posting this once again.can anyone suggest me how to split this view into n number of views based on user role_name
ET.ROLE_NAME is the column where having values like A,B,C,D----
i want all A into one.please give an idea by showing an example

manieverster
Starting Member

5 Posts

Posted - 2009-08-19 : 08:15:53

The only thing I can suggest to you is to keep this viwew as is and create new views using this one to select from. when you create a script to create new views just use this one in the from clause or if you wantto create a view using the design feature then when you click the add table button the dialog box have a tab where you can choose views.

example of script:
CREATE VIEW View1
AS
SELECT * FROM emrVWTodaysTaskList WHERE rolename = 'A'

Manie Verster
Developer - SQL and ASP.Net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources