The script below returns the records I need, with a single record for each employee's transactions.
However, I want to do four more things and cannot get the script right:
1. I want the result set to group by Ladetail.fjobno and by employee name (must check for both Prempl.fstname + UPPER(RTRIM(Prempl.fname))
(which is named column lstname,
2. I want to get a SUM of LABMIN by employee as a new field named TOTLABOR,
3. I want that sum (TOTLABOR) to be divided by 60 so that I get a decimal total hours to two places appearing in TOTLABOR, and
4. The TOTLABOR field will aggregate by Ladetail.fjobno, with summation starting from zero for each employee each time Ladetail.fjobno changes.
I've made a few attempts at using GROUPBY and ROLLUP but I cannot get the syntax correct.
How should the script be modified to accomplish these four things?
If employee A has a LABMIN total of 123 minutes, I want the sum of that employee's LABMIN to appear in the new field TOTLABOR, as 2.05
It does not matter if TOTLABOR exists in each record as I do not want to lose the records making up the employee's total labor from the result set - just as long as TOTLABOR holds the same total number in each one of the employee's records in the table.
SELECT UPPER(RTRIM(Prempl.ffname)) as fstname,
UPPER(RTRIM(Prempl.fname)) as lstname, Jodrtg.fuprodtime,
Ladetail.fscrpqty,Ladetail.fedatetime,Ladetail.fsdatetime,Ladetail.fjobno+STR(Ladetail.foperno,4) as Jobopkey,Ladetail.fchrglab,
WHEN Ladetail.flabtype = 'P' THEN Ladetail.Fchrglab * 60
ELSE DateDiff(mi, Ladetail.fsdatetime, Ladetail.fedatetime)*60
END AS LABSeconds,
Round(DateDiff(mi, Ladetail.fsdatetime, Ladetail.fedatetime),4) as LABMIN,
SUBSTRING(CONVERT(CHAR(19),ladetail.fsdatetime,100),13,7) AS NSHOUR,
SUBSTRING(CONVERT(CHAR(19),ladetail.fedatetime,100),13,7) AS NEHOUR,
ladetail.fdate+(7-(cast(datepart(dw,ladetail.fdate) as int))) AS labweek
LEFT OUTER JOIN prempl ON Ladetail.fempno = Prempl.fempno
LEFT OUTER JOIN jodrtg ON Ladetail.fjobno = Jodrtg.fjobno AND Ladetail.foperno = Jodrtg.foperno
where ladetail.fjobno = '30915-0000'