CODEWITHSUNDEEP
sqlsql-server
Ibanez1408
Ibanez1408

Reputation: 5058

Joining 3 tables on a date criteria

I need help with this.

Tbl: WarehouseInventory

Date       | DelRec | ProductId | Quantity
2015-09-10 | 110    | 1         | 100
2015-09-12 | 111    | 1         | 100
2015-09-12 | 111    | 2         | 200
2015-09-12 | 111    | 3         | 300

Tbl: Withdrawals

Date       | ID     | ProductId | Quantity | CustomerId
2015-09-11 | 1      | 1         | 400      | 2
2015-09-12 | 1      | 1         | 100      | 1
2015-09-12 | 2      | 2         | 200      | 1
2015-09-12 | 3      | 3         | 300      | 1

Tbl: Customers

Customer Id     | Name 
1               | Somebody
2               | Someone

The output should be like this

DelRec | Date Added | ProductId | Stocked | Withdrawn | Customer 
  110  | 2015-09-10 | 1         | 100     | 0         | NULL
  0    | 2015-09-11 | 1         | 0       | 400       | Someone
  111  | 2015-09-12 | 1         | 100     | 100       | Somebody
  111  | 2015-09-12 | 2         | 200     | 200       | Somebody
  111  | 2015-09-12 | 3         | 300     | 300       | Somebody

This is what I have come up so far and it's giving me a wrong output

select wi.DateAdded as 'Date Added', max(wi.DeliveryReceipt) as 'Delivery Receipt', wi.ProductId as 'Product',
     max(isnull(wi.Quantity, 0)) as 'Stocked', max(isnull(w.Quantity, 0)) as 'Withdrawn', e.Customers as 'Customer'
     from WarehouseInventory wi 
     cross join Withdrawals w 
     cross join Customer e 
     group by wi.DateAdded, wi.ProductId, e.Customers, wi.DeliveryReceipt, w.ProductId

Basically, I need to join the two tables on the date and product and if there is a null value in one of the tables, just make it 0. I appreciate your help.

Upvotes: 0

Views: 107

Answers (2)

Jeff Meatball Yang
Jeff Meatball Yang

Reputation: 39037

You have a few inconsistencies between your example table and your query, but here's the basic gist:

  • You want to FULL OUTER JOIN your Warehouse delivery (A) and Withdrawal (B) tables on both product and date
  • Make sure you coalesce(A, B) for both date and product
  • Sum the quantities from each table, then coalesce outside each aggregate to get zeros (since one column can be all nulls).

Here:

select 
  coalesce(wi.DateAdded, w.date) as 'Date Added', 
  max(wi.DeliveryReceipt) as 'Delivery Receipt', 
  coalesce(wi.ProductId, w.productId) as 'Product',
  coalesce(sum(wi.Quantity), 0) as 'Stocked', 
  coalesce(sum(w.Quantity), 0) as 'Withdrawn', 
  e.name as 'Customer'
from WarehouseInventory wi 
  full outer join Withdrawals w on w.date = wi.dateadded and w.productId = wi.productId
  left join Customer e on e.customerId = w.customerId 
group by 
  coalesce(wi.DateAdded, w.date), 
  coalesce(wi.ProductId, w.productId), 
  e.name

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use a FULL OUTER JOIN:

SELECT DelRec, 
       COALESCE(wi.[Date], wd.[Date]) AS Date_Added,
       COALESCE(wi.ProductId, wd.ProductId) AS ProductId,
       COALESCE(wi.Quantity, 0) AS Stocked,
       COALESCE(wd.Quantity, 0) AS Withdrawn,
       c.Name AS Customer
FROM WarehouseInventory AS wi
FULL OUTER JOIN Withdrawals AS wd 
ON wi.[Date] = wd.[Date] AND wi.ProductId = wd.ProductId
LEFT JOIN Customers AS c ON c.[Customer Id] = wd.CustomerId 
ORDER BY Date_Added

Upvotes: 2

Related Questions