I am looking to provide a report that has all customerids on one left column and query a series of derived columns that will provide data for Orders not shipped (orders.status = 'waiting for approval') , how many days orders stay on hold before being shipped ,etc. I need to take each unique (distinct) customerid, and total each orderid that will relate to o.ordershipped, o.orederarrived, cod.confirmed, etc. Below is DDL for tables
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)
);
when i try to do a derived column called PENDING with the below:
SELECT coalesce (cu.customerid,'Total') AS CUSTID
[PENDING] = (select count(DISTINCT o.OrderID) from
orders o LEFT OUTER JOIN Code c on o.OrderID = c.OrderID where cu.customerid = o.customerid and c.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);
....i get the same results for each and every CUSTID, which of course is not correct. So i cannot find a way to take each unique Customerid and tie in these orderids to provide results in these derived columns. Partition by? Subquery I'm missing?
Thanks in advance