Aggregate function query

SELECT COUNT(DISTINCT(CONVER(VARCHAR(10), Logintime, 101)))
AS COUNT
FROM UserLoginStatus
WHERE UserId=15 and MONTH (Logintime) =9
HAVING DATEDIFF(MINUTE,MIN(Logintime),MAX(Logouttime))< 480)

In my query i need to fetch Month(Logintime) in where condition how to do?..
i need to get data based on userid and based on month.


so i have to write condition for month also how to write that one?.

Hi Sasi,


If you not using where condition, you said ur getting two count right , can u see whether that two count has userid 15 ?


SELECT UserID,
COUNT(DISTINCT(CONVERT(VARCHAR(10), Logintime, 101)))
AS COUNT
FROM
UserLoginStatus
Group By UserID
HAVING DATEDIFF(MINUTE,MIN(Logintime),MAX(Logouttime))< 480


This gives result with userid, so later you can query with that userid by using where condition 


Other way :

SELECT 
COUNT(DISTINCT(CONVERT(VARCHAR(10), Logintime, 101)))
AS COUNT
FROM
UserLoginStatus
Where UserID IN( Select UserID 
FROM
UserLoginStatus
Group By UserID
HAVING DATEDIFF(MINUTE,MIN(Logintime),MAX(Logouttime))< 480)