Click here to Skip to main content
15,038,237 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
select case duration when 'Half Day' then '.5' when 'Full Day' then '1' when '"Hour*"' then dbo.GetNumbers(duration) else dbo.GetNumbers(duration) end as DURATION from tbl_leave



Above is an SQL QUERY,the result is

VB
DURATION
1
115
1
3
.5


i want the sum of the DURATION,can anyone help me?/
Posted
Updated 6-Mar-11 22:37pm
v2

1 solution

did you try this
SQL
select Sum(case duration when 'Half Day' then '.5' when 'Full Day' then '1' when '"Hour*"' then dbo.GetNumbers(duration) else dbo.GetNumbers(duration) end) as DURATION from tbl_leave


other wise use this

SQL
select sum(cast(duration as float)) from (
select case duration when 'Half Day' then '.5' when 'Full Day' then '1' when '"Hour*"' then dbo.GetNumbers(duration) else dbo.GetNumbers(duration) end as DURATION from tbl_leave) as LeaveDuration


both of these should work.

--Pankaj
   
v2
Comments
kishore Rajendran 7-Mar-11 4:46am
   
Both of this will not work
pankajupadhyay29 7-Mar-11 4:47am
   
is some error there?
kishore Rajendran 7-Mar-11 4:47am
   
sum(duration) at first wont work because,duration is a VARCHAR field consisting of both full text like 'kishore' and half text like'kish123' and '12345' etc
pankajupadhyay29 7-Mar-11 4:49am
   
your output is numeric use that as numeric value this will work.
pankajupadhyay29 7-Mar-11 4:59am
   
what values you r getting by your query are numeric for that you can change query like this sum(cast(duration as float)) i made the changes in second query in solution check that.
kishore Rajendran 7-Mar-11 5:00am
   
Second answer is correct
kishore Rajendran 7-Mar-11 5:01am
   
Thanx
pankajupadhyay29 7-Mar-11 5:02am
   
my pleasure.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900