Date Calcluation in Query

[feshangi]

I would like to add to my query the following logic but I don't know how to right it:

WHERE

IF
Date800 IS NOT NULL
THEN
(Date802 + 75 Days) < Date803
END

Can someone please help me with my situation?

Thanks,

Mike

 

[SecondToNone]

did you mean something like this:

If date800<>"" THEN

DateAdd(d,75,date802) < date803

End

-SecondToNone

 

[Catadmin]

You might keep your 'is null' arguement as well as adding SecondToNone's blank space argument. I've noticed that SQL doesn't interpret them the same and it will vary your returned records.

So, if you want to check for both, try:

If (date800<>"" or date800 is null) THEN

DateAdd(d,75,date802) < date803

End





Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[feshangi]

thx for the replies.

i'm getting this error when using <> "":

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use empty object or column names. Use a single space if necessary.
The error occurred on line 158.

 

[feshangi]

I changed <> "" to <> '' and worked and now let try the rest of it.

 

[pkailas]

Or you could try

If len(date800) > 0 then
  DateAdd(d,75,date802) < date803
end

_______
I love small animals, especially with a good brown gravy....

 

[feshangi]

Now how can I integrate this logic with my query?

If (Date800 = '' or Date800 IS NULL) 
		THEN
		PlanYrEnd <= #CreateODBCDate(DateAdd("d",75,Back5Months))#
		ELSE
		PlanYrEnd <= #CreateODBCDate(Back5Months)#
		End

 

[SecondToNone]

i think you are doing the other way round...

when you first stated your logic...you wanted to add days if your Date800 field is NOT null...in the above query you are doing the opposite...

-SecondToNone

 

[feshangi]

You are right.

here is what i exactly want:

If (Date800 <> '' or Date800 IS NOT NULL) 
        THEN
        PlanYrEnd <= #CreateODBCDate(DateAdd("d",75,Back5Months))#
        ELSE
        PlanYrEnd <= #CreateODBCDate(Back5Months)#
        End

But I can't just put this on my WHERE clause.

Please help.

Thanks,

Mike

 

[SecondToNone]

put it as a case statement...

Show us your complete query...

-SecondToNone

 

[feshangi]

Below is my query. Thx for taking your time on this.

	<CFQUERY datasource="PGI" name="GetWorkflow">
		SELECT 	ClientDB.CustNum, 
				PlanDB.PlanName, 
				PlanDB.PlanCodeId, 
				ClientDB.Company, 
				AnnualDB.RecNum, 
				AnnualDB.ClientId, 
				AnnualDB.PlanId, 
				AnnualDB.PlanYrEnd, 
				AnnualDB.Date100, 
				AnnualDB.Date101, 
				AnnualDB.Date102, 
				AnnualDB.Date103, 
				AnnualDB.Date104,
				AnnualDB.Chk100, 
				AnnualDB.Chk101, 
				AnnualDB.Chk102, 
				AnnualDB.Chk103, 
				AnnualDB.Chk104, 
				AnnualDB.Date200, 
				AnnualDB.Date201, 
				AnnualDB.Date202, 
				AnnualDB.Date203, 
				AnnualDB.Date204, 
				AnnualDB.Chk200, 
				AnnualDB.Chk201, 
				AnnualDB.Chk202, 
				AnnualDB.Chk203, 
				AnnualDB.Chk204, 
				AnnualDB.Date300, 
				AnnualDB.Date301, 
				AnnualDB.Date302, 
				AnnualDB.Date303, 
				AnnualDB.Date304, 
				AnnualDB.Date305, 
				AnnualDB.Date306, 
				AnnualDB.Chk300, 
				AnnualDB.Chk301, 
				AnnualDB.Chk302, 
				AnnualDB.Chk303, 
				AnnualDB.Chk304, 
				AnnualDB.Chk305, 
				AnnualDB.Chk306, 
				AnnualDB.Date400, 
				AnnualDB.Date401, 
				AnnualDB.Date402, 
				AnnualDB.Date403, 
				AnnualDB.Date404, 
				AnnualDB.Date405, 
				AnnualDB.Date406, 
				AnnualDB.Chk400, 
				AnnualDB.Chk401, 
				AnnualDB.Chk402, 
				AnnualDB.Chk403, 
				AnnualDB.Chk404, 
				AnnualDB.Chk405, 
				AnnualDB.Chk406, 
				AnnualDB.Date500, 
				AnnualDB.Date501, 
				AnnualDB.Date502, 
				AnnualDB.Date503, 
				AnnualDB.Date504, 
				AnnualDB.Chk500, 
				AnnualDB.Chk501, 
				AnnualDB.Chk502, 
				AnnualDB.Chk503, 
				AnnualDB.Chk504, 
				AnnualDB.Date600, 
				AnnualDB.Date601, 
				AnnualDB.Date602, 
				AnnualDB.Date603, 
				AnnualDB.Date604, 
				AnnualDB.Date605, 
				AnnualDB.Chk600, 
				AnnualDB.Chk601, 
				AnnualDB.Chk602, 
				AnnualDB.Chk603, 
				AnnualDB.Chk604, 
				AnnualDB.Chk605, 
				AnnualDB.Date700, 
				AnnualDB.Date701, 
				AnnualDB.Date702, 
				AnnualDB.Date703, 
				AnnualDB.Date704, 
				AnnualDB.Date705, 
				AnnualDB.Chk700, 
				AnnualDB.Chk701, 
				AnnualDB.Chk702, 
				AnnualDB.Chk703, 
				AnnualDB.Chk704,
				AnnualDB.Chk705, 
				AnnualDB.Date800, 
				AnnualDB.Date801, 
				AnnualDB.Date802, 
				AnnualDB.Date803, 
				AnnualDB.Date804, 
				AnnualDB.Date805, 
				AnnualDB.Date806, 
				AnnualDB.Date807, 
				AnnualDB.Chk800, 
				AnnualDB.Chk801, 
				AnnualDB.Chk802, 
				AnnualDB.Chk803, 
				AnnualDB.Chk804, 
				AnnualDB.Chk805, 
				AnnualDB.Chk806, 
				AnnualDB.Chk807, 
				AnnualDB.Date850, 
				AnnualDB.Date851, 
				AnnualDB.Date852, 
				AnnualDB.Date853, 
				AnnualDB.Chk850, 
				AnnualDB.Chk851, 
				AnnualDB.Chk852, 
				AnnualDB.Chk853, 
				AnnualDB.Date900, 
				AnnualDB.Date901, 
				AnnualDB.Date902, 
				AnnualDB.Date903, 
				AnnualDB.Date904, 
				AnnualDB.Chk900, 
				AnnualDB.Chk901, 
				AnnualDB.Chk902, 
				AnnualDB.Chk903, 
				AnnualDB.Chk904, 
				AnnualDB.TopHvy, 
				AnnualDB.Pbgc, 
				AnnualDB.SafeHarborYr, 
				AnnualDB.OnExt, 
				AnnualDB.ChkTopHvy, 
				AnnualDB.ChkPbgc, 
				AnnualDB.ChkSafeHarborYr, 
				AnnualDB.ChkOnExt, 
				AnnualDB.EmpNumID, 
				AnnualDB.Note, 
				AnnualDB.LastModified
		FROM 	PGI.dbo.AnnualDB AnnualDB, 
				PGI.dbo.ClientDB ClientDB, 
				PGI.dbo.PlanDB PlanDB
		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 <= #CreateODBCDate(Back5Months)#
******************************
*		IF (Date800 = '' or Date800 IS NULL) 
*		PlanYrEnd <= #CreateODBCDate(DateAdd("d",75,Back5Months))#
*		ELSE
*		PlanYrEnd <= #CreateODBCDate(Back5Months)#
*		END
******************************
		Order	by Month(PlanYrEnd), CustNum asc
	</CFQUERY>

 

[Catadmin]

What exactly is "CreateODBCDate" supposed to be doing?

Also, in SQL, you don't use the # to surround dates. Use the single quote ' to surround dates. # is for Access.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[dbomrrsm]

Are you using SQL Server or Access ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[feshangi]

Those #s are for output in ColdFusion and that's why my query is saying CFQUERY. The query works fine except the part that I would like to add to it. I have not clue how to integrate that IF ELSE with my query.

I'm using SQL 2000.

 

[feshangi]

In regard to CreateODBCDate that's a date converter in coldusion so basically I am adding that 75 days in coldfusion and not SQL.