SELECT DISTINCT 
                      TOP (100) PERCENT dbo.CWDSPrtcpntDemoInfo.NBR_SSN_RCPT AS SSN, dbo.Kronos_Week_Hours.CDE_PROJ AS ProviderCode, 
                      RTRIM(dbo.Kronos_Week_Hours.CDE_ACTV) AS Activity, ISNULL(NULLIF (dbo.CWDSPrtcpntActivitiesAttendInfo.CNT_HOURS_WEEKLY_ACTV, N''), 0) 
                      AS Hours, dbo.Kronos_Week_Hours.END_DATE AS Weekend, 'K' AS System, ISNULL(dbo.DD_Collecting_Providers.InstitutionProviderID,
                          (SELECT     CAPS_ID
                            FROM          dbo.AIMS_AGENCY_temp
                            WHERE      (REPLACE(LEFT(AGENCY_ID, 6), ' ', '') = dbo.Kronos_Week_Hours.CDE_PROJ))) AS ProviderID
FROM         dbo.Kronos_Week_Hours INNER JOIN
                      dbo.CWDSPrtcpntDemoInfo ON dbo.Kronos_Week_Hours.PRTCPNT_ID = dbo.CWDSPrtcpntDemoInfo.PRTCPNT_ID LEFT OUTER JOIN
                      dbo.DD_Collecting_Providers ON dbo.CWDSPrtcpntDemoInfo.NBR_SSN_RCPT = REPLACE(dbo.DD_Collecting_Providers.SocialSecurityNumber, '-', '') 
                      AND dbo.Kronos_Week_Hours.END_DATE <= ISNULL(NULLIF (dbo.DD_Collecting_Providers.ActivityEndDate, N''), GETDATE()) AND 
                      dbo.DD_Collecting_Providers.ActivityStartDate =
                          (SELECT     MAX(ActivityStartDate) AS Expr1
                            FROM          dbo.DD_Collecting_Providers AS List
                            WHERE      (SocialSecurityNumber = dbo.DD_Collecting_Providers.SocialSecurityNumber) AND 
                                                   (dbo.Kronos_Week_Hours.END_DATE >= ActivityStartDate) AND (dbo.Kronos_Week_Hours.END_DATE <= ActivityEndDate)) 
                      LEFT OUTER JOIN
                      dbo.CWDSPrtcpntActivitiesAttendInfo ON 
                      dbo.Kronos_Week_Hours.SERV_AUTH_DETAIL_ID = dbo.CWDSPrtcpntActivitiesAttendInfo.SERV_AUTH_DETAIL_ID AND 
                      dbo.Kronos_Week_Hours.END_DATE = CONVERT(datetime, dbo.CWDSPrtcpntActivitiesAttendInfo.DTE_ENDING_WEEK)
WHERE     (dbo.Kronos_Week_Hours.END_DATE NOT IN
                          (SELECT     DTE_ENDING_WEEK
                            FROM          dbo.CWDSPrtcpntActivitiesAttendInfo AS Weekends
                            WHERE      (dbo.Kronos_Week_Hours.SERV_AUTH_DETAIL_ID = SERV_AUTH_DETAIL_ID))) AND (ISNULL(NULLIF (CONVERT(float, 
                      dbo.CWDSPrtcpntActivitiesAttendInfo.CNT_HOURS_WEEKLY_ACTV), N''), 0) = 0)