Need help to solve query (using datepart)

Question

select dateadd(mi, -1*(datepart(mi, logindt) % 60),dateadd(ss, -1*(datepart(ss, logindt)),dateadd(ms, -1*(datepart(ms, logindt)), logindt)))[Date],count(*)[Count] from TableName        

where logindt between 'Dec 1 2009 12:00 AM' and 'Dec 2 2009 23:59 PM'            
group by             
dateadd(mi, -1*(datepart(mi, logindt) % 60),dateadd(ss, -1*(datepart(ss, logindt)),dateadd(ms, -1*(datepart(ms, logindt)), logindt)))

---

We have an application in which a number of people login, the problem is I want to make a query through which I can count number of people logged in to the system at a particular time say 10 am/11/12/14.

Above query have made using datepart, through which i can check number of people signed at say 10 am OR sign out.

For example above query returns 10 people sign/signout at 10 am but it does not show me people who are currently using system.

Answer 1

Find all login/logout events before 10am and filter those such that max(login) > max(logout).

SELECT COUNT * FROM 
(SELECT *
FROM Table
WHERE login < 10am //your date function here!
and logout < 10am ) a
GROUP BY a.USER
HAVING MAX(a.login) > MAX(a.logout)

Something like that perhaps?