Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Unanswered: Query Problem Need help

    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:

    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 = <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#">)
    Last edited by xerogee; 09-23-10 at 13:52.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    When you use TOP 1, you must take steps that you get a reproducible result. I guess you will have to ORDER BY the us.DateCreated.

    I tried to solve it, but I have too little information to retrieve the DaysLeft = 0 dataset.
    if the column (DaysLeft) is less than 1 meaning the subscription is about to run out. If it does get to 0,
    "Less than 1", like 0? Then you want to take the same action in both cases. Can you clarify?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2010
    Posts
    4
    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.


    Thanks again I greatly appreciate it.

    Thanks in advance...
    Last edited by xerogee; 09-24-10 at 22:41.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •