Sproc

  • 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.

     

  • 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 !!!**

  • 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