RE: [KCFusion] DateDiff in SQL Query

2003-08-04 Thread Kory Bakken 



You cannot treat 
CustomerEnrollmentDateas a CF Variable until you are 
outside of that query. The following where clause should get you what you 
need:

WHERE CustomerRefer = #Affiliate_ID#AND 
CustomerEnrollmentDate between '#DateAdd('d',-7,createODBCDate(now()))#' and '#DateAdd('d',7,createODBCDate(now()))#'

  -Original Message-From: Robert 
  [mailto:[EMAIL PROTECTED]Sent: Friday, August 01, 2003 11:03 
  PMTo: [EMAIL PROTECTED]Subject: [KCFusion] DateDiff 
  in SQL Query
  I am running into a problem and I can't figure 
  out why. I know i've had this problem before with using CF functions 
  inside of SQL queries, but I can never remember what it was the last time that 
  fixed it, so I wanted to post this query:
  
  CFQUERY name="getcustomers" 
  DATASOURCE="x"SELECT CustomerName, CustomerPin, 
  CustomerRefer,CustomerEnrollmentDateFROM 
  CustomersWHERE CustomerRefer = 
  #Affiliate_ID#AND 
  #DateDiff("w",'#CreateODBCDate(CustomerEnrollmentDate)#','#CreateODBCDate(now())#')# 
  = 0/CFQUERY
  I'm needing to run stats for affiliate programs 
  and so I want a daily, weekly, monthly view. Datediff in T-SQL didn't 
  seem to be able to do anything other than generic day, month, etc. 
  Anyhow, I get the following error:
  
  Error 
  resolving parameter CUSTOMERENROLLMENTDATE
  
  Obviously I 
  have the right name, but it bombs right there...if I change around my quotes 
  and my pound signs, it'll say it's Missing a parameter, Expected 1 or Expected 
  2. Anyone easily point out what i've got in the wrong place or what I am 
  messing up because i'm at a loss. BTW, Affiliate_ID is from another 
  CFOUTPUT from a query that this is all running inside of.
  
  Thanks,
  Robert

Re: [KCFusion] DateDiff in SQL Query

2003-08-04 Thread Robert 



Kory,

Thanks for the reply. I was able to figure 
out my mistake. It was my own fault of syntax and not having the quotes 
and such in the right place. I've abandoned that query, but I will say 
thanks to all who helped.

Thanks,
Robert

  - Original Message - 
  From: 
  Kory Bakken 
  To: [EMAIL PROTECTED] 
  Sent: Monday, August 04, 2003 8:10 
  AM
  Subject: RE: [KCFusion] DateDiff in SQL 
  Query
  
  You cannot treat 
  CustomerEnrollmentDateas a CF Variable until you are 
  outside of that query. The following where clause should get you what 
  you need:
  
  WHERE CustomerRefer = #Affiliate_ID#AND 
  CustomerEnrollmentDate between '#DateAdd('d',-7,createODBCDate(now()))#' and '#DateAdd('d',7,createODBCDate(now()))#'
  
-Original Message-From: Robert 
[mailto:[EMAIL PROTECTED]Sent: Friday, August 01, 2003 11:03 
PMTo: [EMAIL PROTECTED]Subject: [KCFusion] DateDiff 
in SQL Query
I am running into a problem and I can't figure 
out why. I know i've had this problem before with using CF functions 
inside of SQL queries, but I can never remember what it was the last time 
that fixed it, so I wanted to post this query:

CFQUERY name="getcustomers" 
DATASOURCE="x"SELECT CustomerName, CustomerPin, 
CustomerRefer,CustomerEnrollmentDateFROM 
CustomersWHERE CustomerRefer = 
#Affiliate_ID#AND 
#DateDiff("w",'#CreateODBCDate(CustomerEnrollmentDate)#','#CreateODBCDate(now())#')# 
= 0/CFQUERY
I'm needing to run stats for affiliate programs 
and so I want a daily, weekly, monthly view. Datediff in T-SQL didn't 
seem to be able to do anything other than generic day, month, etc. 
Anyhow, I get the following error:

Error 
resolving parameter CUSTOMERENROLLMENTDATE

Obviously I 
have the right name, but it bombs right there...if I change around my quotes 
and my pound signs, it'll say it's Missing a parameter, Expected 1 or 
Expected 2. Anyone easily point out what i've got in the wrong place 
or what I am messing up because i'm at a loss. BTW, Affiliate_ID is 
from another CFOUTPUT from a query that this is all running inside 
of.

Thanks,
Robert

[KCFusion] DateDiff in SQL Query

2003-08-01 Thread Robert 



I am running into a problem and I can't figure out 
why. I know i've had this problem before with using CF functions inside of 
SQL queries, but I can never remember what it was the last time that fixed it, 
so I wanted to post this query:

CFQUERY name="getcustomers" 
DATASOURCE="x"SELECT CustomerName, CustomerPin, 
CustomerRefer,CustomerEnrollmentDateFROM 
CustomersWHERE CustomerRefer = #Affiliate_ID#AND 
#DateDiff("w",'#CreateODBCDate(CustomerEnrollmentDate)#','#CreateODBCDate(now())#')# 
= 0/CFQUERY
I'm needing to run stats for affiliate programs and 
so I want a daily, weekly, monthly view. Datediff in T-SQL didn't seem to 
be able to do anything other than generic day, month, etc. Anyhow, I get 
the following error:

Error 
resolving parameter CUSTOMERENROLLMENTDATE

Obviously I 
have the right name, but it bombs right there...if I change around my quotes and 
my pound signs, it'll say it's Missing a parameter, Expected 1 or Expected 
2. Anyone easily point out what i've got in the wrong place or what I am 
messing up because i'm at a loss. BTW, Affiliate_ID is from another 
CFOUTPUT from a query that this is all running inside of.

Thanks,
Robert

Re: [KCFusion] DateDiff in SQL Query

2003-08-01 Thread Adaryl Wakefield 



Well all my reference books are at work and 
as it is firday night and im on my way out the door to go party just taking a 
quick glance at it but (and I may just be missing a piece of the puzzle here) 
but since you are selecting out CustomerEnrollementDate in the query don't you 
have to assign it a value before you use it in the DateDiff function, or did you 
already do that and it does not show up in your snippet?A.

  - Original Message - 
  From: 
  Robert 
  To: [EMAIL PROTECTED] 
  Sent: Friday, August 01, 2003 11:02 
  PM
  Subject: [KCFusion] DateDiff in SQL 
  Query
  
  I am running into a problem and I can't figure 
  out why. I know i've had this problem before with using CF functions 
  inside of SQL queries, but I can never remember what it was the last time that 
  fixed it, so I wanted to post this query:
  
  CFQUERY name="getcustomers" 
  DATASOURCE="x"SELECT CustomerName, CustomerPin, 
  CustomerRefer,CustomerEnrollmentDateFROM 
  CustomersWHERE CustomerRefer = 
  #Affiliate_ID#AND 
  #DateDiff("w",'#CreateODBCDate(CustomerEnrollmentDate)#','#CreateODBCDate(now())#')# 
  = 0/CFQUERY
  I'm needing to run stats for affiliate programs 
  and so I want a daily, weekly, monthly view. Datediff in T-SQL didn't 
  seem to be able to do anything other than generic day, month, etc. 
  Anyhow, I get the following error:
  
  Error 
  resolving parameter CUSTOMERENROLLMENTDATE
  
  Obviously I 
  have the right name, but it bombs right there...if I change around my quotes 
  and my pound signs, it'll say it's Missing a parameter, Expected 1 or Expected 
  2. Anyone easily point out what i've got in the wrong place or what I am 
  messing up because i'm at a loss. BTW, Affiliate_ID is from another 
  CFOUTPUT from a query that this is all running inside of.
  
  Thanks,
  Robert