SQL Server Stored Procedure for Menu Performance Report

Question

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:

Item	Amount	Qty
KOFTA ANDA	1950	3
HOT N SOUR SOUP	550	1
CHICKEN CHOWMEIN	250	1
CHICKEN KORMA	850	1

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

Item	Amount	Qty
KOFTA ANDA	1950	3
HOT N SOUR SOUP	550	1
CHICKEN CHOWMEIN	250	1
CHICKEN KORMA	850	1
CRISPY CHICKEN	0	0
MEXICAN BURGER	0	0

`Null in (Amount,Qty)` is never true. The only way to test column is null is `is null` — Serg, Dec 18 '21 at 07:14
I dont want to test but add rows with null values. The problem is on adding date in where clause, excludes rows with null values. — Dawood Jamil, Dec 18 '21 at 07:32
If I Remove ` Convert(date,I.Time) = @Date ` From where clause then it gives the desired result but I want the result to be date oreinted. — Dawood Jamil, Dec 18 '21 at 07:35
Procedural code is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Dec 18 '21 at 09:37
Looks like you are missing brackets for the `OR`, also `NULL IN ...` never works because `NULL` is not equal to anything including itself. To top it off, `CONVERT(date` can cause performance issues. Try `AND ((I.Time >= CAST(CAST(@Date AS date) AS datetime) AND I.Time < CAST(DATEADD(day, 1, CAST(@Date AS date) AS datetime))) OR Amount IS NULL OR Qty IS NULL)` — Charlieface, Dec 18 '21 at 17:57

LukStorms · Answer 1 · 2021-12-18T18:14:32.930

1

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

Date	Category	MenuItemName	Amount	Qty
2021-12-18	KOFTA	KOFTA ANDA	1950.00	3
2021-12-18	SOUP	HOT N SOUR SOUP	550.00	1
2021-12-18	CHICKEN	CHICKEN CHOWMEIN	250.00	1
2021-12-18	CHICKEN	CHICKEN KORMA	850.00	1
2021-12-18	CHICKEN	CRISPY CHICKEN	0.00	0
2021-12-18	BURGER	MEXICAN BURGER	0.00	0

Demo on db<>fiddle here

edited Dec 18 '21 at 18:14

answered Dec 18 '21 at 08:01

LukStorms

28,916
5
31
45

The Problem is the date which is only contained in the invoices table. I need Menu Items report for a specific date – Dawood Jamil Dec 18 '21 at 08:02
But you don't want to exclude the unmatched? So isn't it fine then? – LukStorms Dec 18 '21 at 08:07
But then it shows the overall report, not for a specific date. what if I need menu report performance for a month, week or a day – Dawood Jamil Dec 18 '21 at 08:10
Maybe like this then. – LukStorms Dec 18 '21 at 08:22
Same Problem exists.. – Dawood Jamil Dec 18 '21 at 14:52
Could you include fake sample data? As text, or insert statement, or a fiddle? It's a bit of an XY problem atm. – LukStorms Dec 18 '21 at 15:01
The problem is on adding date it excludes menu items from result that are not there in the InvoiceDetails table for that date. But I want them in result even if their amount and qty is null. – Dawood Jamil Dec 18 '21 at 15:38
Let's say Total Number of Menu Items is 40 but only 30 of them were sold on 18/12/2021. Now what i want in the result is sum of amount and qty of those 30 items and remaining 10 items showing 0 as amount and Qty. – Dawood Jamil Dec 18 '21 at 15:42
I want Both Sold and unsold items in the result set. At the moment, on adding date, it only provides items which were sold on that day and excludes items with Null as their amount and Qty – Dawood Jamil Dec 18 '21 at 15:59
@DawoodJamil Check out the demo – LukStorms Dec 18 '21 at 18:15
Thanks to You bro.. @SMor Solution is what solved my problem. – Dawood Jamil Dec 18 '21 at 19:19
1

@DawoodJamil If you need more dates or a range then you could join to a Calendar table. [Here's an example of how to create one](https://stackoverflow.com/a/70218278/4003419). – LukStorms Dec 18 '21 at 20:32

score 1 · Answer 2 · answered Dec 18 '21 at 18:56

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.

Thanks a ton mate. This was exactly what I was looking for.. Secondly just to add, terminal refers to a branch. Categories table has terminalId as FK in case different branches have different categories of food. — Dawood Jamil, Dec 18 '21 at 19:17
I added Category because I wanted report on the basis of date and terminal. — Dawood Jamil, Dec 18 '21 at 19:42
On adding Category Back into your solution, it works fine. Because categories are different at different terminals in my case and without adding category it mixes menu items of all terminals. Thanks a lot again. I appreciate your effort. It solved my problem. — Dawood Jamil, Dec 18 '21 at 19:48

SQL Server Stored Procedure for Menu Performance Report

2 Answers2