How to select even non existent values

Question

I'm using SQL-Server 2005

I have two tables, Users and Orders. Each user can have many orders. Tables connected via userID. Orders has date column ( which is when order was made ). Users have registrationSite column ( which is who is the affiliate site behind user and all of his orders ).

I want to select sum of orders per day and site so even if site haven't sold or user registred i will have 0 as sum value.

this is current query which misses the highlighted part.

select sum(orderSum)*40/100-0.17,count(*),
registrationSite,
dateadd(dd,datediff(dd,0,cu.date),0)
from Users cu 
inner join Orders cp 
on cu.userID=cp.userID
group by dateadd(dd,datediff(dd,0,cu.date),0),registrationSite
order by dateadd(dd,datediff(dd,0,cu.date),0),registrationSite

Answer 1

If you are using SQL 2005 or later, you can build your Calendar table on the fly using a common-table expression. In your original post, you are doing your date math on cu.date but the alias cu represents the Users table not the Orders table. I'm assuming that this was supposed to have been cp.date? I.e., your description talks about order dates but your SQL uses a date in the Users table.

With OrderDateBoundaries As
    (
    Select Cast(DateDiff(d,0,Min([Date]))) As MinDate
        , Cast(DateDiff(d,0,Max([Date]))) As MaxDate
    From Orders
    )
    , Calendar As
    (
    Select MinDate As [Date]
    From OrderDateBoundaries
    Union All
    Select DateAdd(d, 1, [Date])
    From Calendar
    Where [Date] <= DateAdd(d, 1, (
                                    Select MaxDate
                                    From OrderDateBoundaries
                                    ))
    )
Select Calendar.[Date]
    , Coalesce(Sum(O.ordersum) * 40 / 100 - 0.17,0) As OrderSum
    , Count(*)
    , RegistrationSite
From Calendar
        Left Join (Users As U
            Join Orders As O
                On O.userId = U.UserId)
             On Cast(DateDiff(d, 0, O.OrderDate) As datetime) =Calendar.Date
Group By Calendar.[Date], registrationsite
Option(MaxRecursion 0);

If it is the case that you do want to join on Users.Date, then it is a simple change to do that . In addition, it is not clear from which table ordersum is stored.

With DateBoundaries As
    (
    Select Cast(DateDiff(d,0,Min([Date]))) As MinDate
        , Cast(DateDiff(d,0,Max([Date]))) As MaxDate
    From Users
    )
    , Calendar As
    (
    Select MinDate As [Date]
    From DateBoundaries
    Union All
    Select DateAdd(d, 1, [Date])
    From Calendar
    Where [Date] <= DateAdd(d, 1, (
                                    Select MaxDate
                                    From DateBoundaries
                                    ))
    )
Select Calendar.[Date]
    , Coalesce(Sum(O.ordersum) * 40 / 100 - 0.17,0) As OrderSum
    , Count(*)
    , RegistrationSite
From Calendar
        Left Join (Users As U
            Join Orders As O
                On O.userId = U.UserId)
             On Cast(DateDiff(d,0,U.Date) As datetime) =Calendar.Date
Group By Calendar.[Date], RegistrationSite
Option(MaxRecursion 0);