Trouble setting a case statement

Question

Post reply

Trouble setting a case statement

Rank1_nj

Mr or Mrs. 500

Points: 535
More actions
June 27, 2011 at 1:35 pm

#243369

Hello,
I have a query that works fine. But as I started adding more records to my table, I saw that it wasn't working properly. Here's my query
Select
datediff(d,Start_Date,End_Date) - (select count(*) from table_b where table_b.datecol >= table_a.StartDate and table_b.datecol <= table_a.EndDate) As TotalDays
from Table_A [\code]
I have tried case statements on this but I keep getting syntax errors. Can anyone help me with a case statement where it states that if a column's value in TableA is 'OK' then only to execute the above statement?

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

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

Select

datediff(d,Start_Date,End_Date) - (select count(*))

from table_b

where table_b.datecol >= table_a.StartDate

and table_b.datecol <= table_a.EndDate) As TotalDays

from Table_A

I am not sure I understand what you are trying to acomplish with this Query.

you are doing a select from Table_B and then after that you have another line that says from table A What is the ultimate goal and can you post the actual cose you are having a problem with?

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 2

Rank1_nj (6/27/2011)
Hello,
I have a query that works fine. But as I started adding more records to my table, I saw that it wasn't working properly. Here's my query
Select
datediff(d,Start_Date,End_Date) - (select count(*) from table_b where table_b.datecol >= table_a.StartDate and table_b.datecol <= table_a.EndDate) As TotalDays
from Table_A [\code]
I have tried case statements on this but I keep getting syntax errors. Can anyone help me with a case statement where it states that if a column's value in TableA is 'OK' then only to execute the above statement?

Could you give us some example data?

BTW, "Start_Date" and "End_Date" vs. "StartDate" and "EndDate" --- typo? 😉

Rank1_nj Mr or Mrs. 500 Points: 535 More actions · Answer 3

I solved this by just adding variables & then putting IF ELSE conditions to perform the tasks. So it was something along the lines of:

DECLARE

@user-id int,

@RandomCol varchar (100),

SET

@user-id = 12101

SET

@RandomCol = (Select RandomCol from TableA where UserID = @user-id AND RandomCol = 'YES')

IF @RandomCol = 'YES'

BEGIN

SELECT ***Query

END

ELSE

select statement