SELECT 
	OrgCode AS UnitState , 
	OrgCode As State,
	COUNT(*) AS Rows_Per_OrgCode 
	--IsNull(Count(*), 0) As [SLRP Approved] 
FROM tLookup_StateTable LKState 
Left Join dbo.tbl_Status_Of_ContractRequests_By_State TBL on LKState.OrgCode = TBL.UnitState 
Inner Join dbo.tblContracts C ON C.Contract_ID = TBL.TrackingID 
LEFT OUTER JOIN dbo.tLookup_Incentive Lkp ON C.IT_CODE = Lkp.IT_ID

Where (TBL.APPROVED > 0) AND (TBL.VERIFIED = 0) AND (TBL.[VERIFICATION FAILED] = 0) AND (TBL.[SYS VERIF - LOSS] = 0) 
	AND (C.ContractStatus = 'A') AND C.IT_Code = 'S'

Group By OrgCode

Inner Join dbo.tblContracts C
        ON C.Contract_ID = TBL.TrackingID

SELECT  OrgCode AS UnitState
      , OrgCode As State
      , IsNull(Count(*), 0) As [SLRP Approved]
FROM    tLookup_StateTable LKState
Left Join dbo.tbl_Status_Of_ContractRequests_By_State TBL
        on LKState.OrgCode = TBL.UnitState
LEFT Join dbo.tblContracts C        ------ altered inner to left join  (consequences)
        ON C.Contract_ID = TBL.TrackingID
LEFT OUTER JOIN dbo.tLookup_Incentive Lkp
        ON C.IT_CODE = Lkp.IT_ID
Where   ( TBL.APPROVED > 0 )
        AND ( TBL.VERIFIED = 0 )
        AND ( TBL.[VERIFICATION FAILED] = 0 )
        AND ( TBL.[SYS VERIF - LOSS] = 0 )
        AND ( C.ContractStatus = 'A' )
        AND C.IT_Code = 'S'
Group By OrgCode



SELECT  OrgCode AS UnitState
      , OrgCode As State
      , IsNull(Count(*), 0) As [SLRP Approved]
FROM    tLookup_StateTable LKState
Left Join dbo.tbl_Status_Of_ContractRequests_By_State TBL
Inner Join dbo.tblContracts C
        ON C.Contract_ID = TBL.TrackingID
    on LKState.OrgCode = TBL.UnitState   --- nested inner join with left join
LEFT OUTER JOIN dbo.tLookup_Incentive Lkp
        ON C.IT_CODE = Lkp.IT_ID
Where   ( TBL.APPROVED > 0 )
        AND ( TBL.VERIFIED = 0 )
        AND ( TBL.[VERIFICATION FAILED] = 0 )
        AND ( TBL.[SYS VERIF - LOSS] = 0 )
        AND ( C.ContractStatus = 'A' )
        AND C.IT_Code = 'S'
Group By OrgCode

     Jul 13  

Sometimes this is me

     Jul 13  

Sometimes this is me