PROBLEM WITH UNIQUE IDS AND USING AGGREGATES TO CREATE DERIVED COLUMNS

• Zososql

  Ten Centuries

  Points: 1218

  More actions

  February 16, 2017 at 8:46 am

  #327295

  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

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  February 16, 2017 at 12:07 pm

  #1928925

  Without sample data and expected results, it's very difficult to troubleshoot your query.  You can find how to post those in the first link in my signature.

  My first suggestion is to use CASE expressions instead of subqueries.  You're duplicating a lot of information in your subqueries that's already available in your main query, so you're doing a lot of extra work for no additional gain.
  SELECT COALESCE (cu.customerid,'Total') AS CUSTID,
    [PENDING] = COUNT(DISTINCT CASE WHEN cod.confirmed < GETDATE() THEN o.OrderID END)
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);

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA