Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2007
    Posts
    4

    Unanswered: T-SQL query question

    Hi,

    I'm building an ASP.NET application that will be used for link/click tracking. The purpose is to track the number of times that each link is clicked and from which traffic source. So each traffic source (e.g., web page or pay-per-click network such as AdWords) will be associated with a unique tracking link.

    In the web app, I've got one report that shows the top 100 links with the most clicks, and since I don't want to display them all on one page, I've added paging functionality that is facilitated by the stored procedure below:

    Code:
    ALTER PROCEDURE [dbo].[procGetTop100Links] 
    (
    @PageIndex INT,
    @NumRows INT -- the number of rows to display per page
    )
    AS
    BEGIN
    DECLARE @startRowIndex INT;
      SET @startRowIndex = (@PageIndex * @NumRows) + 1;
      
      WITH LinkEntries AS
        (SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY Clicks DESC) AS Row, LinkID, OfferName, TrafficSource, Clicks
        FROM Links a LEFT OUTER JOIN Offers b ON a.OfferID = b.OfferID)
        
      SELECT LinkID, OfferName, TrafficSource, Clicks
      FROM LinkEntries
      WHERE Row BETWEEN @startRowIndex AND @startRowIndex + @NumRows - 1  
    END
    This procedure works for my current setup, where I have a "Clicks" column in the Links table, and each link's click count is increased by 1 every time the link is clicked.

    However, what I'd really like to be able to do is track the clicks over time, so I created a table called Clicks with the following columns:

    1) ClickID INT
    2) LinkID INT
    3) ClickDate DATETIME

    ClickID is the primary key, and LinkID is the foreign key. Running a query on this table alone to get the number of clicks per link per day will be easy, but I'm not sure how to incorporate the total number of clicks for the top 100 links (i.e., the top 100 links with the most clicks, regardless of the date) into the stored procedure above so that I can display the data in paginated form and ranked by the number of clicks.

    Any guidance would be greatly appreciated. Thanks in advance.

  2. #2
    Join Date
    Nov 2007
    Posts
    4
    Here's the solution for those who are interested:

    Code:
    WITH LinkEntries AS
        (SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY tempT.ClickCount DESC) AS Row, tempT.* FROM 
    
       ( SELECT LinkID, OfferName, TrafficSource, (SELECT COUNT(*) FROM dbo.Clicks C WHERE C.LinkID = a.LinkID) As ClickCount 
        FROM Links a LEFT OUTER JOIN Offers b ON a.OfferID = b.OfferID ) AS tempT )
        
      SELECT LinkID, OfferName, TrafficSource, ClickCount 
      FROM LinkEntries
      WHERE Row BETWEEN @startRowIndex AND @startRowIndex + @NumRows - 1
    Reference: http://forums.asp.net/p/1366949/2843589.aspx

Posting Permissions

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