CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Martin
Martin

Reputation: 40583

Join two tables in such way that I can get all items for all dates

I've two tables: purchase and items. For every date in the Purchase table, I'd like to see the number of items purchased for every single items in the Items table. Below is the result set I would expect from my query. The issue is that if no items were purchased for a given day, there's no record of it in the Purchase table. The dates must be coming from the Purchase table (no continuous dates).

+-------------------------+----------+----------+
|      PurchaseDate       | ItemName | Quantity |
+-------------------------+----------+----------+
| 2000-01-01 00:00:00.000 | A        |        1 |
| 2000-01-01 00:00:00.000 | B        |        2 |
| 2000-01-01 00:00:00.000 | C        |        4 |
| 2000-01-04 00:00:00.000 | A        |        6 |
| 2000-01-04 00:00:00.000 | B        |        0 | <- This row doesn't exist in Purchase
| 2000-01-04 00:00:00.000 | C        |        0 | <- This row doesn't exist in Purchase
| 2000-01-07 00:00:00.000 | A        |        0 | <- This row doesn't exist in Purchase
| 2000-01-07 00:00:00.000 | B        |        0 | <- This row doesn't exist in Purchase
| 2000-01-07 00:00:00.000 | C        |        3 |
+-------------------------+----------+----------+

What kind a query would give me the result above with the data below? I am using SQL Server 2008 R2.

CREATE TABLE Purchase
(
   PurchaseDate DATETIME,
   ItemName NVARCHAR(200),
   Quantity INT
)

CREATE TABLE Items
(
   Value  NVARCHAR(200)
)

INSERT INTO Items VALUES ('A')
INSERT INTO Items VALUES ('B')
INSERT INTO Items VALUES ('C')
INSERT INTO Purchase VALUES ('2000-01-01', 'A', 1)
INSERT INTO Purchase VALUES ('2000-01-01', 'B', 2)
INSERT INTO Purchase VALUES ('2000-01-01', 'C', 4)
INSERT INTO Purchase VALUES ('2000-01-04', 'A', 6)
INSERT INTO Purchase VALUES ('2000-01-07', 'C', 3)

Upvotes: 0

Views: 130

Answers (3)

dotjoe
dotjoe

Reputation: 26940

select
    p.purchasedate,
    i.value,
    sum(case when p.itemname = i.value then p.quantity else 0 end)
from
    Purchase p
    cross join Items i
group by
    p.purchasedate,
    i.value
order by
    p.purchasedate, 
    i.value

Upvotes: 0

Johann
Johann

Reputation: 1663

You can cross join a distinct set of dates from the Purchase table to get your list of dates. This will only return a date if at least one item was purchased on a particular date: 

SELECT
 dt.PurchaseDate, i.Value as ItemName, SUM(ISNULL(Quantity,0)) as Quantity
FROM Items i
CROSS JOIN ( SELECT DISTINCT PurchaseDate FROM Purchase ) dt
LEFT OUTER JOIN Purchase p
  ON i.Value = p.ItemName
  AND dt.PurchaseDate = p.PurchaseDate
GROUP BY dt.PurchaseDate, i.Value
ORDER BY dt.PurchaseDate, i.Value

Upvotes: 2

Kermit
Kermit

Reputation: 34054

Unless you have a table with all calendar dates, or use a cursor approach, you won't be able to populate dates where nothing was purchased.

Upvotes: 1

Related Questions