The following solution is for SQL Server, I imagine most other DBMSs have a
similar function, so if you aren't using SQL Server it should still lead you
in the right direction.
Assuming that the value stored in YearsPaid is always a number of years (as
opposed to a number of months) and that DateofPayment is a datetime field,
you could use the DateAdd function.
DateAdd(Year, DuesAdmin.YearsPaid, DuesAdmin.DateofPayment) - will give you
a new datetime that is the DateofPayment plus the number of years in
YearsPaid.
Use this in a Where clause like:
Where DateAdd(Year, DuesAdmin.YearsPaid, DuesAdmin.DateofPayment) <
GetDate()
This will give you all of the records for people who have expired.
hth,
Bob
-----Original Message-----
From: Owens, Howard [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 19, 2000 7:01 PM
To: CF-Talk
Subject: (SQL) Date column x year column
I hope somebody can help me out with something that is looking like it's a
SQL problem more advanced than I can handle.
I have two table -- Primary and DuesAdmin. I need to select data from each
table. The WHERE clause needs to be based on the combination of two columns
compared to today's date.
The first clauses would look something like this:
SELECT Primary.FirstName
Primary.LastName
Primary.Email
DuesAdmin.DateofPayment
DuesAdmin.YearsPaid
FROM Primary, DuesAdmin
OK.
Now it gets tricky.
I need to mulitiply DateofPayment by YearsPaid. If that creates a date that
is something short of day, I want to select that row.
To put it another way, I've got people who paid 18 months ago and paid for
one year of membership. I want to find those expired accounts. I've also
got people who paid 3 years ago and paid for 3 years (or 2 years, etc.).
Apparently, I can't multiple the columns and then do a compare (less than)
with today's date.
OK, SQL guru's, how would you do this?
H.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
