OK, I have a problem that I am trying to solve in one query. What I am trying to do is select the top record only if the daysleft is less than 1 other wise select the last record by Date. So the query that I am using does this but I am having a problem with the part where it needs to select when the query Column is less than 1. Can any one give me an Idea of how to accomplish this. By the way I am using Coldfusion as well. Thanks in advance.
In essence for a clearer understanding of what I am trying to do:
I am trying to select the last row only if the column (DaysLeft) is less than 1 meaning the subscription is about to run out. If it does get to 0, I should be looking at the very first record which was inserted upon registration. Is there a way to do this in one query or is it better to use a stored procedure for this?
here is the code:
SELECT TOP 1 cp.website, cp.email, c.name AS CompName, u.username, u.userID, ust.name AS SubName, ust.price, ust.DaysSubscribed, rol.roleID,
us.dateCreated,DATEDIFF(day,getDate(),us.DateCreated + DaysSubscribed) AS DaysLeft
FROM tbl_user AS u INNER JOIN
tbl_company AS c ON u.userID = c.userID INNER JOIN
tbl_company_profile AS cp ON c.companyID = cp.companyID INNER JOIN
tbl_user_subscription AS us ON u.userID = us.userID LEFT OUTER JOIN
tbl_user_role AS rol ON u.roleID = rol.roleID LEFT OUTER JOIN
tbl_user_subscription_type AS ust ON us.typeID = ust.typeID
WHERE u.userID = somenumericnumber
AND us.DateCreated = (Select Max(DateCreated) From tbl_user_subscription Where userID = somenumericnumber)