I’m attempting to use a Left Join to pull information from a table that contains my objectives and place in with information from a Summary table. I use one where clause per Left Join, that defines which rows from the objective table I want that left join to represent (for instance “(mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL )” ). I have 7 left joins and where statements.The problem I’m having is that the where clause are removing all rows where the Left Join does not result in a match, despite the fact that I have the OR IS NULL in the where clause.Can anyone help!The full SQL is as follows:SELECT DATE_DAY , SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , FORCE_STATUS , NUM_CREATED , NUM_OPEN , NUM_AWAITING_DISPATCH , NUM_OPEN_IN_DUE_DATE , NUM_DISPATCH_TO , DURATION_DISPATCH_TO , SLA_DURATION_DISPATCH_TO , SLA_DURATION_DISPATCH_TO_G_L , MET_SLA_DISPATCH_TO , mdt.SLA AS SLA_MET_DISPATCH_TO , mdt.SLA_G_L AS SLA_MET_DISPATCH_TO_G_L , C_NUM_DISPATCH_TO , C_DURATION_DISPATCH_TO , C_SLA_DURATION_DISPATCH_TO , C_SLA_DURATION_DISPATCH_TO_G_L , C_MET_SLA_DISPATCH_TO , mdt.SLA AS C_SLA_MET_DISPATCH_TO , mdt.SLA_G_L AS C_SLA_MET_DISPATCH_TO_G_L , NUM_DISPATCH_FROM , DURATION_CREATE_TO_DISPATCH_FROM , NUM_DISPATCH_TO_TO_FROM , DURATION_DISPATCH_FROM , SLA_DURATION_DISPATCH_FROM , SLA_DURATION_DISPATCH_FROM_G_L , MET_SLA_DISPATCH_FROM , mdf.SLA AS SLA_MET_DISPATCH_FROM , mdf.SLA_G_L AS SLA_MET_DISPATCH_FROM_G_L , C_NUM_DISPATCH_FROM , C_DURATION_CREATE_TO_DISPATCH_FROM , C_NUM_DISPATCH_TO_TO_FROM , C_DURATION_DISPATCH_FROM , C_SLA_DURATION_DISPATCH_FROM , C_SLA_DURATION_DISPATCH_FROM_G_L , C_MET_SLA_DISPATCH_FROM , mdf.SLA AS C_SLA_MET_DISPATCH_FROM , mdf.SLA_G_L AS C_SLA_MET_DISPATCH_FROM_G_L , NUM_CLOSED , DURATION_WIP_TO_RESOLVE , SLA_WIP_TO_RESOLVE , SLA_WIP_TO_RESOLVE_G_L , MET_SLA_WIP_TO_RESOLVE , wip.SLA AS SLA_MET_WIP_TO_RESOLVE , wip.SLA_G_L AS SLA_MET_WIP_TO_RESOLVE_G_L , DURATION_MTTR , SLA_MTTR , SLA_MTTR_G_L , MET_SLA_MTTR , mttr.SLA AS SLA_MET_MTTR , mttr.SLA_G_L AS SLA_MET_MTTR_G_L , NUM_RESOLVE_FD , rfd.SLA AS SLA_RESOLVE_FD , rfd.SLA_G_L AS SLA_RESOLVE_FD_G_L , NUM_DISPATCH_NTF , ntf.SLA AS SLA_DISPATCH_NTF , ntf.SLA_G_L AS SLA_DISPATCH_NTF , AR_NUM_MET_DUE_DATE , ar.SLA AS AR_SLA_MET_DUE_DATE , ar.SLA_G_L AS AR_SLA_MET_DUE_DATEFROM ( SELECT DATE_DAY , CASE WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN (CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END) WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN (CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END) ELSE SEVERITY END AS SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END AS FORCE_STATUS , sum( NUM_CREATED ) AS NUM_CREATED , sum( NUM_OPEN ) AS NUM_OPEN , sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH , sum( NUM_OPEN_IN_DUE_DATE ) AS NUM_OPEN_IN_DUE_DATE , sum( NUM_DISPATCH_TO ) AS NUM_DISPATCH_TO , sum( DURATION_DISPATCH_TO ) AS DURATION_DISPATCH_TO , max( SLA_DURATION_DISPATCH_TO ) AS SLA_DURATION_DISPATCH_TO , max( SLA_DURATION_DISPATCH_TO_G_L ) AS SLA_DURATION_DISPATCH_TO_G_L , sum( MET_SLA_DISPATCH_TO ) AS MET_SLA_DISPATCH_TO , sum( C_NUM_DISPATCH_TO ) AS C_NUM_DISPATCH_TO , sum( C_DURATION_DISPATCH_TO ) AS C_DURATION_DISPATCH_TO , max( C_SLA_DURATION_DISPATCH_TO ) AS C_SLA_DURATION_DISPATCH_TO , max( C_SLA_DURATION_DISPATCH_TO_G_L ) AS C_SLA_DURATION_DISPATCH_TO_G_L , sum( C_MET_SLA_DISPATCH_TO ) AS C_MET_SLA_DISPATCH_TO , sum( NUM_DISPATCH_FROM ) AS NUM_DISPATCH_FROM , sum( DURATION_CREATE_TO_DISPATCH_FROM ) AS DURATION_CREATE_TO_DISPATCH_FROM , sum( NUM_DISPATCH_TO_TO_FROM ) AS NUM_DISPATCH_TO_TO_FROM , sum( DURATION_DISPATCH_FROM ) AS DURATION_DISPATCH_FROM , max( SLA_DURATION_DISPATCH_FROM ) AS SLA_DURATION_DISPATCH_FROM , max( SLA_DURATION_DISPATCH_FROM_G_L ) AS SLA_DURATION_DISPATCH_FROM_G_L , sum( MET_SLA_DISPATCH_FROM ) AS MET_SLA_DISPATCH_FROM , sum( C_NUM_DISPATCH_FROM ) AS C_NUM_DISPATCH_FROM , sum( C_DURATION_CREATE_TO_DISPATCH_FROM ) AS C_DURATION_CREATE_TO_DISPATCH_FROM , sum( C_NUM_DISPATCH_TO_TO_FROM ) AS C_NUM_DISPATCH_TO_TO_FROM , sum( C_DURATION_DISPATCH_FROM ) AS C_DURATION_DISPATCH_FROM , max( C_SLA_DURATION_DISPATCH_FROM ) AS C_SLA_DURATION_DISPATCH_FROM , max( C_SLA_DURATION_DISPATCH_FROM_G_L ) AS C_SLA_DURATION_DISPATCH_FROM_G_L , sum( C_MET_SLA_DISPATCH_FROM ) AS C_MET_SLA_DISPATCH_FROM , sum( NUM_CLOSED ) AS NUM_CLOSED , sum( DURATION_WIP_TO_RESOLVE ) AS DURATION_WIP_TO_RESOLVE , max( SLA_WIP_TO_RESOLVE ) AS SLA_WIP_TO_RESOLVE , max( SLA_WIP_TO_RESOLVE_G_L ) AS SLA_WIP_TO_RESOLVE_G_L , sum( MET_SLA_WIP_TO_RESOLVE ) AS MET_SLA_WIP_TO_RESOLVE , sum( DURATION_MTTR ) AS DURATION_MTTR , max( SLA_MTTR ) AS SLA_MTTR , max( SLA_MTTR_G_L ) AS SLA_MTTR_G_L , sum( MET_SLA_MTTR ) AS MET_SLA_MTTR , sum( NUM_RESOLVE_FD ) AS NUM_RESOLVE_FD , sum( NUM_DISPATCH_NTF ) AS NUM_DISPATCH_NTF , sum( AR_NUM_MET_DUE_DATE ) AS AR_NUM_MET_DUE_DATE FROM CTS.dbo.NDC_Scorecard_Summary_Table t WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-30,1) as datetime) AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime) AND upper(EQ_REGION) IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' ) AND upper(EQ_MARKET_CLUSTER) IN ( 'NEW ENGLAND','GEORGIA' ) GROUP BY DATE_DAY , CASE WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN (CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END) WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN (CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END) ELSE SEVERITY END-- AS SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END-- AS FORCE_STATUS) t LEFT JOIN CTS.dbo.NDC_SLAs mdt ON t.SEVERITY=mdt.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs mdf ON t.SEVERITY=mdf.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs wip ON t.SEVERITY=wip.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs mttr ON t.SEVERITY=mttr.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs rfd ON t.SEVERITY=rfd.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs ntf ON t.SEVERITY=ntf.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs ar ON t.SEVERITY=ar.SLA_SEVERITYWHERE (mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL ) AND (mdf.SLA_NAME = 'Duration NDC to Field % Met SLA' OR mdf.SLA_NAME IS NULL ) AND (wip.SLA_NAME = 'Duration Dispatch Out % Met SLA' OR wip.SLA_NAME IS NULL ) AND (mttr.SLA_NAME = 'Total MTTR % Met SLA' OR mttr.SLA_NAME IS NULL ) AND (rfd.SLA_NAME = '% Resolved 1st Dispatch' OR rfd.SLA_NAME IS NULL ) AND (ntf.SLA_NAME = '% Dispatched NTF' OR ntf.SLA_NAME IS NULL ) AND (ar.SLA_NAME = '% Met Due Date' OR ar.SLA_NAME IS NULL )
Left Join Help Needed!
Posted - 2007-12-27 : 16:24:54
