July 29, 2005 at 8:51 am
I'm transfering from access to sql. I've got the query working and set up the sproc. What I'm having a problem with is that in access the expression for the rate was CInt(Sum([hrs]*10000)), but sql does not like Cint and I only want the rate to show as 192400 and not as its coming out 192400.00. Also the SUM(s.Hrs * c.Payrate)AS Amount, can this be converted to a money value?
I've posted the full query.
SELECT e.Pay_No, e.Forename, e.Surname, 12 AS CC, SUM(s.Hrs * 100) AS Hours, convert (varchar (10), c.Payrate * 100000 )AS Rate,
case when c.payrate = 19.24 then '325'
when c.payrate = 17.07 then '321'
when c.payrate = 20.26 then '322'
when c.payrate = 23.63 then '323'
when c.payrate = 20.60 then '324'
when c.payrate in(27.58,19.80,20.80,26.91)then '324'
when c.payrate < 13.00 or c.payrate > 30.00 then '324'
end 'HED',
ss.CSM, ss.Team_No,c.NomCode, SUM(s.Hrs * c.Payrate)AS Amount, c.Status, e.DCinput, c.DSInput, e.DateletSigned
FROM Sessions s INNER JOIN Contracts c ON s.ContractID = c.ContractID INNER JOIN [Schools/Service] ss
ON c.BudCode = ss.Budget_Code INNER JOIN Emp_PTCOE_vw e ON c.Pay_No = e.Pay_No
GROUP BY e.Pay_No, e.Surname, e.Forename, s.Claimed, ss.CSM, ss.Team_No, c.NomCode, c.Payrate * 10000, c.Claim, c.Status, e.DCinput, c.DSInput,
e.DateletSigned,payrate
HAVING (s.Claimed > N'0') AND (c.Claim = 1) AND (c.Status = 'a' OR c.Status = 'c') AND (e.DCinput < GETDATE())
AND (c.DSInput < GETDATE()) AND (e.DateletSigned < GETDATE())
ORDER BY e.Surname, e.Forename, ss.CSM, ss.Team_No
Hope someone can help.
July 29, 2005 at 9:08 am
You can do this:
select cast(c.Payrate * 100000 as int)
select cast(SUM(s.Hrs * c.Payrate) as money)
**ASCII stupid question, get a stupid ANSI !!!**
July 29, 2005 at 9:12 am
Thanks, that works fine, Still learning SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply