CFQUERY with IF THEN ELSE

[mike2004]

I have a CFQUERY which works perfectly and now I would like to add a little
date calculation to this query to filter it little more. The query is a simple
query like <CFQUERY>SELECT * FROM XYZ WHERE Date100 = '01/01/2005' and now here
is what I would like to add to it: IF (Date800 = '' or Date800 IS NULL) AND
PlanYrEnd <= #CreateODBCDate(DateAdd('d',75,Back5Months))# ELSE AND PlanYrEnd
<= #CreateODBCDate(Back5Months)# END Note: Date800 and PlanYrEnd are columns
on my SQL 2000 table and Back5Months is a ColdFusion Date Variable. If a the
first statement is true I would like to add 'AND PlanYrEnd <=
#CreateODBCDate(DateAdd('d',75,Back5Months))#' to my WHERE Clause and if it is
not true then I would like to add 'AND PlanYrEnd <=
#CreateODBCDate(Back5Months)#' to my WHERE Clause. I don't know how to do
this. Some on different told me about using Case Statements but I had no clue
what they were talking about. I would really appriciate if someone can help me
to rewrite this query. Thanks, Mike:Q

[paross1]

Is this closer to what you had in mind?

Phil



SELECT *
FROM XYZ
WHERE Date100 = '01/01/2005'
AND (((Date800 = '' OR Date800 IS NULL) AND PlanYrEnd <=
#CreateODBCDate(DateAdd("d",75,Back5Months))#)
OR PlanYrEnd <= #CreateODBCDate(Back5Months)#)

[mike2004]

Thanks paross1 for your reply. I tried your query and the output is still
showing all the extra data which I don't wanna show! Please advice. I'm sure
we both are in the right direction but need to work together to finalize the
codes.

[paross1]

I didn't think this version out too much, but you get the idea....

Phil



SELECT *
FROM XYZ
WHERE Date100 = '01/01/2005'
AND (((Date800 = '' OR Date800 IS NULL) AND PlanYrEnd <=
#CreateODBCDate(DateAdd("d",75,Back5Months))#)
OR ((Date800 <> '' OR Date800 IS NOT NULL) AND PlanYrEnd <=
#CreateODBCDate(Back5Months)#))

[mike2004]

No Luck!!! Now I am even getting more outputs.

[paross1]

Which records are you seeing that you do not expect? In other words, what are the values of Date100, Date800, PlanYrEnd, and Back5Months for the "offending" rows?

Phil

[mike2004]

Bsically I would like to add 75 Days to Variable Back5Month if there's a date in field DATE800. This has to apply to each row so the only way I can this is to include this in the QUERY.

[paross1]

What am I missing? This seems to me like it would give you what you are trying
to get, unless my brain just isn't working logically today.

Phil

SELECT *
FROM XYZ
WHERE Date100 = '01/01/2005'
AND PlanYrEnd =
CASE
WHEN (Date800 = '' OR Date800 IS NULL) THEN
#CreateODBCDate(DateAdd("d",75,Back5Months))#
WHEN (Date800 <> '' AND Date800 IS NOT NULL) THEN
#CreateODBCDate(Back5Months)#
END

[mike2004]

Now I am getting syntax error:

WHERE ClientDB.RecNum = AnnualDB.ClientId
AND PlanDB.ClientId = AnnualDB.ClientId
AND PlanDB.RecNum = AnnualDB.PlanId
AND PlanStatusId < '3'
AND (((Date805 IS NULL OR Date806 = '') AND (Chk805 = '0' OR Chk805 IS NULL
OR Chk805 = '')) OR ((Date806 IS NULL OR Date806 = '') AND (Chk806 = '0' OR
Chk806 IS NULL OR Chk806 = '')))
AND PlanYrEnd <= #CreateODBCDate(EndOfThisMonth)#
AND PlanYrEnd >= #CreateODBCDate(Back11Months)#
CASE
WHEN Date800 = '' OR Date800 IS NULL THEN
#CreateODBCDate(DateAdd("d",75,Back5Months))#
ELSE #CreateODBCDate(Back5Months)#
END

Order by Month(PlanYrEnd), CustNum asc

[paross1]

You forgot the AND PlanYrEnd <= CASE.... part. By the way, I am assuming SQL
Server DB.

Phil



WHERE ClientDB.RecNum = AnnualDB.ClientId
AND PlanDB.ClientId = AnnualDB.ClientId
AND PlanDB.RecNum = AnnualDB.PlanId
AND PlanStatusId < '3'
AND (((Date805 IS NULL OR Date806 = '') AND (Chk805 = '0' OR Chk805 IS NULL OR
Chk805 = '')) OR ((Date806 IS NULL OR Date806 = '') AND (Chk806 = '0' OR Chk806
IS NULL OR Chk806 = '')))
AND PlanYrEnd <= #CreateODBCDate(EndOfThisMonth)#
AND PlanYrEnd >= #CreateODBCDate(Back11Months)#
AND PlanYrEnd <=
CASE
WHEN Date800 = '' OR Date800 IS NULL THEN
#CreateODBCDate(DateAdd("d",75,Back5Months))#
ELSE #CreateODBCDate(Back5Months)#
END
Order by Month(PlanYrEnd), CustNum asc

[mike2004]

Perfect. Thanks a lot.

Mike :beer;