Using Left Join To Get All Dates
<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>