Using MAX function in DateADD SQL. Error - Invalid aggregate function in where clause [MAX(date)]-CodePudding

I have a table 'CSALES' having columns such as customerid,transactiondate,quantity,price. I'm trying to find customers who have not been active in 1 month from a list of dates present in the transactiondate column. I've tried the following code but I'm unsure about the approach and the code is giving a compilation error

SELECT C.CUSTOMERID
FROM CSALES C

WHERE C.CUSTOMERID  NOT IN
(
    SELECT CS.CUSTOMERID FROM CSALES as CS
    WHERE CS.TRANSACTIONDATE > DATEADD(month, -1, MAX(CS.TRANSACTIONDATE )
);

I'm getting the following error

SQL compilation error: Invalid aggregate function in where clause [MAX(CS.TRANSACTIONDATE)]

What changes should I make in the code to reflect the requirement? Would MAX(date) be a right approach ?

CodePudding user response：

SELECT
  C.CUSTOMERID
FROM
  CSALES C
GROUP BY
  C.CUSTOMERID
HAVING
  MAX(C.TRANSACTIONDATE)
  <
  DATEADD(
    month,
    -1,
    (SELECT MAX(TRANSACTIONDATE) FROM CSALES)
  )

Or, assuming you have a customer table...

SELECT
  *
FROM
  CUSTOMER   C
WHERE
  NOT EXISTS (
    SELECT *
      FROM CSALES   CS
     WHERE CS.CUSTOMERID       = C.ID
       AND CS.TRANSACTIONDATE >= DATEADD(
                                   month,
                                   -1,
                                   (SELECT MAX(TRANSACTIONDATE) FROM CSALES)
                                 )
  )

Demo : dbfiddle

CodePudding user response：

there are multiple possibilities, you must check which is faster

SELECT C.CUSTOMERID
FROM CSALES C

WHERE C.CUSTOMERID  NOT IN
(
    SELECT CS.CUSTOMERID FROM CSALES as CS CROSS JOIN (SELECT MAX(TRANSACTIONDATE) maxdate FROM CSALES) t1
    WHERE CS.TRANSACTIONDATE > DATEADD(month, -1, maxdate)
);

GO

| CUSTOMERID |
| ---------: |
|          4 |

SELECT DISTINCT C.CUSTOMERID
FROM CSALES C CROSS JOIN (SELECT MAX(TRANSACTIONDATE) maxdate FROM CSALES) t1
WHERE  NOT EXISTS (SELECT 1 FROM CSALES WHERE CUSTOMERID = c.CUSTOMERID AND TRANSACTIONDATE > DATEADD(month, -1, maxdate))
;
GO

| CUSTOMERID |
| ---------: |
|          4 |

db<>fiddle here

CodePudding user response：

SELECT CUSTOMERID
FROM
  CSSALES
GROUP BY CUSTOMERID
HAVING
  MAX(TRANSACTIONDATE) < ADD_MONTHS(CURRENT_DATE(),-1)

Shawnt00 is right the max date in the transaction table is irrelevant if you just want any customer that hasn't been active in 1 calendar month.

In snowflake use CURRENT_DATE() to get the date portion of Today then ADD_MONTHS(date,int) to get months. Other functions work two but these are pretty easy. If you only want customers to remove duplicate CUSTOMERIDS group by the column.