Cannot perform an aggregate function problem

1

I need to count how many times a customer has had a confirmed shipment sit on the dock in the past working week (the result would be an integer). Confirmed in the codes table is the date it gets tagged:

TABLE dbo.orders --o ( OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
trackid (varchar(50) NOT NULL),
Status varchar(50) null,
Ordershipped datetime NULL,
Orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC) );

TABLE dbo.codes --cod
( codeID (pk uniqueidentifier NOT NULL),
trackid (varchar(50) NOT NULL),
confirmed datetime NULL,
code (varchar(2) NULL),
CONSTRAINT PK_codeID PRIMARY KEY CLUSTERED (codeID ASC) );

select cu.customerid, ,[CONFIRMED_LAST_WK] = sum(case when (select convert(date,cod.confirmed) from Codes cod where o.trackid = cod.trackid and cod.confirmed <>'') >= DATEADD(DAY, -8 + CASE DATEDIFF(DAY, 0, GETDATE()) % 7 WHEN 0 THEN 1 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END, GETDATE()) then 1 else 0 end) from orders o group by customerid

But this is getting "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." error, i cant find a good way to subquery this
??? Thanks in advance

2

Try this way:

select 
o.customerid 
  ,SUM(CONFIRMED_LAST_WK) AS CONFIRMED_LAST_WK
from orders AS o 
    OUTER APPLY
    (SELECT 
          CASE WHEN convert(date,cod.confirmed) 
                   >= DATEADD(DAY, -8 + 
                                  CASE DATEDIFF(DAY, 0, GETDATE()) % 7 
                                      WHEN 0 THEN 1 
                                      WHEN 5 THEN 2 
                                      WHEN 6 THEN 1 
                                  ELSE 0 
                                  END
                              , GETDATE())
             then 1 else 0 
             end AS CONFIRMED_LAST_WK
     from Codes AS cod 
     where 
           o.trackid = cod.trackid 
           and cod.confirmed <>''             
     )OA     
group by customerid

PS:
Nice that you inserted the DDL of your tables.This helps.
And also,it is recomended to insert some records in your tables, and show the desired output
And I should move/add an additional condition in the WHERE clause , like:

where 
           o.trackid = cod.trackid 
           and cod.confirmed <>''   
           and cod.confirmed >  DATEADD(DAY, -10 , GETDATE())

I added an extra line and cod.confirmed > DATEADD(DAY, -10 , GETDATE()) so that the range scanned will be smaller. (So that will not scan the entire table , depending of your indexes ofcourse) .

I used -10 to be sure to include all the candidates records but not older than X days (like one year old) because this is not taken in calculation , make sense?
Is good also -9 (...)

http://rextester.com/IQOCO56483

3

Thanks for reply

The desired output is to have each customerID produce a result calculated via all the customerids that have this confirmed tied to it within the past 7 working days

Customerid CONFIRMED_LAST_WK
100 4
101 0
102 5
103 19

...so CONFIRMED_LAST_WK needs to be a derived column; what you are proposing disrupts the flow of the original Select statement (whereas Customerid will be a GroupBy field in the result set)
HTH
S2017

4

I added and extra join in the OUTER APPLY, for the moment, to pull out the customerID
this is why is good to have some input(records) and output results

select 
o.customerid 
  ,SUM(CONFIRMED_LAST_WK) AS CONFIRMED_LAST_WK
from orders AS o 
    OUTER APPLY
    (SELECT 
          CASE WHEN convert(date,cod.confirmed) 
                   >= DATEADD(DAY, -8 + 
                                  CASE DATEDIFF(DAY, 0, GETDATE()) % 7 
                                      WHEN 0 THEN 1 
                                      WHEN 5 THEN 2 
                                      WHEN 6 THEN 1 
                                  ELSE 0 
                                  END
                              , GETDATE())
             then 1 else 0 
             end AS CONFIRMED_LAST_WK
     from Codes AS cod 
           INNER JOIN orders AS O2
           ON o2.trackid = cod.trackid
     where 
           o2.CustomerID = o.CustomerID 
           and cod.confirmed <>''   
           and cod.confirmed >  DATEADD(DAY, -10 , GETDATE())
     )OA     
group by customerid
5

OR this:

select 
   o.customerid 
  ,SUM(CASE WHEN convert(date,cod.confirmed) 
           >= DATEADD(DAY, -8 + 
                          CASE DATEDIFF(DAY, 0, GETDATE()) % 7 
                              WHEN 0 THEN 1 
                              WHEN 5 THEN 2 
                              WHEN 6 THEN 1 
                          ELSE 0 
                          END
                      , GETDATE())
     then 1 else 0 
     end) AS CONFIRMED_LAST_WK             
from orders AS o 
    LEFT JOIN Codes AS cod 
    ON o.trackid = cod.trackid
WHERE
    cod.confirmed <>''   
    and cod.confirmed >  DATEADD(DAY, -10 , GETDATE())
group by customerid
6

Here's my problem. I need to do the join between the Codes and Orders table only inside the CONFIRMED_LAST_WK derived column, as I am not tying in any of the other derived columns with this Codes table.
Thanks
S2017

7

OK, joining Codes and Orders after the SELECT may work, i have to look at the data coming back. ...no, the join after the select clause brings back way too many fields, so yes I need only the data for CONFIRMED_LAST_WK contained within its own derived column