AT-2016 AT-2016 - 8 days ago 6
SQL Question

Using Left Join To Get All Dates

I am trying to work on Inventory Management System where I've to track down products on their issued date. So my condition is - If the store ID is 1 and year is 2014, then it should return all the products against the issued date of that particular year as follows:

Product - Date - TotalIssued
Keyboard - 2014-10-10 - 1 -- This product has been issued or given 1 time on that date of the year 2014
Mouse - 2014-10-11 - 1
Keyboard - 2014-10-12 - 0
Mouse - 2014-10-12 - 0
-------------------------
-------------------------
Keyboard - 2014-12-31 - 0
Mouse - 2014-12-31 - 0 -- This will continue till the end of the year


I've tried to use the calendar table using the following link and created one:

Calendar Table

I've used the following query to get the desired output using calendar table but it returns only the products that had been issued that means only the dates that are stored in the ReturnToStore table:

SELECT l.ItemName AS Product, m.PKDate AS Date, COUNT(m.PKDate) AS TotalIssued
FROM [days] m
LEFT JOIN ReturnToStore k ON CONVERT(DATE, k.IssuedDate) = m.PKDate
LEFT JOIN Item l ON l.ItemID = k.ItemID
WHERE m.calendar_year = 2014 AND k.StoreID = 1
GROUP BY k.StoreID, l.ItemName, m.PKDate


This is the output I am getting now:

Product - Date - TotalIssued
Keyboard - 2014-10-10 - 1
Mouse - 2014-10-11 - 1


Here are the table structures:

CREATE TABLE [dbo].[Item](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ItemName] [varchar](50) NULL,
[Description] [varchar](150) NULL,
[Tag] [varchar](50) NULL,
CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
(
[ItemID] ASC
)
) ON [PRIMARY]

INSERT INTO [dbo].[Item]
([ItemName]
,[Description]
,[Tag])
VALUES
('Keyboard', 'IT Equipment', ''),
('Mouse', 'IT Equipment', '')

CREATE TABLE [dbo].[ReturnToStore](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StoreID] [int] NULL,
[CategoryID] [int] NULL,
[ItemID] [int] NULL,
[Quantity] [float] NULL,
[IssuedDate] [datetime] NULL,
[Description] [varchar](150) NULL,
[OrderID] [varchar](50) NULL,
CONSTRAINT [PK_ReturnToStore] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]

INSERT INTO [dbo].[ReturnToStore]
([StoreID]
,[CategoryID]
,[ItemID]
,[Quantity]
,[IssuedDate]
,[Description]
,[OrderID])
VALUES
(1, 1, 1, 10, '2014-10-10 00:00:00.000', '', 'PO-02'),
(1, 1, 2, 20, '2014-10-11 00:00:00.000', '', 'PO-03')


Note: I guess, this could be done easily with LEFT JOIN using calendar table. But got stuck here.

Answer

Your first error is the fact the you have placed a condition on the right table of a LEFT JOIN in the WHERE clause instead of the ON clause.

The second thing, you should also use a derived table for item names, just like the dates :

SELECT p.ItemName AS Product, m.PKDate AS Date, COUNT(l.ItemID) AS TotalIssued
FROM [days] m
CROSS JOIN (SELECT DISTINCT itemName,itemID FROM Item) p
LEFT JOIN ReturnToStore k
 ON CONVERT(DATE, k.IssuedDate) = m.PKDate AND
    p.itemid = k.itemid AND
    k.StoreID = 1
LEFT JOIN Item l 
 ON l.ItemID = k.ItemID
WHERE m.calendar_year = 2014 
GROUP BY p.ItemName, m.PKDate