hi can anyone help me out in this view to split it into multiple views based on user role_name like for example a,b,c,d,e,fall a to be in one view and so on.my view is like this.column name based is ET.ROLE_NAME--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_ACTIVEFROM 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 PTIWHERE 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>1UNION ALLSELECT 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_ACTIVEFROM 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_IDWHERE 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_IDAND 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_IDAND ET.PATIENT_ID = PTI.PATIENT_ID AND PTI.TASK_ID IN (1,2) AND PTI.TASK_COUNT=1UNION ALLSELECT 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_ACTIVEFROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRAPPOINTMENTDETAILS EA, EMRPATIENTSMASTER EPM, EMRAPPOINTMENTSTATUSLKUP EAL, EMRTEMPNODEDETAILS ETN, EMRCONSULTANTDETAILS ECDWHERE 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_IDUNION ALLSELECT 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_ACTIVEFROM 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_VALUEWHERE ET.TASK_ID IN (9,26) AND EA.APPOINTMENT_STATUS_ID = 2UNION ALLSELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, EEC.ECHART_ID 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, EEC.CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.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_ACTIVEFROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM, EMRECHARTS EECWHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.PATIENT_ID = EEC.PATIENT_ID AND EEC.CONSULTANT_ID = ET.USER_LOGIN AND ET.ID_VALUE = EEC.ECHART_ID AND EEC.VERIFICATION_REQD = 1 AND ET.TASK_ID = 10UNION ALLSELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, Isnull(ET.ID_VALUE, '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, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EPM.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_ACTIVEFROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM, EMRCONSULTANTDETAILS ECDWHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 11 AND ECD.CONSULTANT_ID = ET.USER_LOGINUNION ALLSELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, Isnull(ET.ID_VALUE, '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, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.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_ACTIVEFROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM, EMRPATIENTNOTES EPNWHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND EPN.NOTES_ID = ET.ID_VALUE AND ET.TASK_ID IN (12,13)UNION ALLSELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, Isnull(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID, Isnull(EED.APPOINTMENT_DATE, '1/1/1900') AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, Isnull(EED.CREATED_DATE, '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, EED.CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.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_ACTIVEFROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM, EMRENCOUNTERDETAILS EED, EMRFOLLOWUPAPPOINTMENTS EFAWHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.ID_VALUE = EFA.FOLLOWUP_ID AND EFA.ENCOUNTER_ID = EED.ENCOUNTER_ID AND ET.TASK_ID = 14UNION ALLSELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, ET.ID_VALUE, '0' AS FLOW_ID, EED.APPOINTMENT_DATE, NULL AS APPOINTMENT_TIME, EED.CREATED_DATE, EED.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, EED.CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.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_ACTIVEFROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM, EMRTRANSCRIPTIONMAPPING ETM, EMRENCOUNTERDETAILS EED, EMRTRANSCRIPTIONDATA ETDWHERE ETL.TASK_ID = ET.TASK_ID AND ET.ID_VALUE = ETM.TRANSCRIPTION_ID AND ETM.ENCOUNTER_ID = EED.ENCOUNTER_ID AND ETD.IS_VERIFIED = 0 AND ETD.TRANSCRIPTION_ID = ETM.TRANSCRIPTION_ID AND EED.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 15UNION ALLSELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, CASE ET.TASK_ID WHEN 68 THEN 'FINAL DIAGNOSIS' WHEN 16 THEN 'SAVED ENCOUNTER' WHEN 17 THEN 'SAVED DM ENCOUNTER' WHEN 25 THEN 'ENCOUNTER' END AS TASK_NAME, '0' AS NODE_DEFINITION_ID, Isnull(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID, EA.APPOINTMENT_DATE, EA.APPOINTMENT_TIME, CASE ET.TASK_ID WHEN 68 THEN EA.CREATED_DATE WHEN 16 THEN EED.CREATED_DATE WHEN 17 THEN EED.CREATED_DATE WHEN 25 THEN EA.CREATED_DATE END AS CREATED_DATE, EA.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, Isnull(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVEFROM 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_IDWHERE ET.TASK_ID IN (16,17,25,68)UNION ALLSELECT 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, EPM.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_ACTIVEFROM 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 LEFT JOIN EMRTASKLISTLKUP ETL ON ETL.TASK_ID = ET.TASK_ID LEFT JOIN EMRAPPOINTMENTSTATUSLKUP EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_IDWHERE ET.TASK_ID = 27UNION ALLSELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, Isnull(ET.ID_VALUE, '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, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EPM.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_ACTIVEFROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM, EMRCONSULTANTDETAILS ECDWHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 32 AND ECD.CONSULTANT_ID = ET.USER_LOGINUNION ALLSELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, Isnull(ET.ID_VALUE, '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, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME, NULL AS PATIENT_DOB, NULL AS PATIENT_HOMEPHONE, NULL AS PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, NULL AS LOCATION_ID, '' AS REGISTER_ID, Isnull(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 0 AS IS_ACTIVEFROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, USERS USWHERE ETL.TASK_ID = ET.TASK_ID AND ET.USER_LOGIN = US.USER_LOGIN AND ET.TASK_ID IN (34, 61, 62, 77, 80)UNION ALLSELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, ESF.SCREENING_DOC_ID 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, ESD.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.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_ACTIVEFROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM, EMRPATIENTSCREENINGFILES ESF, EMRPATIENTSCREENINGDETAILS ESDWHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.PATIENT_ID = ESD.PATIENT_ID AND ESD.USER_LOGIN = ET.USER_LOGIN AND ET.ID_VALUE = ESF.SCREENING_DOC_ID AND ESF.VERIFICATION_REQUIRED = 1 AND ESF.SCREENING_ID = ESD.SCREENING_ID AND ET.TASK_ID = 65UNION ALLSELECT 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(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVEFROM EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM ,EMRTRANSACTIONS ET,EMRPATIENTTASKINFO PTIWHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (69,70)AND ET.PATIENT_ID = PTI.PATIENT_ID AND PTI.TASK_ID IN (69,70) AND PTI.TASK_COUNT>1UNION ALLSELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME, '0' AS NODE_DEFINITION_ID, ID_VALUE, '0' AS FLOW_ID, EAD.APPOINTMENT_DATE, EAD.APPOINTMENT_TIME, EAD.CREATED_DATE, EAD.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, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EPM.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_ACTIVEFROM EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM ,EMRTRANSACTIONS ET,EMRPATIENTTASKINFO PTI, EMRAPPOINTMENTDETAILSHISTORY EAD ,EMRCONSULTANTDETAILS ECDWHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (69,70)AND ET.PATIENT_ID = PTI.PATIENT_ID AND PTI.TASK_ID IN (69,70) AND PTI.TASK_COUNT=1AND EAD.APPOINTMENT_ID=ET.ID_VALUE AND EAD.APPOINTMENT_STATUS_ID=2 AND ECD.CONSULTANT_ID = EAD.CONSULTANT_IDUNION ALLSELECT DISTINCT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, Isnull(ET.ID_VALUE, '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, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME, NULL AS PATIENT_DOB, NULL AS PATIENT_HOMEPHONE, NULL AS PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, ECT.LOCATION AS LOCATION_ID, '' AS REGISTER_ID, Isnull(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVEFROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, USERS US, EMRCONSULTANTTEMP ECTWHERE ETL.TASK_ID = ET.TASK_ID AND ET.USER_LOGIN = US.USER_LOGIN AND ECT.GROUP_NAME = US.USER_LOGIN AND ET.TASK_ID IN (74)UNION ALLSELECT DISTINCT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, Isnull(ET.ID_VALUE, '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, ELEL.EQUIPMENT_NAME AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME AS PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME AS PATIENT_LASTNAME, EPM.PATIENT_DOB AS PATIENT_DOB, EPM.PATIENT_HOMEPHONE AS PATIENT_HOMEPHONE, EPM.PATIENT_ID AS PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, Isnull(IS_EXPRESS,0) AS IS_EXPRESS, ELRM.LAB_REQUEST_ID, ELRM.SPECIMEN_ID, 0 AS IS_ACTIVEFROM EMRSUPPLEMENTARYTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM, EMRLABREQUEST ELRMINNER JOIN EMRLABREQUESTDETAILS ELRD ON ELRM.LAB_REQUEST_ID=ELRD.LAB_REQUEST_IDLEFT OUTER JOIN EMRTESTEQUIPMENTS ETE ON (ETE.TEST_ID=ELRD.TEST_ID OR ETE.LAB_PROFILE_ID=ELRD.LAB_PROFILE_ID) LEFT OUTER JOINEMRLABEQUIPMENTSLKUP ELEL ON ELEL.EQUIPMENT_ID=ETE.EQUIPMENT_IDWHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.ID_VALUE = ELRM.LAB_REQUEST_ID ANDET.TASK_ID IN(72,81,82,83,87,88,97,98)UNION ALLSELECT DISTINCT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, Isnull(ET.ID_VALUE, '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, EQUIPMENT_NAME AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME AS PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME AS PATIENT_LASTNAME, EPM.PATIENT_DOB AS PATIENT_DOB, EPM.PATIENT_HOMEPHONE AS PATIENT_HOMEPHONE, EPM.PATIENT_ID AS PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, Isnull(IS_EXPRESS,0) AS IS_EXPRESS, LR.LAB_REQUEST_ID, LR.SPECIMEN_ID , 0 AS IS_ACTIVEFROM EMRSUPPLEMENTARYTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM, EMRLABTRANSACTIONS ELTD INNER JOIN EMRLABEQUIPMENTSLKUP ELEL ON ELEL.EQUIPMENT_ID=ELTD.EQUIPMENT_IDINNER JOIN EMRLABREQUESTDETAILS LRD ON ELTD.LAB_REQUEST_DETAILS_ID = LRD.LAB_REQUEST_DETAILS_IDINNER JOIN EMRLABREQUEST LR ON LRD.LAB_REQUEST_ID = LR.LAB_REQUEST_IDWHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.ID_VALUE = ELTD.LAB_TRANSACTION_IDAND ET.TASK_ID IN (84,85,86,89,90,99,100)UNION ALLSELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, Isnull(ET.ID_VALUE, '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, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME AS ROLE_NAME, EPM.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_ACTIVEFROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPMWHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN(4,5,8,18,19,20,21,22,23,24,29,30,31,32,33,36,58,60,63,64,67,71,73,76,79,91,92,93,94,102,103,104,105,106,114,127)GO
help required in large view
Posted - 2009-08-17 : 00:40:12
