Q:

SQL sub query select doubts

LogTable in SQL Server
timestamp                  user_name     session_id
2012-10-17 06:30:10        Sanjay        034A955F
2012-10-17 06:30:20        Sanjay        034A955F
2012-08-20 13:20:59        John          0547961A 
2012-08-20 13:21:05        John          0547961A 
2012-08-20 13:22:10        John          0547961A 
2012-10-17 04:02:10        John          0977661B 

Desired output
user       Total_login      This_Month_Login   Recent_Login  
Sanjay     2                2                  2012-10-17 06:30:10 
John       4                1                  2012-10-17 04:02:10 

I tried to write SQL SELECT query to get above output but I am not good in SQL so couldn't take it ahead and wrote only for 2 columns. Can you please help me to get above select output?
select user_name as 'User', count (distinct session_id) as 'Total_Login' 
from "LogTable" 
group by user_name

A:

try this query
select
    T.user_name as User,
    count(distinct T.session_id) as Total_Login,
    count(distinct case when datediff(mm, T.timestamp, getdate()) = 0 then session_id else null end) as This_Month_Login,
    max(T.timestamp) as Recent_Login
from LogTable as T
group by T.user_name

I've made an assumption that you need to count only distinct session_id in your output.
If you not, than you need
select
    T.user_name as User,
    count(*) as Total_Login,
    count(case when datediff(mm, T.timestamp, getdate()) = 0 then 1 else null end) as This_Month_Login,
    max(T.timestamp) as Recent_Login
from LogTable as T
group by T.user_name

