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 |
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
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
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.