CODEWITHSUNDEEP
sqlsql-server-2008
AT-2017
AT-2017

Reputation: 3149

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.

Edited: I've updated the post as I've converted the query to pivot with following:

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

That returns the following output:

SP_Pivot

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.

Upvotes: 1

Views: 1882

Answers (2)

sagi
sagi

Reputation: 40481

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

Upvotes: 1

jarlh
jarlh

Reputation: 44766

I'm not sure if this is the solution, but at least it is an improvement...

When LEFT JOIN, put the right side table's conditions in the ON clause to get true LEFT JOIN behavior. (When in WHERE, you'll get regular INNER JOIN result.) I.e. move k.StoreID = 1 to the ON clause:

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 AND k.StoreID = 1
LEFT JOIN Item l ON l.ItemID = k.ItemID
WHERE m.calendar_year = 2014
GROUP BY l.ItemName, m.PKDate

Upvotes: 1

Related Questions