SQLTeam.com | Weblogs | Forums

Problem with unique ids and using aggregates to create derived columns


#1

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


#2

Is this the right condition?

where cu.customerid = o.orderid


Maybe :

where cu.customerid = o.CustomerID


#3

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


#4

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.