select MAX(employid),MAX(chekdate),
case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end as PAYCODE,
case payrolcd WHEN 'NIGHT1' THEN untstopy else 0 end as NIGHT1HOURS,
case payrolcd WHEN 'NIGHT1' THEN payrate else 0 end as NIGHT1RATE,
case payrolcd WHEN 'NIGHT1' THEN uprtrxam else 0 end as NIGHT1TOTAL,
case payrolcd WHEN 'NIGHT2' THEN 'NIGHT2' else '' end as PAYCODE,
case payrolcd WHEN 'NIGHT2' THEN untstopy else 0 end as NIGHT2HOURS,
case payrolcd WHEN 'NIGHT2' THEN payrate else 0 end as NIGHT2RATE,
case payrolcd WHEN 'NIGHT2' THEN uprtrxam else 0 end as NIGHT2TOTAL,
case payrolcd WHEN 'VACATN' THEN 'VACATN' else '' end as PAYCODE,
case payrolcd WHEN 'VACATN' THEN untstopy else 0 end as VACATNHOURS,
case payrolcd WHEN 'VACATN' THEN payrate else 0 end as VACATNRATE,
case payrolcd WHEN 'VACATN' THEN uprtrxam else 0 end as VACATNTOTAL,
case payrolcd WHEN 'SICKTM' THEN 'SICKTM' else '' end as PAYCODE,
case payrolcd WHEN 'SICKTM' THEN untstopy else 0 end as SICKTMHOURS,
case payrolcd WHEN 'SICKTM' THEN payrate else 0 end as SICKTMRATE,
case payrolcd WHEN 'SICKTM' THEN uprtrxam else 0 end as SICKTMTOTAL,
case payrolcd WHEN 'HOLIDY' THEN 'HOLIDY' else '' end as PAYCODE,
case payrolcd WHEN 'HOLIDY' THEN untstopy else 0 end as HOLIDYHOURS,
case payrolcd WHEN 'HOLIDY' THEN payrate else 0 end as HOLIDYRATE,
case payrolcd WHEN 'HOLIDY' THEN uprtrxam else 0 end as HOLIDYTOTAL,
case payrolcd WHEN 'PERSNL' THEN 'PERSNL' else '' end as PAYCODE,
case payrolcd WHEN 'PERSNL' THEN untstopy else 0 end as PERSNLHOURS,
case payrolcd WHEN 'PERSNL' THEN payrate else 0 end as PERSNLRATE,
case payrolcd WHEN 'PERSNL' THEN uprtrxam else 0 end as PERSNLTOTAL
FROM UPR30300
where payrolcd in ('NIGHT1','NIGHT2','VACATN','SICKTM','HOLIDY','PERSNL') and EMPLOYID='34328'
GROUP BY EMPLOYID,CHEKDATE
order by chekdate,employid
select MAX(employid),MAX(chekdate),
SUM(case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT1' THEN untstopy else 0 end) as NIGHT1HOURS,
SUM(case payrolcd WHEN 'NIGHT1' THEN payrate else 0 end) as NIGHT1RATE,
SUM(case payrolcd WHEN 'NIGHT1' THEN uprtrxam else 0 end) as NIGHT1TOTAL,
SUM(case payrolcd WHEN 'NIGHT2' THEN 'NIGHT2' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT2' THEN untstopy else 0 end) as NIGHT2HOURS,
SUM(case payrolcd WHEN 'NIGHT2' THEN payrate else 0 end) as NIGHT2RATE,
SUM(case payrolcd WHEN 'NIGHT2' THEN uprtrxam else 0 end) as NIGHT2TOTAL,
SUM(case payrolcd WHEN 'VACATN' THEN 'VACATN' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'VACATN' THEN untstopy else 0 end) as VACATNHOURS,
SUM(case payrolcd WHEN 'VACATN' THEN payrate else 0 end) as VACATNRATE,
SUM(case payrolcd WHEN 'VACATN' THEN uprtrxam else 0 end) as VACATNTOTAL,
SUM(case payrolcd WHEN 'SICKTM' THEN 'SICKTM' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'SICKTM' THEN untstopy else 0 end) as SICKTMHOURS,
SUM(case payrolcd WHEN 'SICKTM' THEN payrate else 0 end) as SICKTMRATE,
SUM(case payrolcd WHEN 'SICKTM' THEN uprtrxam else 0 end) as SICKTMTOTAL,
SUM(case payrolcd WHEN 'HOLIDY' THEN 'HOLIDY' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'HOLIDY' THEN untstopy else 0 end) as HOLIDYHOURS,
SUM(case payrolcd WHEN 'HOLIDY' THEN payrate else 0 end) as HOLIDYRATE,
SUM(case payrolcd WHEN 'HOLIDY' THEN uprtrxam else 0 end) as HOLIDYTOTAL,
SUM(case payrolcd WHEN 'PERSNL' THEN 'PERSNL' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'PERSNL' THEN untstopy else 0 end) as PERSNLHOURS,
SUM(case payrolcd WHEN 'PERSNL' THEN payrate else 0 end) as PERSNLRATE,
SUM(case payrolcd WHEN 'PERSNL' THEN uprtrxam else 0 end) as PERSNLTOTAL
FROM UPR30300
where payrolcd in ('NIGHT1','NIGHT2','VACATN','SICKTM','HOLIDY','PERSNL') and EMPLOYID='34328'
GROUP BY EMPLOYID,CHEKDATE
order by chekdate,employid
xselect employid, chekdate,
SUM(case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT1' THEN untstopy else 0 end) as NIGHT1HOURS,
SUM(case payrolcd WHEN 'NIGHT1' THEN payrate else 0 end) as NIGHT1RATE,
SUM(case payrolcd WHEN 'NIGHT1' THEN uprtrxam else 0 end) as NIGHT1TOTAL,
SUM(case payrolcd WHEN 'NIGHT2' THEN 'NIGHT2' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT2' THEN untstopy else 0 end) as NIGHT2HOURS,
SUM(case payrolcd WHEN 'NIGHT2' THEN payrate else 0 end) as NIGHT2RATE,
SUM(case payrolcd WHEN 'NIGHT2' THEN uprtrxam else 0 end) as NIGHT2TOTAL,
SUM(case payrolcd WHEN 'VACATN' THEN 'VACATN' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'VACATN' THEN untstopy else 0 end) as VACATNHOURS,
SUM(case payrolcd WHEN 'VACATN' THEN payrate else 0 end) as VACATNRATE,
SUM(case payrolcd WHEN 'VACATN' THEN uprtrxam else 0 end) as VACATNTOTAL,
SUM(case payrolcd WHEN 'SICKTM' THEN 'SICKTM' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'SICKTM' THEN untstopy else 0 end) as SICKTMHOURS,
SUM(case payrolcd WHEN 'SICKTM' THEN payrate else 0 end) as SICKTMRATE,
SUM(case payrolcd WHEN 'SICKTM' THEN uprtrxam else 0 end) as SICKTMTOTAL,
SUM(case payrolcd WHEN 'HOLIDY' THEN 'HOLIDY' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'HOLIDY' THEN untstopy else 0 end) as HOLIDYHOURS,
SUM(case payrolcd WHEN 'HOLIDY' THEN payrate else 0 end) as HOLIDYRATE,
SUM(case payrolcd WHEN 'HOLIDY' THEN uprtrxam else 0 end) as HOLIDYTOTAL,
SUM(case payrolcd WHEN 'PERSNL' THEN 'PERSNL' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'PERSNL' THEN untstopy else 0 end) as PERSNLHOURS,
SUM(case payrolcd WHEN 'PERSNL' THEN payrate else 0 end) as PERSNLRATE,
SUM(case payrolcd WHEN 'PERSNL' THEN uprtrxam else 0 end) as PERSNLTOTAL
FROM UPR30300
where payrolcd in ('NIGHT1','NIGHT2','VACATN','SICKTM','HOLIDY','PERSNL') and EMPLOYID='34328'
GROUP BY EMPLOYID,CHEKDATE
order by chekdate,employid
ASKER
select employid,chekdate,
(case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT1' THEN untstopy else 0 end) as NIGHT1HOURS,
(case payrolcd WHEN 'NIGHT1' THEN payrate else 0 end) as NIGHT1RATE,
SUM(case payrolcd WHEN 'NIGHT1' THEN uprtrxam else 0 end) as NIGHT1TOTAL,
(case payrolcd WHEN 'NIGHT2' THEN 'NIGHT2' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT2' THEN untstopy else 0 end) as NIGHT2HOURS,
(case payrolcd WHEN 'NIGHT2' THEN payrate else 0 end) as NIGHT2RATE,
SUM(case payrolcd WHEN 'NIGHT2' THEN uprtrxam else 0 end) as NIGHT2TOTAL,
(case payrolcd WHEN 'VACATN' THEN 'VACATN' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'VACATN' THEN untstopy else 0 end) as VACATNHOURS,
(case payrolcd WHEN 'VACATN' THEN payrate else 0 end) as VACATNRATE,
SUM(case payrolcd WHEN 'VACATN' THEN uprtrxam else 0 end) as VACATNTOTAL,
(case payrolcd WHEN 'SICKTM' THEN 'SICKTM' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'SICKTM' THEN untstopy else 0 end) as SICKTMHOURS,
(case payrolcd WHEN 'SICKTM' THEN payrate else 0 end) as SICKTMRATE,
SUM(case payrolcd WHEN 'SICKTM' THEN uprtrxam else 0 end) as SICKTMTOTAL,
(case payrolcd WHEN 'HOLIDY' THEN 'HOLIDY' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'HOLIDY' THEN untstopy else 0 end) as HOLIDYHOURS,
(case payrolcd WHEN 'HOLIDY' THEN payrate else 0 end) as HOLIDYRATE,
SUM(case payrolcd WHEN 'HOLIDY' THEN uprtrxam else 0 end) as HOLIDYTOTAL,
(case payrolcd WHEN 'PERSNL' THEN 'PERSNL' else '' end) as PAYCODE,
SUM(case payrolcd WHEN 'PERSNL' THEN untstopy else 0 end) as PERSNLHOURS,
(case payrolcd WHEN 'PERSNL' THEN payrate else 0 end) as PERSNLRATE,
SUM(case payrolcd WHEN 'PERSNL' THEN uprtrxam else 0 end) as PERSNLTOTAL
FROM UPR30300
where payrolcd in ('NIGHT1','NIGHT2','VACATN','SICKTM','HOLIDY','PERSNL') and EMPLOYID='34328'
GROUP BY EMPLOYID,CHEKDATE
order by chekdate,employid
ASKER
As usual, you folks always come through. Thanks for your help. This is the final version that gives me what I need.
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
TRUSTED BY
You are using and aggregate function (MAX) so any other field included in the field list must be in a GROUP BY clause