CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
eugeneK
eugeneK

Reputation: 11116

How to select even non existent values

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

Upvotes: 2

Views: 1180

Answers (2)

Thomas
Thomas

Reputation: 64645

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);

Upvotes: 1

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196002

Use LEFT OUTER JOIN instead of INNER JOIN ..

from Users cu 
inner join Orders cp

should become

from Users cu 
left outer join Orders cp

which means all users and their accompanying orders if they exist.. (but users will be all regardless of orders)

update

you need to create a tally table with the date ranges (the days) and left outer join on that ..

look here: How to get a table of dates between x and y in sql server 2005

Upvotes: 3

Related Questions