Datatype in CTE

Question

Datatype in CTE

Mike Levan

SSChampion

Points: 14201
More actions
August 1, 2008 at 11:11 am

#196396

;WITH emps(empID,Percent)
AS
(
SELECT
empID,
case when COUNT(DISTINCT payid)>4 then (COUNT(DISTINCT payid)/5)*100
else (COUNT(DISTINCT payid)/4)*100
end
FROM
paytbl INNER JOIN
#tmpemp ON #tmpemp.Value = paytbl.empId
)
select * from emps
I am not getting the floating number for percent. How could i make my percent as float datatype in CTE?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steven Cameron SSCrazy Points: 2903 More actions · Answer 1

When you divide an integer by an integer, you get an integer. Change the /5 and /4 to be /5.0 and /4.0 and you should get a float.

Mike Levan SSChampion Points: 14201 More actions · Answer 2

DECLARE @Month int

set @Month=1

;WITH emps(empID)

AS

(

SELECT empID FROM

#tmpemp

)

select @month,hours

from emps inner join hours

on emps.empid=hours.empid

I get only for 1 month from this query, how would i calcualte for all 12 months. i can do it through cursor but how abt CTE?

something like @Month+1 upto 12.

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 3

This is why we always ask for a description of what you're trying to do... are you trying to generate 12 otherwise identical rows for each employee or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)