JOIN ON COLUMNS CREATING TOO MANY INSTANCES (DUPLICATES)
I have an orders table that i want to display with some aggregate derived columns , and i want to count the instances a value shows up per customerid. One derived column [DOCKED] will have a join into another table via the customerid
TABLE dbo.orders --o
(
OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
Sourceid int NOT NULL,
Status varchar(50) null,
ordershipped datetime NULL,
orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);
TABLE dbo.Ordermessages --om
(
OmID (pk,varchar(5), NOT NULL),
Sourceid int NOT NULL,
dockcode varchar(5),
dockdate datetime NULL,
location nvarchar(50) not null
CONSTRAINT PK_customerID PRIMARY KEY CLUSTERED (OmID ASC)
);
SELECT coalesce (Customerid,'Total') AS CUSTID
[READY] = sum (case when o.status = 'Confirmed' then 1 else 0 end)
,[DOCKED] = sum (case when o.status = 'Confirmed' and om.dockcode ='z' AND om.dockdate >= GETDATE() -1 then 1 else 0 end)
FROM Orders o
join Ordermessages om on o.sourceid = om.sourceid
where o.Customerid <> ''
GROUP BY ROLLUP (Customerid);
Thanksfor all help
S