I have four tables in my SQL database i.e MenuItems, Categories, Invoices and InvoiceDetails. Now what I want is to show the menu performance report for a certain date i.e total Qty and total Amount for each menu item for a specific date. It shows the desired result without the date in the where clause but excludes menu items with null values.

Here is my stored procedure:

CREATE PROCEDURE spGetMenuPerformanceByDay 
    @Date date, 
    @Terminal int
AS
BEGIN
    SELECT
        M.Name, 
        ISNULL(SUM(D.Amount), 0) AS Amount,
        ISNULL(SUM(D.Qty), 0) AS Qty
    FROM
        MenuItems AS M 
    JOIN
        Categories AS C ON C.Id = M.CategoryId  
    LEFT JOIN 
        InvoiceDetails AS D ON M.Id = D.ItemId 
    LEFT JOIN 
        Invoices I ON I.Id = d.InvoiceId
    WHERE
        @Terminal IN (I.TerminalId, C.TerminalId) 
        AND CONVERT(date, I.Time) = @Date 
        OR NULL IN (Amount, Qty) 
    GROUP BY
        M.Name, M.Id, D.ItemId
    ORDER BY
        (Qty) DESC
END

The result this stored procedure returns on adding Date in where clause:

And the result I want is but don't get it on adding Date in where clause :

CodePudding user response：

What if you don't put criteria for Invoices in the WHERE clause?

Sample data

create table Categories (
 Id int primary key, 
 Name varchar(30) not null,
 TerminalId int not null
);

create table MenuItems (
 Id int identity(21,1) primary key, 
 Name varchar(30) not null,
 CategoryId int not null, 
 foreign key (CategoryId) references Categories(Id)
);

create table Invoices (
 Id int identity(31,1) primary key,
 TerminalId int not null, 
 ItemId int not null, 
 Time datetime, 
 foreign key (ItemId) references MenuItems(Id)
);

create table InvoiceDetails (
 InvoiceDetailId int identity(41,1) primary key, 
 InvoiceId int,
 Amount decimal(10,2), 
 Qty int, 
 foreign key (InvoiceId) references Invoices(Id)
);

insert into Categories (Id, Name, TerminalId) values
(1,'KOFTA', 1), 
(2,'SOUP', 1), 
(3,'CHICKEN', 1), 
(4,'BURGER', 1);

insert into MenuItems (CategoryId, Name) values
(1,'KOFTA ANDA'), 
(2,'HOT N SOUR SOUP'), 
(3,'CHICKEN CHOWMEIN'), 
(3,'CHICKEN KORMA'), 
(3,'CRISPY CHICKEN'), 
(4,'MEXICAN BURGER');

insert into Invoices (ItemId, TerminalId, Time)
select itm.Id, cat.TerminalId, GetDate() as Time
from MenuItems itm
join Categories cat on cat.Id = itm.CategoryId
where itm.Name in (       
'KOFTA ANDA', 
'HOT N SOUR SOUP', 
'CHICKEN CHOWMEIN', 
'CHICKEN KORMA'
);

insert into InvoiceDetails (InvoiceId, Amount, Qty) values
(31, 1950, 3),
(32, 550, 1),
(33, 250, 1),
(34, 850, 1);

Query

DECLARE @TerminalId INT = 1;
DECLARE @Date DATE = GetDate();

SELECT
 V.[Date], 
 C.Name AS Category, 
 M.Name AS MenuItemName, 
 ISNULL(SUM(D.Amount), 0) AS Amount,
 ISNULL(SUM(D.Qty), 0) AS Qty
FROM Categories AS C 
CROSS JOIN (SELECT @Date AS [Date], @TerminalId AS TerminalId) V
JOIN MenuItems AS M 
ON M.CategoryId = C.Id
LEFT JOIN Invoices I
  ON I.ItemId = M.Id
 AND I.TerminalId = V.TerminalId
 AND CAST(I.Time AS DATE) = V.[Date]
LEFT JOIN InvoiceDetails AS D 
  ON D.InvoiceId = I.Id
WHERE C.TerminalId = V.TerminalId
GROUP BY V.[Date], C.Id, M.Id, C.Name, M.Name
ORDER BY SUM(D.Qty) DESC

Demo on db<>fiddle here

CodePudding user response：

Here's my crack at your goal. Notice the changes. I found the reference to TerminalId in Category table highly suspicious - so much that I suspect it is a model flaw. Along those lines I note that TerminalId should likely have a foreign key to a missing table for Terminals. So I ignore that.

With that out, references to Category are now irrelevant. So that was removed as well. I also changed the procedure name since I find the reference to "day" misleading. It is highly likely "menu performance" would be evaluated on a "day" basis since retail (especially food service) sales vary by day of week consistently. So let's not mislead anyone thinking that is what this procedure does.

For simplicity and clarity, I removed the ISNULL usage. Add it back if desired but such things are usually better handled by the consumer of the resultset. I left the ORDER BY clause as a stub for you to re-evaluate (and you need to).

So how does this work? Simply calculate the sums directly in the CTE and then outer join from the menu items to the CTE sums to get all menu items along with the relevant performance information for the date specified.

CREATE PROCEDURE dbo.GetMenuPerformanceByDate
    @Date date, 
    @Terminal int
AS
BEGIN 
    with sales as (
       select det.ItemId, SUM(det.Amount) as amt, SUM(det.Qty) as qty 
         from dbo.Invoices as inv
        inner join dbo.InvoiceDetails as det 
           on inv.Id = det.InvoiceId
       where cast(inv.Time as date) = @Date 
         and inv.TerminalId = @Terminal 
       group by det.ItemId 
    )
    select menu.name, sales.amt, sales.qty 
      from dbo.MenuItems as menu
      left join sales 
        on menu.Id = sles.ItemId 
    order by ...
    ;
END;

One last note. This filter:

cast(inv.Time as date) = @Date 

is generally not a good method of filtering a datetime column. Far better to use inclusive lower and exclusive upper boundaries like:

inv.Time >= @date and inv.Time < dateadd(day, 1, @date)

for this reason.

My last note - there is a potential flaw regarding MenuItems. Presumably "name" is unique. It is highly unlikely that multiple rows would have the same name, but "unlikely" is not a restriction. If you generate rows based on name and name turns out to NOT be unique, your results are difficult to interpret.