IF exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'dd_CertifiedPayroll') begin
DROP proc dd_CertifiedPayroll
end
GO
CREATE proc dd_CertifiedPayroll
-- dd_CertifiedPayroll '10/21/2014'
@End_Date date
AS
declare @dtStart date
set @dtStart = DATEADD(day,-6,@End_Date)
--get summary data for the report
select
ut.EMPLOYID,
sum(case when PAYROLCD in ('cerreg','certot','reg','ot') then UPRTRXAM else 0 end) as Earned,
sum(case when PAYROLCD in ('md') then UPRTRXAM else 0 end) as SWH
into #empSum
from UPR30300 ut
where ut.TRXBEGDT between @dtStart and @End_Date
--and ut.EMPLOYID = 'JB9038 '
group by ut.employid
select gp.JOBNUMBR,
isnull(pm.zProjectDescription,'<none>') as zProjectDescription,
isnull(pm.zMasterProjectID,'<none>') as zMasterProjectID,
RTRIM(ut.EMPLOYID) AS EMPLOYID,
RTRIM(e.FRSTNAME) + ' ' + rtrim(e.lastname) as empname,
rtrim(ea.address1) + ' ' + ea.address2 as Address1,
rtrim(ea.CITY) + ', ' + rtrim(ea.STATE) + ' ' + ea.ZIPCODE as Address2,
dbo.ddf_SSN(e.SOCSCNUM) as Address3,
PAYROLCD as PAYROLCD,
sum(case when ut.PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT = DATEADD(day,-6,@End_Date) then ut.UNTSTOPY else 0 end) as day1,
sum(case when ut.PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT = DATEADD(day,-5,@End_Date) then ut.UNTSTOPY else 0 end) as day2,
sum(case when ut.PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT = DATEADD(day,-4,@End_Date) then ut.UNTSTOPY else 0 end) as day3,
sum(case when ut.PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT = DATEADD(day,-3,@End_Date) then ut.UNTSTOPY else 0 end) as day4,
sum(case when ut.PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT = DATEADD(day,-2,@End_Date) then ut.UNTSTOPY else 0 end) as day5,
sum(case when ut.PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT = DATEADD(day,-1,@End_Date) then ut.UNTSTOPY else 0 end) as day6,
sum(case when ut.PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT = DATEADD(day,-0,@End_Date) then ut.UNTSTOPY else 0 end) as day7,
left(datename(weekday,DATEADD(day,-6,@End_Date)),3) as day1label,
left(datename(weekday,DATEADD(day,-5,@End_Date)),3) as day2label,
left(datename(weekday,DATEADD(day,-4,@End_Date)),3) as day3label,
left(datename(weekday,DATEADD(day,-3,@End_Date)),3) as day4label,
left(datename(weekday,DATEADD(day,-2,@End_Date)),3) as day5label,
left(datename(weekday,DATEADD(day,-1,@End_Date)),3) as day6label,
left(datename(weekday,DATEADD(day,-0,@End_Date)),3) as day7label,
day(DATEADD(day,-6,@End_Date)) as day1date,
day(DATEADD(day,-5,@End_Date)) as day2date,
day(DATEADD(day,-4,@End_Date)) as day3date,
day(DATEADD(day,-3,@End_Date)) as day4date,
day(DATEADD(day,-2,@End_Date)) as day5date,
day(DATEADD(day,-1,@End_Date)) as day6date,
day(DATEADD(day,-0,@End_Date)) as day7date,
sum(case when PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT between @dtStart and @End_Date then ut.UNTSTOPY else 0 end) as WeekTotal,
max(case when PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT between @dtStart and @End_Date then ut.JOBTITLE else '' end) as JOBTITLE,
avg(case when PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT between @dtStart and @End_Date then ut.PAYRATE else 0 end) as PAYRATE,
es.earned,
sum(case when PAYROLCD in ('cerreg','certot') then UPRTRXAM else 0 end) as JobEarned,
uc.ficamwpr + uc.FCASWPR as FICA,
es.SWH,
uc.FDWDGPRN as FWH,
uc.ttlddtns as Other,
uc.NTWPYRN as Net_Paid,
rtrim(e.ALTERNATENAME) as Health,
rtrim(e.SPOUSE) as Holiday,
rtrim(e.USERDEF1) as PTO,
rtrim(e.USERDEF2) as [401K],
convert(numeric(19,2),e.MINETPAY) as Apprenticeship,
rtrim(e.NICKNAME) as Disability,
ud.zUDString20_1 as FAD,
ud.zUDString20_2 as SSS
from UPR30300 ut
join #empSum es on es.employid = ut.employid
join upr00100 e on e.employid = ut.EMPLOYID
join (select distinct AUCTRLCD,PYADNMBR,TRXENDDT, TOTALPAY, jobnumbr, convert(numeric(19,5),HOURSWKD)/100 as HoursWorked from gpUPRCPR ) gp
on gp.AUCTRLCD = ut.AUCTRLCD and gp.PYADNMBR = ut.PYADNMBR
and gp.TRXENDDT = ut.TRXENDDT
and gp.HoursWorked = ut.UNTSTOPY
and gp.TOTALPAY = ut.UPRTRXAM
join UPR30100 uc on uc.AUCTRLCD = ut.AUCTRLCD and uc.PYADNMBR = ut.PYADNMBR
left join pc00200 pm on pm.zProjectID = gp.JOBNUMBR
left join pc00205 ud on ud.zProjectNumber = pm.zProjectNumber
left join UPR00102 ea on ea.EMPLOYID = e.EMPLOYID and ea.ADRSCODE = e.ADRSCODE
where ut.TRXBEGDT between '1/1/1900' and @End_Date
--and e.employid in ('cw3969','rw7574','tb5229')
group by RTRIM(e.FRSTNAME) + ' ' + rtrim(e.lastname),gp.JOBNUMBR , ut.employid,uc.ficamwpr, uc.FCASWPR,uc.FDWDGPRN,pm.zProjectDescription,uc.NTWPYRN,
rtrim(ea.address1) + ' ' + ea.address2 ,
rtrim(ea.CITY) + ', ' + rtrim(ea.STATE) + ' ' + ea.ZIPCODE ,
dbo.ddf_SSN(e.SOCSCNUM) ,
pm.zMasterProjectID,
ud.zUDString20_1 ,
ud.zUDString20_2 ,
e.ALTERNATENAME,
e.SPOUSE,
e.USERDEF1,
e.USERDEF2,
e.NICKNAME ,
e.MINETPAY ,
PAYROLCD,
es.earned,
es.swh,
uc.ttlddtns
--all pay codes are allowed into the report, but we only show employees that have cerreg
having sum(case when PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT between @dtStart and @End_Date then ut.UNTSTOPY else 0 end) > 0
order by 1,5
GO
GRANT EXEC ON dd_CertifiedPayroll TO PUBLIC
-- dd_CertifiedPayroll '10/21/2014'