why won't this query work properly?

Question

my MasterSales table looks like this

SalesDate | Category | Total
----------------------------- 
1/1/2000    01          100 
1/1/2000    02          110
1/2/2000    01          80
1/2/2000    03          20

and my Category table looks like this

ID | Name
----------
01 | A
02 | B
03 | C
04 | D

my query looks like this:

SELECT m.SalesDate, c.Name, SUM(ISNULL(m.Total,0)) AS TotalSales
FROM MasterSales m
LEFT JOIN Category c ON c.ID = m.Category
WHERE m.SalesDate BETWEEN '1/1/2000' AND '1/2/2000'

the result I want is like this:

SalesDate | Name | TotalSales
------------------------------
1/1/2000    A      100
1/1/2000    B      110
1/1/2000    C      0
1/1/2000    D      0
1/2/2000    A      80
1/2/2000    B      0
1/2/2000    C      20
1/2/2000    D      0

but the result I get looks this:

SalesDate | Name | TotalSales
------------------------------
1/1/2000    A      100
1/1/2000    B      110
1/2/2000    A      80
1/2/2000    C      20

I already tried using RIGHT JOIN instead of LEFT JOIN and switching the table on FROM clause but the result is still the same. can anyone help explain to me why it won't work properly?

P.S. : I'm using SQL Server 2005 (if it matters)

What is the logic here ? How do you get the sale date for Category D as 01012000 and 01022000 ? — Satheesh Variath, Feb 27 '14 at 06:18
You are joining with the condition `c.ID = m.Category` so obviously `Name=D` wont get selected because `id=4` isn't available in the other table — G one, Feb 27 '14 at 06:21
@TinTran : I tried that too but the result is the same.. @SatheeshVariath Variath : I'm using `Between` in the query @Gone : I thought by using `left join Category` all the data from `Category` will appear even though there's no match in the other table? btw how do I add a breakline in the comment? — dapidmini, Feb 27 '14 at 07:07
thank you so much to everyone who tried to help. I picked Darka's answer because it is the easiest one to try on management studio and it works perfectly (not that everyone else's is wrong, I just didn't try them all) :D — dapidmini, Feb 27 '14 at 08:29

score 2 · Answer 1 · edited May 23 '17 at 12:03

Try this. This covers all dates where you have MasterSales. In other words, this fills in the missing categories for the day. However, you're looking to fill in missing dates as well, you'll need to create a date control table.. look into recursive cte for the date table.

;with control_table as (
  select distinct SalesDate, ID
  from MasterSales ms
  cross join Category c
  where ms.SalesDate between '1/1/2000' AND '1/2/2000'
  )


select ct.SalesDate, c.Name as Category, coalesce(sum(ms.Total),0) as Total
from control_table ct
inner join Category c
  on ct.ID = c.ID
left join MasterSales ms
  on ct.SalesDate = ms.SalesDate
  and ct.ID = ms.Category
group by ct.SalesDate, c.Name
order by ct.SalesDate asc, c.Name asc

FIDDLE

This is almost better than mine - you forgot `SUM` – Alexander Feb 27 '14 at 07:23 — Alexander, Feb 27 '14 at 07:23

score 2 · Accepted Answer · edited Feb 27 '14 at 07:35

2

and here is my answer

WITH MasterSales (SalesDate, Category, Total) AS (
  SELECT       '1/1/2000','01',100
  UNION SELECT '1/1/2000','02',110
  UNION SELECT '1/2/2000','01',80
  UNION SELECT '1/2/2000','03',20
), Category (ID, Name) AS (
  SELECT       '01','A'
  UNION SELECT '02','B'
  UNION SELECT '03','C'
  UNION SELECT '04','D'
), getDates AS (
  SELECT DISTINCT SalesDate 
  FROM MasterSales 
  WHERE SalesDate BETWEEN '1/1/2000' AND '1/2/2000'
)

SELECT gD.SalesDate, C.Name, SUM(ISNULL(MS.Total,0)) AS TotalSales
FROM getDates AS gD
CROSS JOIN Category AS C
LEFT JOIN MasterSales AS MS 
    ON MS.Category = C.ID
    AND MS.SalesDate = gD.SalesDate 
GROUP BY gD.SalesDate, C.Name

edited Feb 27 '14 at 07:35

sam yi

4,806
1
29
40

answered Feb 27 '14 at 07:05

Darka

2,762
1
14
31

I don't think this is somewhat good with all those static values – Alexander Feb 27 '14 at 07:31
where u see static ?? its just example with cte nothing more ;) – Darka Feb 27 '14 at 07:34

Raging Bull · Answer 3 · 2014-02-27T07:43:48.940

Finally, your problem is solved. Use this query:

SELECT AAA.SalesDate,AAA.Name,ISNULL(BBB.TotalSales,0) as TotalSales  
FROM (
    (SELECT m.SalesDate, c.Name
    FROM MasterSales m,Category c
    WHERE m.SalesDate BETWEEN '1/1/2000' AND '1/2/2000'
    GROUP BY m.SalesDate,c.Name) AAA

    LEFT JOIN 

    (SELECT m.SalesDate,c.Name, SUM(ISNULL(m.Total,0)) AS TotalSales
    FROM MasterSales m,Category c
    WHERE m.SalesDate BETWEEN '1/1/2000' AND '1/2/2000'
    AND c.ID=m.Category 
    GROUP BY m.SalesDate,c.Name) BBB 
    on AAA.SalesDate=BBB.SalesDate AND AAA.Name=BBB.Name)

Explanation:

The first part of the query (AAA) selects all combination of SalesDate and Category.

The second part of the query (BBB) selects SalesDate,Category and TotalSales.

And then we join both of them (AAA & BBB) on SalesDate and Category.

Works perfectly !!!. See SQL Fiddle.

I suspect this will lose in perfomance to my or sam yi solution — Alexander, Feb 27 '14 at 07:34

score 0 · Answer 4 · edited May 23 '17 at 11:57

UPDATED: changed the method of creating temp table using sam yi logic

I suggest you create an addition table.

;WITH control_table AS (
      SELECT DISTINCT SalesDate, Name
        FROM MasterSales ms
             CROSS JOIN Category c
       )

SELECT d.SalesDate,
       c.name,
       COALESCE(sum(m.total),0) totalsales
  FROM control_table d
       LEFT OUTER JOIN Category c
            ON d.Name = c.name
       LEFT OUTER JOIN MasterSales m
            ON (c.id = m.category
                AND d.SalesDate = m.salesdate)
WHERE d.SalesDate BETWEEN '1/1/2000' 
                      AND '1/2/2000'
GROUP BY 
       d.SalesDate,
       c.name
ORDER BY 
       d.SalesDate,
       c.name;

Here is SQLFiddle

Create another table and insert all data into it? I don't think that is a good idea. — Raging Bull, Feb 27 '14 at 07:21

Romeo · Answer 5 · 2014-02-27T21:14:00.053

-1

SELECT cj.Date, cj.Name, IsNull(m.Total,0) Total
FROM (SELECT d.Date, c.Name, c.Id
    FROM (SELECT DISTINCT SalesDate Date 
          FROM MasterSales) d, Category c) cj
LEFT JOIN MasterSales m ON m.SalesDate = cj.Date 
  AND cj.Id = m.Category

http://www.sqlfiddle.com/#!3/d1344/3

Add your date-between conditions

And I'm sure there ate better ways to do this.

Cheers

edited Feb 27 '14 at 21:14

answered Feb 27 '14 at 06:23

Romeo

1,093
11
17

why negative? did u even try? :( – Romeo Feb 27 '14 at 07:08

why won't this query work properly?

5 Answers5