SELECT ROW_NUMBER() OVER(ORDER BY tp.prid) DIMKEY,
       @select:dim:user_def:implied:RES:SR.ID:RES_ID@,
       @select:dim_prop:user_def:implied:RES:SR.FULL_NAME:FULL_NAME@,
       @select:dim_prop:user_def:implied:RES:SR.UNIQUE_NAME:UNIQUE_NAME@,
       @select:dim_prop:user_def:implied:RES:PR.PRISOPEN:RES_OPEN@,
       @select:dim_prop:user_def:implied:RES:TP.PRSTART:TS_START@,
       @select:dim_prop:user_def:implied:RES:TP.PRFINISH:TS_FINISH@,
       @select:dim_prop:user_def:implied:RES:PRJ_obs_units.name:obs@,
       @select:dim_prop:user_def:implied:RES:TP.PRISOPEN:TP_OPEN@,
       @select:dim_prop:user_def:implied:RES:(case
              when TS.PRSTATUS= 0 then 'Populated'
              when TS.PRSTATUS= 1 then 'Submitted'
              when TS.PRSTATUS= 2 then 'Returned'
              when TS.PRSTATUS= 3 then 'Approved'
              when TS.PRSTATUS= 4 then 'Posted'
              when TS.PRSTATUS= 5 then 'Adjusted'
              when TS.PRSTATUS is null then 'Unpopulated'
              else 'Unpopulated'
              end):TS_STATUS_DISPLAY@,
       @select:dim_prop:user_def:implied:RES:TS.PRSTATUS:statnum@,
       @select:dim_prop:user_def:implied:RES:(case 
              when DATENAME(dw, GETDATE()) = 'Monday' then (DATEADD(Day, -9, {fn CURDATE()}))
              When DATENAME(dw, GETDATE()) = 'Sunday' then (DATEADD(Day, -8, {fn CURDATE()}))  
              When DATENAME(dw, GETDATE()) = 'Tuesday' then (DATEADD(Day, -3, {fn CURDATE()}))  
              When DATENAME(dw, GETDATE()) = 'Wednesday' then (DATEADD(Day, -4, {fn CURDATE()}))  
              When DATENAME(dw, GETDATE()) = 'Thursday' then (DATEADD(Day, -5, {fn CURDATE()})) 
              When DATENAME(dw, GETDATE()) = 'Friday' then (DATEADD(Day, -6, {fn CURDATE()})) 
              When DATENAME(dw, GETDATE()) = 'Saturday' then (DATEADD(Day, -7, {fn CURDATE()})) 
              else null end):TS_TIMESTART@

FROM
(SELECT DISTINCT PR.PRID RESOURCE_ID, TP.PRID PRTIMEPERIODID FROM niku.PRTIMEPERIOD TP, niku.PRJ_RESOURCES PR WHERE PR.PRTRACKMODE=2 AND TP.PRISOPEN=1 AND 
(tp.prstart = (case 
when DATENAME(dw, GETDATE()) = 'Monday' then (DATEADD(Day, -9, {fn CURDATE()}))
When DATENAME(dw, GETDATE()) = 'Sunday' then (DATEADD(Day, -8, {fn CURDATE()}))  
When DATENAME(dw, GETDATE()) = 'Tuesday' then (DATEADD(Day, -3, {fn CURDATE()}))  
When DATENAME(dw, GETDATE()) = 'Wednesday' then (DATEADD(Day, -4, {fn CURDATE()}))  
When DATENAME(dw, GETDATE()) = 'Thursday' then (DATEADD(Day, -5, {fn CURDATE()})) 
When DATENAME(dw, GETDATE()) = 'Friday' then (DATEADD(Day, -6, {fn CURDATE()})) 
When DATENAME(dw, GETDATE()) = 'Saturday' then (DATEADD(Day, -7, {fn CURDATE()})) 
end) 
)) AS RESTP
LEFT JOIN niku.PRTIMEPERIOD TP ON (RESTP.PRTIMEPERIODID=TP.PRID)
LEFT JOIN niku.PRJ_RESOURCES PR ON (RESTP.RESOURCE_ID = PR.PRID)
LEFT OUTER JOIN niku.PRTIMESHEET TS ON (RESTP.PRTIMEPERIODID=TS.PRTIMEPERIODID AND RESTP.RESOURCE_ID=TS.PRRESOURCEID)
LEFT OUTER JOIN niku.PRTIMEENTRY TE ON TS.PRID=TE.PRTIMESHEETID,
niku.SRM_RESOURCES SR 
INNER JOIN Clarity.niku.PRJ_OBS_ASSOCIATIONS PRJ_OBS_ASSOCIATIONS on SR.ID=PRJ_OBS_ASSOCIATIONS.RECORD_ID
INNER JOIN Clarity.niku.PRJ_OBS_UNITS PRJ_OBS_UNITS on PRJ_OBS_ASSOCIATIONS.unit_id=PRJ_OBS_UNITS.id AND PRJ_OBS_UNITS.type_id=5000002

WHERE
SR.ID = RESTP.RESOURCE_ID
AND SR.is_active=1
AND SR.unique_name <> 'OUTR'
AND SR.last_name <> 'PIC'
AND PR.prisopen=1
AND 
(tp.prstart = (case 
when DATENAME(dw, GETDATE()) = 'Monday' then (DATEADD(Day, -9, {fn CURDATE()}))
When DATENAME(dw, GETDATE()) = 'Sunday' then (DATEADD(Day, -8, {fn CURDATE()}))  
When DATENAME(dw, GETDATE()) = 'Tuesday' then (DATEADD(Day, -3, {fn CURDATE()}))  
When DATENAME(dw, GETDATE()) = 'Wednesday' then (DATEADD(Day, -4, {fn CURDATE()}))  
When DATENAME(dw, GETDATE()) = 'Thursday' then (DATEADD(Day, -5, {fn CURDATE()})) 
When DATENAME(dw, GETDATE()) = 'Friday' then (DATEADD(Day, -6, {fn CURDATE()})) 
When DATENAME(dw, GETDATE()) = 'Saturday' then (DATEADD(Day, -7, {fn CURDATE()})) 
end) 
OR (ts.prresourceid is null)
)

AND @FILTER@

GROUP BY SR.ID,PR.PRID,
SR.FULL_NAME,
SR.UNIQUE_NAME,
SR.MANAGER_ID,
TP.PRSTART,
TP.PRFINISH,
TP.PRID,
PRJ_OBS_UNITS.name,
TP.PRISOPEN,
SR.RESOURCE_TYPE,
TS.PRID,
TS.PRSTATUS,
PR.PRISOPEN,
SR.PERSON_TYPE
HAVING @HAVING_FILTER@