• Zososql - Saturday, February 18, 2017 12:49 PM

    OK thanks... again the problem lies with each unique customerID has to produce a result with the above formula, the output needs to be something similar to

    CUSTID  HOLDING
    1OO        5
    101          0
    102          15
    103           2
    Total        22

    ...when i run this particular statement for the HOLDING column that I show above, I'm getting
    CUSTID  HOLDING
    100        150
    101         150
    102         150
    103         150
    Total        150
    hope that helps
    thanks
    z

    Maybe you need to make a change to the HOLDING field formula...   Your using a correlated subquery, and you're using a table alias that duplicates one that's already in use, which may or may not cause trouble.   Try this and let me know what results you get.

    ,[HOLDING] = SUM(
        (SELECT CASE WHEN o2.[status] LIKE '%shipment date schedule%' OR o2.[status] LIKE '%shipment ready%' THEN 1 ELSE 0 END
        FROM orders o2
        WHERE o2.CustomerID = cu.CustomerID)
        )

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)