Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    3

    Unanswered: SP help needed, query of query thing

    I need some help writing a stored procedure for an application.

    I have two tables:

    Ads
    -----
    AdID
    AdTitle
    AdStartDate (datetime)
    AdEndDate (datetime)
    AdBudget (money)


    AdClicks
    ------
    AdClickID
    AdID
    AdClickDateTime
    AdClickCost


    .. The idea is that Ads contain ads (duh) and when a user clicks on the ad, a record is added to the AdClicks table with an AdClickCost, say $1. The AdBudget is specified, so an ad could have a $10 budget for example.

    Now, I need a query that can return AdID and AdTitle of all the adds that are within budget of the day and the date range.

    I've played around with stuff like:

    SELECT AdTitle FROM Ads
    WHERE AdStartDate <= getdate()
    AND AdEndDate >= getdate()
    AND AdBudget > (SELECT SUM(AdClickCost) AS AdBudgetDay FROM AdClicks WHERE AdID = ***something*** AND AdClickDateTime = '3/5/2003')

    What I've done above wont work, but I'm stuck. Tried all sorts of things, and I can't find examples anywhere that does this specifically.

    Any suggestions most appreciated!


    Jack

  2. #2
    Join Date
    May 2002
    Posts
    299
    SELECT AdTitle FROM Ads
    WHERE AdStartDate <= getdate()
    AND AdEndDate >= getdate()
    AND AdBudget > (SELECT SUM(AdClickCost) AS AdBudgetDay FROM AdClicks WHERE AdID = Ads.AdID AND AdClickDateTime = '3/5/2003')
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Mar 2003
    Posts
    3
    Thanks, but doesn't seem to work. .. Get 0 records back.

  4. #4
    Join Date
    Mar 2003
    Posts
    3
    I'm sorry, I take that back.. It almost works. If there are clicks for an ad on the selected date, it returns records correctly, but ads that don't have clicks in the clicks table don't show.

  5. #5
    Join Date
    May 2002
    Posts
    299
    You want something like this then...

    SELECT AdTitle
    FROM Ads left outer join AdClicks on Ads.AdID=AdClicks.AdID
    WHERE Ads.AdStartDate <= getdate()
    AND Ads.AdEndDate >= getdate()
    and AdClickDateTime = '3/5/2003'
    group by Ads.AdID
    having sum(Ads.adBudget)>SUM(AdClicks.AdClickCost)
    --
    -oj
    http://www.rac4sql.net

  6. #6
    Join Date
    Sep 2002
    Location
    MI
    Posts
    19

    Here is alternitive

    you can used the server log to determine who clicks on it and when. This will reduse overhead for your application.

    What internet server are you using? IIS Apache. Remeber IIS logs are space delimited with with no text qualifier. It is also impossible to get a real primary key. If you want to inport every 15 minutes, blow out previous days data.

    If you want help,

    Contact me at jon@oratu.com
    Jon Nelson

Posting Permissions

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