Grouping by day from 2 tables

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, orderDat开发者_StackOverflow社区e),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?

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

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