Select SUM(CASE When CPayment='Cash' Then CAmount Else 0 End ) as CashPaymentAmount,
       SUM(CASE When CPayment='Check' Then CAmount Else 0 End ) as CheckPaymentAmount
from TableOrderPayment
Where ( CPayment='Cash' Or CPayment='Check' ) AND CDate<=SYSDATETIME() and CStatus='Active';

You simply need to balance your ().

Either delete the ( between when and left or add a ) after '2015'.

I think you do not need the last column in your GROUP BY clause:

SELECT     Household.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode, 
           SUM(Asset.Value) AS HHValue,
           SUM(CASE WHEN financialinst.name = 'National Financial Services' THEN Asset.Value ELSE 0 END) AS NationalValue, 
           SUM(CASE WHEN financialinst.name <> 'National Financial Services' THEN Asset.Value ELSE 0 END) AS DirectValue
FROM       PlanFirmSpecCode INNER JOIN
           FinPlanSpecCode ON PlanFirmSpecCode.PlanFirmSpecCodeID = FinPlanSpecCode.PlanFirmSpecCodeID RIGHT OUTER JOIN
           Household INNER JOIN
           Account ON Household.HouseholdID = Account.HouseholdID INNER JOIN
           Asset ON Account.AccountID = Asset.AccountID INNER JOIN
           FinancialPlanner ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID LEFT OUTER JOIN
           FinancialInst ON Account.FinancialInstID = FinancialInst.FinancialInstID ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE      (Account.Closed <> 'Y') AND (Asset.Value > '0') and (Account.Managed <> 'N')
GROUP BY   Household.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode
Order by   household.Name

To answer this question, I did the following (all of the code below is available on the fiddle here):

Tables - as per question.

Data - simulated.

INSERT INTO customer VALUES
(1, 'cust1', 'Sales'),
(2, 'cust2', 'Sales'),
(3, 'cust3', 'Sales'),
(4, 'cust4', 'Sales'),
(5, 'cust5', 'Sales');

and

INSERT INTO invoice VALUES
(1,  1, '2022-01-01', '2022-01-03',         NULL,         NULL,   1.0),
(2,  1, '2022-01-03', '2022-01-08',         NULL,         NULL,   1.0),
(3,  1, '2022-01-05', '2022-01-13',         NULL,         NULL,   1.0),

(4,  2, '2022-02-01', '2022-02-03',         NULL,         NULL,   2.0),
(5,  2, '2022-02-03', '2022-02-08',         NULL,         NULL,   2.0),
(6,  2, '2022-02-05', '2022-02-13',         NULL,         NULL,   2.0),


(7,  3, '2022-03-01',         NULL, '2022-03-07',         NULL,  3.0),
(8,  3, '2022-03-08',         NULL, '2022-03-14',         NULL,  3.4),
(9,  3, '2022-03-14',         NULL, '2022-03-21',         NULL,  3.4),

(10, 4, '2022-04-01', '2022-04-06', '2022-04-10', '2022-04-22',  4.0),  -- with 4, some refunded
(11, 4, '2022-04-02', '2022-04-07', '2022-04-12', '2022-04-25',  4.1),  -- some not!
(12, 4, '2022-04-03', '2022-04-09', '2022-04-16', '2022-04-28',  4.2),

(13, 4, '2022-04-20', '2022-04-27',         NULL,         NULL, 100.1),
(14, 4, '2022-04-21', '2022-04-29',         NULL,         NULL, 100.2);

The first query is "exploratory" - i.e. getting the required information together. Use is made of window functions - these are very powerful and are well worth getting to know - they will repay any effort spent on learning them many times over!

SELECT
  ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY c.id, i.time_issued) AS rn,
  i.invoice_number AS invno, c.id AS cid, c.customer_name AS cname, c.dept AS cdept,
  i.time_issued AS idate, i.time_paid AS ipaid, 
  i.time_canceled AS icancel, 
  i.time_refunded AS irefund,
  LAST_VALUE(i.time_issued) OVER (PARTITION BY c.id ORDER BY c.id) AS l_inv_date,
  ROUND(COALESCE(i.total_price, 0), 2)   AS tot_price,
  ROUND(SUM(i.total_price) OVER (PARTITION By c.id ORDER BY c.id), 2) AS s_tot,
  ROUND(SUM(NULLIF(time_refunded IS NOT NULL, NULL) * total_price)
     OVER (PARTITION BY c.id ORDER BY c.id), 2) AS refund,

  ROUND(SUM(i.total_price) OVER (PARTITION By c.id ORDER BY c.id) -
  SUM(NULLIF(time_refunded IS NOT NULL, NULL) * total_price)
     OVER (PARTITION BY c.id ORDER BY c.id), 2) AS billed


FROM
  customer c
LEFT JOIN invoice i
  ON i.customer_id = c.id
ORDER BY cid, idate;

Result:

For the result, see the fiddle.

And then, we run the query:

SELECT
  cid, cname, inv_count, l_inv_date, s_tot, refund, valid_billed
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY c.id, i.time_issued) AS rn,
    c.id AS cid, c.customer_name AS cname,
    
    COUNT(i.invoice_number) OVER (PARTITION BY c.id ORDER BY c.id) AS inv_count,
    
    LAST_VALUE(i.time_issued) OVER (PARTITION BY c.id ORDER BY c.id) AS l_inv_date,
    
    ROUND(COALESCE(SUM(i.total_price) OVER (PARTITION BY c.id ORDER BY c.id), 0), 2) AS s_tot,
    ROUND(COALESCE(SUM(NULLIF(time_refunded IS NOT NULL, NULL) * total_price)
      OVER (PARTITION BY c.id ORDER BY c.id), 0), 2) AS refund,

    ROUND(COALESCE(SUM(i.total_price) OVER (PARTITION By c.id ORDER BY c.id) -
      SUM(NULLIF(time_refunded IS NOT NULL, NULL) * total_price)
        OVER (PARTITION BY c.id ORDER BY c.id), 0), 2) AS valid_billed
FROM
  customer c
LEFT JOIN invoice i
  ON i.customer_id = c.id
) AS sub1
WHERE rn = 1
ORDER BY cid;

Result:

cid  cname  inv_count   l_inv_date  s_tot   refund  valid_billed
1    cust1          3   2022-01-05      3        0             3
2    cust2          3   2022-02-05      6        0             6
3    cust3          3   2022-03-14    9.8        0           9.8
4    cust4          5   2022-04-21  212.6     12.3         200.3
5    cust5          0         NULL      0        0             0

• in future, when asking questions such as this, please include a fiddle with your tables and data. The usefulness of this is twofold - it creates a single source of truth for the question and it eliminates duplication of effort on behalf of those trying to help you.

You need to remove the outer case statement. It should be:

Select sum( case when ..... then .... end) from foo

You have

Select case when sum(case when.... then .... end) end from foo

The when clause expects something that will resolve to either true or false. That's what your error message is trying to tell you.

I'm still not 100% sure what your end goal is but you either need to remove the wrapping case statement like I said above or pull the case condition out of the sum.

Select
   Case 
       When <condition > then sum(<column>)
        When <condition > then sum(<column >)
         Else sum(<column >)
         End
From foo

CASE WHEN {item} IN('CITL','CITV','CRDD','etc'....) THEN {amount} ELSE 0 END

OR

CASE WHEN {item} = 'CITL' THEN {amount} WHEN {item} = 'CITV' THEN {amount} WHEN {item} = 'CRDD' THEN {amount} etc ELSE 0 END

The first is cleaner but the second works too. Cheers!

Hello everyone,

I have 3 CASE statements and I am trying to understand how to properly add them without CTE (because it will make the whole code a lot more complex). Does someone know how it can be done?

--CASE WHEN S1FRAC != '0' THEN '1' ELSE '0' END AS 'SORT_ORDER1'

--CASE WHEN MASTER_BOL_NUMBER = '' OR MASTER_BOL_NUMBER = '.' THEN '0' WHEN MASTER_BOL_P8= AR_ORD THEN '1' ELSE '0' THEN 'SORT_ORDER2'

--CASE WHEN MASTER_BOL_NUMBER != '' OR MASTER_BOL_NUMBER= '.' THEN '0' WHEN AR_LOAD = '0' THEN '0' ELSE AR_ORD = L3BOL_P8 THEN '1' ELSE '0' END AS 'SORT_ORDER3'

-- CASE WHEN  SORT_ORDER1 + SORT_ORDER2 + SORT_ORDER3 != 0 THEN 1 ELSE 0 AS 'SORT_ORDER'