Monday, March 18, 2013
LEFT OUTER JOIN
My Requirement is :
All employees have some value for TRN_DTL_CODE = '000003'
Only few employees have value for TRN_DTL_CODE = '000124'
I want the data view for the format of TRN_STARTDATE,TRN_ENDDATE,EMP_NUMBER, NSSF, VOL_NSSF
NSSF = Value of TRN_DTL_CODE = '000003'
VOL_NSSF = Value of TRN_DTL_CODE = '000124'
I used LEFT OUTER JOIN for create this.
CREATE VIEW VW_HS_PR_VOLUN_NSSF AS
SELECT A.TRN_STARTDATE AS TRN_STARTDATE, A.TRN_ENDDATE AS TRN_ENDDATE , A.EMP_NUMBER AS EMP_NUMBER,
A.TRN_PROC_EMP_AMT AS NSSF, ISNULL (B.TRN_PROC_EMP_AMT,0) AS VOL_NSSF FROM
(SELECT TRN_STARTDATE, TRN_ENDDATE , EMP_NUMBER, TRN_PROC_EMP_AMT
FROM HS_PR_PROCESSEDTXN WHERE TRN_DTL_CODE = '000003' ) A LEFT OUTER JOIN
(SELECT TRN_STARTDATE, TRN_ENDDATE , EMP_NUMBER, TRN_PROC_EMP_AMT
FROM HS_PR_PROCESSEDTXN WHERE TRN_DTL_CODE = '000124' ) B
ON
A.EMP_NUMBER = B.EMP_NUMBER AND
A.TRN_STARTDATE = B.TRN_STARTDATE AND
A.TRN_ENDDATE = B.TRN_ENDDATE
Subscribe to:
Posts (Atom)