SQLTeam.com | Weblogs | Forums

Problem with unique ids and using aggregates to create derived columns

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

Is this the right condition?

where cu.customerid = o.orderid


Maybe :

where cu.customerid = o.CustomerID

Thanks, stepson, but yes the crux of the problem is building the derived columns in general amidst all these table joins

I suggest to populate these tables with some records and present the desired output.

This way is more easy for us to understand your request.