Need suggestions on combining rows.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need suggestions on combining rows.

Author

Topic

greg52
Starting Member

5 Posts

Posted - 2005-07-14 : 17:39:18

First off, my experience with SQL is limited. I am trying combine on a single row the Hourly and OT hours and rates for an employee. Ideally, I would like the output data to look like this:

Reg_Hours Reg_Rate OT_HRS OT_Rate Total_Billed
-----------------------------------------------------------------------
40.00000 17.36000 7.50000 26.04000 889.70000

However, the query is returning two lines. The first line is the Hourly pay code row and has the regular hours and regular rate columns valued properly. The OT columns are zeros. The second row is the OT pay code row and has the OT columns valued correctly. The regular columns are zeros.

Any help would be appreciated. My code follows:

select distinct rm00101.custname as Client_Name, rm00101.custnmbr as ID, rm00101.pymtrmid as PO_#,
tw00111.thinkinvno as INV_#, tw00111.date1 as INV_Date,
upr30100.emplname as Employee_Name, upr30100.employid as Employee_ID,
twupr400.tw_billing_info as Job_Title, twupr400.payrcord as Pay_Code,
case upr30300.payrolcd
when 'HOURLY' then upr30300.untstopy
else '0'
end as Reg_Hours,
case upr30300.payrolcd
when 'HOURLY' then twupr400.tw_bill_rate
else '0'
end as Reg_Rate,
case upr30300.payrolcd
when 'OT' then upr30300.untstopy
else '0'
end as OT_HRS,
case upr30300.payrolcd
when 'OT' then twupr400.tw_bill_rate
else '0'
end as OT_Rate,
Cast(upr30300.untstopy * twupr400.tw_bill_rate as SmallMoney) as Totaled_Billed
from upr30100
inner join twupr400 on (upr30100.employid = twupr400.employid)
right join rm00101 on (substring(twupr400.employid,3,3) = rm00101.custnmbr)
right join tw00111 on (rm00101.custnmbr = tw00111.custnmbr)
left join upr30300 on (upr30100.employid = upr30300.employid and twupr400.payrcord = upr30300.payrolcd )
where upr30100.employid like '010420003%' and tw00111.date1 = '2005-07-13 00:00:00.000'
and (twupr400.payrcord = 'HOURLY' or twupr400.payrcord = 'OT')
and (upr30300.chekdate = '2005-06-24 00:00:00.000' and (upr30300.payrolcd = 'HOURLY' or upr30300.payrolcd = 'OT'))
order by tw00111.thinkinvno, upr30100.emplname

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-07-14 : 18:45:12

can you post the table structure, some sample data, and a desired result?

greg52
Starting Member

5 Posts

Posted - 2005-07-15 : 09:00:08

Table rm00101 contains the customer number (rm00101.custnmbr) (ie "042'), the customer name (rm00101.custname) (ie "Smith Inc.), and a shortened customer name (rm00101.pymtrmid) (ie "Smith").
Table tw00111 contains the customer number (tw00111.thinkinvno) and the payroll check date (tw00111.date1).
Table upr30100 contains the employee name (upr30100.emplname) and the employee number (upr30100.employid).
Table twupr400 contains the employee job title (twupr400.tw_billing_info), the payrole code (twupr400.payrcord) (ie HOURLY, OT, etc), and the employee's hourly/OT rate (twupr400.tw_bill_rate).
Table upr30300 contains the regular/OT hours worked by the employee (upr30300.untstopy).

The rate and hours worked for one employee has two separate rows in tables upr30300 and twupr400. One with a paycode of Hours, the hours worked and the pay rate and the other for OT hours with similar data. I am trying to combine the hours, rate, and the product of the two values for both regular hours and OT hours on the same output line and shown in my first message post. What I am getting now is two rows - one for regular hours and one for OT hours. Is what I'm trying to accomplish even possible? Thank you for your interest.

greg52
Starting Member

5 Posts

Posted - 2005-07-15 : 14:37:57

I figured it out. Created two temp tables, one for regular hours and the other for OT, and then queried on both to get the single row. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources