SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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');
10 rows affected

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'
);
4 rows affected

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

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


Warning: Null value is eliminated by an aggregate or other SET operation.

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