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 query in question:
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 = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userID#">
AND us.DateCreated = (Select Max(DateCreated) From tbl_user_subscription Where userID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userID#">)
OK so here is a scenario which is what I am trying to accomplish:
When someone logs in for the first time they already have a free subscription which was given to them upon registration. So if they upgrade to another subscription type IE (Silver,Gold,Platinum Etc..) this inserts a new record into the table giving them that membership Type,and the query should look at the last DateCreated hence this part
(AND us.DateCreated = (Select Max(DateCreated) From tbl_user_subscription Where userID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userID#">) it also starts a count down of Days ie the (DATEDIFF(day,getDate(),us.DateCreated + DaysSubscribed) AS DaysLeft
) Then once the day is Less than 1 or = to 0 it should insert a new record into the subscription table as free membership automatically. I am open to suggestions on how to get this to work in one query.
I forgot about one more thing. This query should also only update to the free membership only if the Days have counted down to 0 and the subscriptionType is Not Free. So something like this: if daysLeft < 1 AND Subname Is Not "Free" Then do an insert. I hope this gives whoever helps me a better Idea of what I am trying to accomplish. As i am still pondering how to formulate this query.
The Reason why I added the second part is because when a member subscribes to a subscription other than a Free Subscription, the member needs to be able to utilize their last subscription type until there are no more days left.
For better understanding:
Scenario 1: User 1 logs on who has a silver membership and clicks on a tool which only will allow him/her to utilize that tool if he/she has a membership type of Gold.
The user clicks to upgrade to the gold membership but has 20 days left under the current membership. In order to get the full amount of days under the silver after the user has upgraded to the gold the user will need to wait until the silver expires then the new membership from which he/she upgraded to is now the row from which the query is looking at.