SQL Query w/ Dynamic String

[NervousRex]

I need to change up an existing query in an application, I have no CF expierence so need the details, as I have no clue on debugging or anything like that.

Existing query:

		<cfquery name="qry_select_client_contact" datasource="#request.dsn#" dbtype="#request.dbtype#">
			select a.contact_for as biz_association_id, a.contact_type, a.client_id, a.contact_id, a.contact, b.client_first_name + ' ' + b.client_last_name as client_name, b.client_company_name as company_name, b.client_first_name + ' ' + b.client_last_name as full_name
  			  from v_Client_Contact_Preferences a, v_calling_clients b, v_Nightly_Calendar_Recipients c
		     where a.BP_Contact_Name='Calendar' AND
		       	   a.contact_for=b.biz_association_id AND
		       	   a.contact_for=c.biz_association_id AND
		       	   c.send_calendars=1 AND
		       	   b.active_status=1 AND
		       	   b.on_hold_status=0 AND
		       	   b.orientation_released=1
			order by b.client_last_name, b.client_first_name, a.contact_type
		</cfquery>

In the Where clause, I need to add a dynamic line to it that is <day_of_week> = 1

I got the DayOfWeekAsString(DayOfWeek(Now())) part I think, but I don't know CF syntax enough to add the dynamic stuff into the query string.

Appreciate any help

 

[philhege]

What's dynamic? Is the dayofweek always going to be equal to 1? Does it have to match something else in the query? Is there a value passed from a form that you need to include in the query?

More info, please.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[NervousRex]

the day of the week is dynamic, if its run on monday I need the query to say WHERE Monday = 1

Tuesday

Tuesday = 1


my question is more how do I add that into the string?

WHERE a.BP_Contact_Name='Calendar' AND
a.contact_for=b.biz_association_id AND
a.contact_for=c.biz_association_id AND
c.send_calendars=1 AND
b.active_status=1 AND
b.on_hold_status=0 AND
b.orientation_released=1 AND
DayOfWeekAsString(DayOfWeek(Now())) = 1

 

[philhege]

I'm still not clear on this. Are you looking to compare the dayofweek value of a date in the table with the dayofweek of the current date?

There are two ways you can do this. Your example is using CF syntax, and you would simply enclose the function in pound signs. This causes the CF engine to replace the function call with its value in the SQL statement. The other method is to use the database's internal date functions. My example uses Transact-SQL.

WHERE a.BP_Contact_Name='Calendar' AND
a.contact_for=b.biz_association_id AND
a.contact_for=c.biz_association_id AND
c.send_calendars=1 AND
b.active_status=1 AND
b.on_hold_status=0 AND
b.orientation_released=1 AND
datepart(dw,yourdatefield) = datepart(dw,getdate())

 

[NervousRex]

the pound signs were what I was looking for, thanks