title: Using Left Join To Get All Dates
body: <p>I am trying to work on <strong>Inventory Management System</strong> 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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I've tried to use the calendar table using the following link and created one:</p>
  
  <p><a href="https://www.experts-exchange.com/articles/12267/SQL-Server-Calendar-Table.html" rel="nofollow noreferrer">Calendar Table</a></p>
  
  <p>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 <strong>ReturnToStore</strong> table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This is the output I am getting now:</p>
  
  <pre><code> Product - Date - TotalIssued
  Keyboard - 2014-10-10 - 1 
  Mouse    - 2014-10-11 - 1
  </code></pre>
  
  <p>Here are the table structures:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p><strong>Edited:</strong> I've updated the post as I've converted the query to pivot with following:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[MonthlyConsumption]
  @StoreID int,
  @Year int
  
  AS
  
  SELECT Distinct ItemName,
      [1] AS M1,
      [2] AS M2,
      [3] AS M3,
      [4] AS M4,
      [5] AS M5,
      [6] AS M6,
      [7] AS M7,
      [8] AS M8,
      [9] AS M9,
     [10] AS M10,
     [11] AS M11,
     [12] AS M12
  FROM
  (SELECT MONTH(m.PKDate) as YEAR, 
  p.ItemName, k.IssuedDate, k.ItemID
  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 = @StoreID
  LEFT JOIN Item l 
   ON l.ItemID = k.ItemID
  WHERE m.calendar_year = @Year
  GROUP BY p.ItemName, m.PKDate, k.ItemID, k.IssuedDate)
  
  source
  PIVOT
  (
      COUNT(ItemID)
      FOR YEAR
      IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
  ) AS PivotDay
  </code></pre>
  
  <p>That returns the following output:</p>
  
  <p><a href="https://i.stack.imgur.com/eaWHk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eaWHk.png" alt="SP_Pivot"></a></p>
  
  <p>I am not sure why it is repeating and if I add the same product more than once on that specific date, then there is no change shown in the pivot and it remains the same as the output given.</p>