0
votes
SELECT 
    c.customer_Id, SUM(total_amt), DATEDIFF(YEAR, DOB, GETDATE()) as DOB
FROM 
    Transactions T
INNER JOIN 
    Customer C ON T.cust_id = C.customer_Id
GROUP BY
    c.customer_Id, DATEDIFF(YEAR, DOB, GETDATE())
HAVING
    (DATEDIFF(YEAR, DOB, GETDATE()) BETWEEN 25 AND 35)
    AND (SELECT tran_date
         FROM Transactions
         HAVING tran_date BETWEEN DATEADD (DAY , -30, MAX(tran_date)) AND MAX(tran_date))

I am getting this error while executing the above code:

Msg 4145, Level 15, State 1, Line 136
An expression of non-boolean type specified in a context where a condition is expected, near ')'

1
Please explain what you are trying to do. Sample data and desired results help. A non-working query does not necessary convey this information.Gordon Linoff
SELECT tran_date ... subquery returns some dataset, not boolean. Maybe EXISTS operator is lost? But it is non-correlated, "always true" in general, and it uses explicit GROUP BY.\Akina

1 Answers

0
votes

If you want a summary each customer's most recent 30 days of transaction where the customers are between 25 and 35 years old, then you basically want to filter before aggregation:

SELECT c.customer_Id, SUM(total_amt), DATEDIFF(YEAR, DOB, GETDATE()) as DOB
FROM Customer C JOIN
     (SELECT T.*,
             MAX(tran_date) OVER (PARTITION BY cust_id) as max_tran_date
      FROM Transactions T
     ) T
     ON T.cust_id = C.customer_Id
WHERE DOB >= DATEADD(YEAR, -35, GETDATE()) AND
      DOB < DATEADD(YEAR, -24, GETDATE()) AND
      t.tran_date >= DATEADD(DAY, -30, max_tran_date)
GROUP BY c.customer_Id, DATEDIFF(YEAR, DOB, GETDATE());