Need SQL help

I'm embarrassed to admit it, but I need a little SQL help. The truth is, I suck when it comes to SQL. I guess I'm not disciplined enough to really become a guru.

I'm serving ads, and I have two queries. One counts impressions, the other clicks. They look like this:

SELECT COUNT(*) AS ImpressionCount, CONVERT(nvarchar, Impressions.ServeTime, 101) AS ServeTime
FROM Impressions
WHERE Impressions.CreativeID = 15
GROUP BY CONVERT(nvarchar, Impressions.ServeTime, 101)
ORDER BY CONVERT(nvarchar, Impressions.ServeTime, 101)

SELECT COUNT(*) AS ClickCount, CONVERT(nvarchar, Clicks.ClickTime, 101) AS ServeTime
FROM Clicks
WHERE Clicks.CreativeID = 15
GROUP BY CONVERT(nvarchar, Clicks.ClickTime, 101)
ORDER BY CONVERT(nvarchar, Clicks.ClickTime, 101)

What I'd like to do is combine them into one set of results, three columns: ImpressionCount, ClickCount and ServeTime (one for each date). The idea is to roll that into a new table for faster querying, since the data will never change. How do I do that? If my methodology “ain't right,” feel free to poke fun or help.

7 Comments

  • Not tested but should be pretty close...



    select t1.ImpressionCount, t2.ClickCount, t1.ServeTime

    from

    (SELECT COUNT(*) AS ImpressionCount, CONVERT(nvarchar, Impressions.ServeTime, 101) AS ServeTime

    FROM Impressions

    WHERE Impressions.CreativeID = 15

    GROUP BY CONVERT(nvarchar, Impressions.ServeTime, 101)) t1

    inner join

    (SELECT COUNT(*) AS ClickCount, CONVERT(nvarchar, Clicks.ClickTime, 101) AS ServeTime

    FROM Clicks

    WHERE Clicks.CreativeID = 15

    GROUP BY CONVERT(nvarchar, Clicks.ClickTime, 101)) t2

    on t1.ServeTime = t2.ServeTime

    order by t1.ServeTime



    ... t1 and t2 are derived tables. Hope this helps. If this doesnt work post from sample insert statements and I'll fix it.



    Justin





  • I'm not sure if it's the most optimised query possible but something like this should work:



    SELECT DISTINCT [ClickTime],

    (SELECT COUNT(*) FROM [Clicks] WHERE CONVERT(nvarchar, [ClickTime], 101) = [r].[ClickTime]) [ClickCount],

    (SELECT COUNT(*) FROM [Impressions] WHERE CONVERT(nvarchar, [ServeTime], 101) = [r].[ClickTime]) [ServeCount]

    FROM (

    SELECT [CreativeID], CONVERT(nvarchar, [ClickTime], 101) [ClickTime]

    FROM [Clicks]

    UNION

    SELECT [CreativeID], CONVERT(nvarchar, [ServeTime], 101)

    FROM [Impressions]

    ) [r]

    WHERE [r].[CreativeID] = 15

    ORDER BY [r].[ClickTime]



    Lee

  • Jeff - "I'm not disciplined enough to really become a guru" .... c'mon. Nobody can be an expert at everything. you need to read this AGAIN today: http://www.secretgeek.net/inadequate.asp :-)

  • I'd go with the UNION query as well.

  • The UNION query took 2:30 minutes... that's not an option. The first one took one second. Guess which one I'll use? :)



    There is one problem, and I'm not sure if I can address it in SQL. In my test data (a three month ad campaign), I have one day where there are no clicks, and as a result, no row is returned for it even though there are impressions that day. I'm fuzzy on joins... can the query be changed to include that day?

  • How about a combination of the two to get results when there is a click value and no impression value. This should be a bit quicker than the UNION but uses a LEFT JOIN to 'preserve' NULL values for a particular day...



    SELECT [r].[ClickTime], [t1].[ImpressionCount], [t2].[ClickCount]

    FROM (

    SELECT [CreativeID], CONVERT(nvarchar, [ClickTime], 101) [ClickTime]

    FROM [Clicks]

    WHERE [CreativeID] = 15

    UNION

    SELECT [CreativeID], CONVERT(nvarchar, [ServeTime], 101)

    FROM [Impressions]

    WHERE [CreativeID] = 15

    ) [r]

    LEFT JOIN (

    SELECT COUNT(*) AS [ImpressionCount], CONVERT(nvarchar, [ServeTime], 101) AS [ServeTime]

    FROM [Impressions]

    WHERE [CreativeID] = 15

    GROUP BY CONVERT(nvarchar, [ServeTime], 101)) [t1] ON [r].[ClickTime] = [t1].[ServeTime]

    LEFT JOIN (

    SELECT COUNT(*) AS [ClickCount], CONVERT(nvarchar, [ClickTime], 101) AS [ClickTime]

    FROM [Clicks]

    WHERE [CreativeID] = 15

    GROUP BY CONVERT(nvarchar, [ClickTime], 101)) [t2] ON [r].[ClickTime] = [t2].[ClickTime]

    ORDER BY [r].[ClickTime]



    Lee

  • As it turns out, I used a FULL JOIN and an ISNULL function to return 0 instead of null. Thanks for your help, everyone!

Comments have been disabled for this content.