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

No comments:

Post a Comment