1

Trying to change the following code to allow me to use min in where clause

SELECT od.personID
,'Uno' AS [CustomerType]
FROM dbo.orderDetails od
WHERE od.orderDate BETWEEN CONVERT(date, '2016-02-01') AND @StartDate
AND MIN(odd.orderDate) BETWEEN CONVERT(date, '2016-02-01') AND @StartDate)
GROUP BY personID
NinjaDeveloper
  • 1,620
  • 3
  • 19
  • 51
Faiz
  • 273
  • 2
  • 7
  • 21
  • cheers, that is so silly of me. Its been a long day forgetting basics lol – Faiz Jun 17 '16 at 14:09
  • Possible duplicate of [Aggregate function in SQL WHERE-Clause](http://stackoverflow.com/questions/6319183/aggregate-function-in-sql-where-clause) – Ako Jun 17 '16 at 14:18

1 Answers1

6

Move the MIN to the HAVING section

SELECT od.personID
,'Uno' AS [CustomerType]
FROM dbo.orderDetails od
WHERE od.orderDate BETWEEN CONVERT(date, '2016-02-01') AND @StartDate     
GROUP BY personID
HAVING MIN(odd.orderDate) BETWEEN CONVERT(date, '2016-02-01') AND @StartDate
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • just a quick question that statement in having clause means that the first order date of that person is between that date period right? just clarifying – Faiz Jun 17 '16 at 14:14
  • if I add on the statement `COUNT(PersonID) = 1` in the having clause that will restrict the results to only those customers that only bought once? – Faiz Jun 17 '16 at 14:18
  • Yes, but I will use `AND COUNT(orderDate) = 1` or `orderID` to make it more readable about what are you counting ;). – Juan Carlos Oropeza Jun 17 '16 at 14:23
  • ah yes ofcource. Cheers Thanks. – Faiz Jun 17 '16 at 14:33