QUERY PROBLEM - SQL Server Forums

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)

QUERY PROBLEM

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-06-09 : 07:14:03

Hi my query is like this,


SELECT  Case When isnull(EA.APPOINTMENT_OWNER,'NULL')='NULL' then  ET.USER_LOGIN 
	    ELSE 
	    CASE WHEN isnull(EA.APPOINTMENT_OWNER,'PHYSICIAN ASSISTANT')='PHYSICIAN ASSISTANT' THEN ET.USER_LOGIN
	    ELSE 
        EA.APPOINTMENT_OWNER END END AS CONSULTANT_ID,
        Case When isnull(EA.APPOINTMENT_OWNER,'NULL')='NULL' then  ECD.CONSULTANT_NAME 
	    ELSE
	    CASE WHEN isnull(EA.APPOINTMENT_OWNER,'PHYSICIAN ASSISTANT')='PHYSICIAN ASSISTANT' THEN ECD.CONSULTANT_NAME
	    ELSE USR.USER_NAME END END AS CONSULTANT_NAME,
        ET.ROLE_NAME,EURL.ROLE_NAME                           
FROM    EMREncounterDetails EED                           
        LEFT JOIN EMRAppDetailsSuppHistory 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 EMRPATransactions ET ON ET.ID_VALUE = EED.ENCOUNTER_ID                           
        LEFT JOIN EMRPatientsMaster EPM ON EED.PATIENT_ID = EPM.PATIENT_ID                          
        LEFT JOIN EMRTransferredPatients ETP ON ETP.PATIENT_ID = EPM.PATIENT_ID AND ETP.STATUS = 1 AND                          
        EED.ENCOUNTER_ID = ETP.ENCOUNTER_ID 
        LEFT OUTER JOIN EMRuserrolelocation EURL ON EURL.USER_LOGIN = EA.APPOINTMENT_OWNER AND EURL.location_id = EPM.location_id
        LEFT OUTER JOIN USERS USR ON EURL.USER_LOGIN = USR.USER_LOGIN AND USR.USER_STATUS = 'active'
WHERE   ET.TASK_ID = 16

data coming like this

1502_vikram Vikram PHYSICIAN ASSISTANT PHYSICIAN ASSISTANT
1502_vikram Vikram PHYSICIAN ASSISTANT ACCOUNTS
1502_kiran Kiran PHYSICIAN ASSISTANT NULL
1507_arun Arun PHYSICIAN ASSISTANT NULL
1507_arun Arun PHYSICIAN ASSISTANT NULL
1507_rajan Rajan Desai PHYSICIAN ASSISTANT NULL
1507_arun Arun PHYSICIAN ASSISTANT NULL

some where case condition failing can u help me a bit in this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-09 : 07:34:03

[code]SELECT CASE
WHEN EA.APPOINTMENT_OWNER IS NULL THEN ET.USER_LOGIN
WHEN EA.APPOINTMENT_OWNER = 'PHYSICIAN ASSISTANT' THEN ET.USER_LOGIN
ELSE EA.APPOINTMENT_OWNER
END AS CONSULTANT_ID,
CASE
WHEN EA.APPOINTMENT_OWNER IS NULL THEN ECD.CONSULTANT_NAME
WHEN EA.APPOINTMENT_OWNER = 'PHYSICIAN ASSISTANT' THEN ECD.CONSULTANT_NAME
ELSE USR.USER_NAME
END AS CONSULTANT_NAME,
ET.ROLE_NAME,
EURL.ROLE_NAME
FROM EMREncounterDetails AS EED
INNER JOIN EMRPATransactions AS ET ON ET.ID_VALUE = EED.ENCOUNTER_ID
LEFT JOIN EMRAppDetailsSuppHistory AS EA ON EA.APPOINTMENT_ID = EED.APPOINTMENT_ID
AND EA.APPOINTMENT_STATUS_ID = 2
LEFT JOIN EMRConsultantDetails AS ECD ON ECD.CONSULTANT_ID = EED.CONSULTANT_ID
LEFT JOIN EMRPatientsMaster AS EPM ON EED.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRTransferredPatients AS ETP ON ETP.PATIENT_ID = EPM.PATIENT_ID
AND ETP.STATUS = 1
AND EED.ENCOUNTER_ID = ETP.ENCOUNTER_ID
LEFT JOIN EMRuserrolelocation AS EURL ON EURL.USER_LOGIN = EA.APPOINTMENT_OWNER
AND EURL.location_id = EPM.location_id
LEFT JOIN USERS AS USR ON EURL.USER_LOGIN = USR.USER_LOGIN
AND USR.USER_STATUS = 'active'
WHERE ET.TASK_ID = 16[/code]

N 56°04'39.26"
E 12°55'05.63"

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-06-09 : 07:41:05

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources