CODEWITHSUNDEEP
sqlsql-serversql-server-2008selectwhere-clause
Smix
Smix

Reputation: 69

SQL select issue : get max value for each day

I have a problem with my SQL select, I need to get the max value for the column (currentcount) per each day for the period between DateDebut and DateFin, but the returned result show me multiple values.

My SQL statement:

DECLARE @dateDebut DATE, 
        @dateFin DATE, 
        @SITA_METIER VARCHAR(20), 
        @DEFSITE VARCHAR(20);

SET @dateDebut = '01/01/2017';
SET @dateFin = '31/12/2018';


SELECT DISTINCT
    CONVERT(DATE, attemptdate) AS Date, 
    MAX(currentcount) AS MAXUSERS
FROM
    logintracking 
WHERE
    attemptdate BETWEEN CONVERT(DATE, @dateDebut) AND CONVERT(DATE, @dateFin)
    AND logintracking.clientaddr IN ('10.118.254.21', '10.118.254.156') 
GROUP BY
    attemptdate, currentcount

Result:

enter image description here

Desired result: only the max value for the column (currentcount) for each day

PS: attemptdate type is Timestamp, that's why I need to cast it into a simple date.

I also tried to use 'having' function but still get multiple values

Upvotes: 0

Views: 1360

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You already use GROUP BY the distinct is no make sense, so the distinct can be removed.

then you just modify CONVERT(Date,attemptdate) instead of attemptdate in Group by, and only need to group by CONVERT(Date,attemptdate)

select CONVERT(Date,attemptdate) as Date, max(currentcount) as MAXUSERS
from logintracking 
where attemptdate between @dateDebut and @dateFin
  and logintracking.clientaddr in ('10.118.254.21', '10.118.254.156') 
group by CONVERT(Date,attemptdate)

Note:

Your @dateDebut and @dateFin already are Date type. No need to use CONVERT function.

Upvotes: 2

Related Questions