hi hope this helps
solved = NO
create tables insert data script
drop table dbo.orders
drop table dbo.shipments
create table dbo.orders
(
OrderID int NOT NULL,
CustomerID varchar(5) NOT NULL,
sourceid uniqueidentifier NOT NULL,
Status varchar(50) null,
ordershipped datetime NULL,
orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);
create table dbo.shipments
(
evID varchar(5) NOT NULL,
shipdate datetime NULL,
CustomerID varchar(5) NOT NULL,
location nvarchar(50) not null
CONSTRAINT PK_customerID PRIMARY KEY CLUSTERED (evID ASC)
);
INSERT INTO dbo.orders (OrderID, CustomerID, sourceid, Status, ordershipped, orderarrived)
VALUES
(1001, 'C001', NEWID(), 'Processing', NULL, NULL),
(1002, 'C002', NEWID(), 'Shipped', '2026-05-10', NULL),
(1003, 'C003', NEWID(), 'Confirmed', '2026-05-05', '2026-05-12'),
(1004, 'C001', NEWID(), 'Cancelled', NULL, NULL),
(1005, 'C004', NEWID(), 'Confirmed', '2026-04-28', '2026-05-03');
INSERT INTO dbo.shipments (evID, shipdate, CustomerID, location)
VALUES
('S001', '2026-05-16', 'C002', 'Hyderabad'),
('S002', '2026-05-05', 'C003', 'Bangalore'),
('S003', '2026-04-28', 'C004', 'Chennai'),
('S004', '2026-05-11', 'C001', 'Delhi'),
('S005', '2026-05-16', 'C003', 'Mumbai');
Data
T-sql
DECLARE @now DATE;
SET @now = CAST(GETDATE() AS DATE);
SELECT
COALESCE(o.customerid, 'Total') AS CUSTID,
SUM(CASE WHEN o.status = 'Confirmed' THEN 1 ELSE 0 END) AS READY,
SUM(CASE WHEN sh.shipdate = @now THEN 1 ELSE 0 END) AS [SHIPPING TODAY]
FROM Orders o
LEFT JOIN Shipments sh
ON o.customerid = sh.customerid
GROUP BY ROLLUP(o.customerid);
Result
