February 15, 2017 at 9:19 am
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 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, -- received the inventory
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, -- received the inventory
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
February 15, 2017 at 11:51 am
It sounds like there's a one-to-many relationship between customer and codes. What do you get if you add a DISTINCT to your counts, like count(DISTINCT o.OrderID) ?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply