Certified Payroll

I recently received a request to write a certified payroll report, and the query turned out to be a tough one. The client needed me to bring in the project from the gpUPRCPR table, which I'd never heard of; and several other custom fields. Like most GP reports, this report does not run from a query but they build the dataset one line at a time behind the form. I had to trace through several hundred SQL calls to get this figured out. More, the stored procedures that they did call were not on the machine. I'm not sure how that works, because they didn't return errors. Maybe they create them on the fly and delete them?

At any rate, the query for the report is below. I don't what to have to do this twice <smiles>

If you can shed any light on how this process works, would you add a comment below the article?

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

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
 
--get rid of all duplicates for ut.AUCTRLCD,ut.PYADNMBR ,ut.TRXENDDT,ut.UNTSTOPY,ut.UPRTRXAM,
select
        ut.AUCTRLCD,ut.PYADNMBR ,ut.TRXBEGDT,ut.TRXENDDT,ut.UNTSTOPY,ut.UPRTRXAM,
        rtrim(ut.employid) as employid,
        ut.PAYROLCD as PAYROLCD,
        ut.JOBTITLE,
        ut.PAYRATE
    into #ut
    from UPR30300 ut
    where ut.TRXBEGDT between '1/1/1900' and @End_Date
    group by ut.AUCTRLCD,ut.PYADNMBR ,ut.TRXENDDT,ut.UNTSTOPY,ut.UPRTRXAM, ut.EMPLOYID, ut.PAYROLCD,ut.TRXBEGDT,ut.JOBTITLE,ut.PAYRATE,ut.employid
    order by TRXENDDT, UPRTRXAM
 
 
--get the job
select
        isnull(gp.jobnumbr,'') as JOBNUMBR,ut.AUCTRLCD,ut.PYADNMBR ,ut.TRXBEGDT,ut.TRXENDDT,ut.UNTSTOPY,ut.UPRTRXAM,
        ut.employid,
        ut.PAYROLCD as PAYROLCD,
        ut.JOBTITLE,
        ut.PAYRATE
    into #ut2
    from #ut ut
        left 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
    order by JOBNUMBR
 
--sum by day
select
        ut.JOBNUMBR,
        --ut.AUCTRLCD,ut.PYADNMBR ,
        ut.JOBTITLE,
        avg(ut.PAYRATE) AS PAYRATE,
        ut.EMPLOYID,
        ut.PAYROLCD,
        uc.FDWDGPRN as FWH,
        uc.ttlddtns as  Other,
        uc.NTWPYRN as Net_Paid,
        uc.ficamwpr + uc.FCASWPR as FICA,
        SUM(case when ut.PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT between DATEADD(day,-6,@End_Date) and DATEADD(day,-0,@End_Date) then ut.UPRTRXAM else 0 end) as JobEarned,
        SUM(case when ut.PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT between DATEADD(day,-6,@End_Date) and DATEADD(day,-0,@End_Date) then ut.UNTSTOPY else 0 end) as WeekTotal,
        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
    into #ut3
    from #ut2 ut
        join UPR30100 uc on uc.AUCTRLCD = ut.AUCTRLCD and uc.PYADNMBR = ut.PYADNMBR
    group by ut.EMPLOYID,ut.PAYROLCD,ut.JOBTITLE,ut.JOBNUMBR,
        uc.FDWDGPRN ,
        uc.ttlddtns ,
        uc.NTWPYRN ,uc.ficamwpr + uc.FCASWPR
    HAVING SUM(case when ut.PAYROLCD in ('cerreg','certot') and ut.TRXBEGDT between DATEADD(day,-6,@End_Date) and DATEADD(day,-0,@End_Date) then ut.UNTSTOPY else 0 end) > 0
 
select ut.JOBNUMBR,
        isnull(pm.zProjectDescription,'<none>') as zProjectDescription,
        isnull(pm.zMasterProjectID,'<none>') as zMasterProjectID,
        ut.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,
        ut.PAYROLCD as PAYROLCD,
        ut.FWH,
        ut.Other,
        ut.Net_Paid,
        ut.day1,
        ut.day2,
        ut.day3,
        ut.day4,
        ut.day5,
        ut.day6,
        ut.day7,
        ut.day1label,
        ut.day2label,
        ut.day3label,
        ut.day4label,
        ut.day5label,
        ut.day6label,
        ut.day7label,
        ut.day1date,
        ut.day2date,
        ut.day3date,
        ut.day4date,
        ut.day5date,
        ut.day6date,
        ut.day7date,
        ut.WeekTotal,
        ut.JOBTITLE,
        es.earned,
        ut.JobEarned,
        ut.FICA,
        es.SWH,
        ut.FWH,
        ut.Other,
        ut.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 #ut3 ut
        join #empSum es on es.employid = ut.employid
        join upr00100 e on e.employid = ut.EMPLOYID
        left join pc00200 pm on pm.zProjectID = ut.JOBNUMBR
        left join pc00205 ud on ud.zProjectNumber = pm.zProjectNumber
        left join UPR00102 ea on ea.EMPLOYID = e.EMPLOYID and ea.ADRSCODE = e.ADRSCODE
    order by 1,5
    
GO
 GRANT EXEC ON dd_CertifiedPayroll TO PUBLIC

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences