Grouping by day from 2 tables

Question

I have 2 tables: "orders" and "visits". in the orders table i'm saving some details about the order including "userIP" and "orderDate". in the visits table i'm saving details everytime a user visiting my web page including "userIP" and "visitDate". i'm using ASP.NET and SQL SERVER 2005. i want to create a statistic table whitin i save the number of users visited and the number of users that ordered BOTH GROUPED BY DAY so far i got:

  select count(userIP) as NumOfOrders,
         dateadd(dd, datediff(dd, 0, orderDate),0) as Date
    from Orders
group by dateadd(dd, datediff(dd, 0, orderDate), 0)

this works fine and give me the number of orders grouped by day, but how do i add the total visits grouped by day to this?

Answer 1

SELECT COALESCE(O1.Date, V1.Date) Date,
       COALESCE(O1.NumOfOrders, 0) NumOfOrders,
       COALESCE(V1.TotalVisits, 0) TotalVisits
FROM 
     (select dateadd(dd,0, datediff(dd, 0, O.orderDate)) Date,
             count(O.userIP) NumOfOrders
        from Orders O
    group by dateadd(dd,0, datediff(dd, 0, O.orderDate))) O1
FULL JOIN
     (select dateadd(dd,0, datediff(dd, 0, V.visitDate)) Date,
             count(V.userIP) TotalVisits
        from Visits V
    group by dateadd(dd,0, datediff(dd, 0, V.visitDate))) V1
on O1.Date = V1.Date

Answer 2

I would do this:

SELECT v.userIP, NumOfVisits, NumOfOrders, v.Date
FROM (
    SELECT userIP, count(*) as NumOfVisits,
         dateadd(dd, datediff(dd, 0, visitDate),0) as Date
    FROM visits
    GROUP BY userIP, dateadd(dd, datediff(dd, 0, orderDate), 0)) v
LEFT JOIN (
    SELECT userIp, count(*) as NumOfOrders,
         dateadd(dd, datediff(dd, 0, orderDate),0) as Date
    FROM orders
    GROUP BY UserIP, dateadd(dd, datediff(dd, 0, orderDate), 0)) o
 ON o.UserIP = v.UserIP
    AND o.Date = v.Date

and your result should be like:

78.34.5.11 | 3 | 1 | 2009.10.06
78.34.5.19 | 9 | 0 | 2009.10.06

if you don't need to group by userIP, you can do this:

SELECT NumOfVisits, NumOfOrders, v.Date
FROM (
    SELECT count(*) as NumOfVisits,
         dateadd(dd, datediff(dd, 0, visitDate),0) as Date
    FROM visits
    GROUP BY dateadd(dd, datediff(dd, 0, visitDate), 0)) v
LEFT JOIN (
    SELECT count(*) as NumOfOrders,
         dateadd(dd, datediff(dd, 0, orderDate),0) as Date
    FROM orders
    GROUP BY dateadd(dd, datediff(dd, 0, orderDate), 0)) o
 ON o.Date = v.Date

and your result will look like:

12 | 1 | 2009.10.06