im having a problem with JOIN tables in a stored procedure- this script uses distinct Customerid to provide details on order statuses . When I do not include the Codes table in the join mix, I get an accurate amount of [ORDER OUT] instances, but when i include the Codes table, but for [PENDING] derived column, I get thousands of more rows back than expected:
TABLE dbo.orders --o
( OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
sourceid (pk uniqueidentifier NOT NULL),
Status varchar(50) null,
ordershipped datetime NULL,
orderarrived datetime NULL CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC) );
TABLE dbo.customers --cus
( customerID (pk,varchar(5), NOT NULL),
firstname nvarchar(50) null,
lastname nvarchar(50) not null
CONSTRAINT PK_customerID PRIMARY KEY CLUSTERED (customerID ASC) );
TABLE dbo.codes --cod
( codeID (pk uniqueidentifier NOT NULL),
confirmed datetime NULL,
code (varchar(2) NULL),
CONSTRAINT PK_codeID PRIMARY KEY CLUSTERED (codeID ASC) );
TABLE dbo.messages --me
( msgID (pk uniqueidentifier NOT NULL),
sourceid (pk uniqueidentifier NOT NULL),
msgDate datetime NULL,
msgcode (varchar(2) NOT NULL),
msg (varchar(2000) NOT NULL),
CONSTRAINT PK_msgID PRIMARY KEY CLUSTERED (msgID ASC) );
SELECT coalesce (cu.customerid,'Total') AS CUSTID , [ORDER OUT] = count(o.OrderID) - count(o.ordershipped)
, [PENDING] = (select count(o.OrderID) from
orders o where cu.customerid = o.orderid and cod.confirmed < getdate())
FROM CU.customers cu left join Orders o join messages me on o.sourceID = me.sourceID join codes cod on o.OrderID = cod.OrderID
on cu.customerid = o.customerid GROUP BY ROLLUP(cu.customerid);
??
Thanks in advance