Hey Skip, I really appreciate your help.
SELECT d.empl_no, d.code,
CASE
WHEN b.ck_title = 'RETIRE' THEN 'RETIREMENT'
WHEN b.ck_title = 'B/C' THEN 'HEALTH INS'
WHEN b.ck_title = 'KEYSTONE' THEN 'HEALTH INS'
WHEN b.ck_title = 'POS' THEN 'HEALTH INS'
ELSE b.ck_title
END,
d.pay_date, d.amount,
CASE
WHEN GROUPING(d.code) = 0 and GROUPING(d.empl_no) = 0 and
GROUPING(d.pay_date) = 1 and GROUPING(d.amount) = 1 and
GROUPING(b.ck_title) = 1
THEN 'Sub Total' + cast(d.empl_no as varchar(8)) + '/' +
cast(d.code as varchar(4))
end,
sum(d.amount)
from detdist d
inner join employee e on e.empl_no = d.empl_no
inner join person p on p.empl_no = d.empl_no
inner join payrate r on r.empl_no = d.empl_no
inner join payroll y on y.empl_no = d.empl_no
Inner Join dist_orgn h on h.empl_no = d.empl_no
left join bartable l on l.code = p.bargain
left join employee_type m on m.code = p.empl_type
left join clstable c on c.class_cd = r.classify
left join dedtable b on b.ded_cd = d.code
where p.status='ACT' and SUBSTRING(h.orgn, 1, 3) IN ('058','114','128','213','300')
and ( (d.code between '0002' and '1005' ) or
(d.code between '1019' and '1025' ) or
(d.code between '3001' and '4399' ) or
(d.code between '5001' and '5098' ) or
(d.code between '5101' and '5102' ))
and d.code is not NULL
and (d.rec_type <> 'F' and d.rec_type <> 'R')
and (d.pay_date >= '2012/09/01' and d.pay_date <= '2013/08/31')
GROUP BY d.empl_no, d.code, b.ck_title, d.pay_date, d.amount WITH ROLLUP
But I also need to include this code:
-- select d.empl_no, RTRIM(e.l_name) 'Last', RTRIM(e.f_name) 'First',
-- CONVERT(Decimal(10,0),r.days_worked) 'Days Worked', RTRIM(d.orgn_proj) 'Orgn',
-- RTRIM(d.acct) 'Account', CONVERT(Decimal(4,2),h.prcent) '%',
-- CASE d.rec_type
-- WHEN 'F' THEN 'FRINGE BENS'
-- WHEN 'R' THEN 'FRINGE BENS (PROJECT)'
-- WHEN 'O' THEN 'GROSS WAGES'
-- WHEN 'P' THEN 'GROSS WAGES (PROJECT)'
-- WHEN 'D' THEN 'DEDUCTS/TAXES'
-- WHEN 'W' THEN 'WORKERS COMP'
-- END,
S8730 Processors (Redundncy/Mirror Img)
Aura 5.2 (Communication Manager)
Modular Messaging 5.2