CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alex Alexy
Alex Alexy

Reputation: 13

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

Upvotes: 1

Views: 525

Answers (3)

codingbiz
codingbiz

Reputation: 26386

Try this

Select 
    USER_NAME, 
    COUNT(*) As Total_login,
    COUNT(case when MONTH(getdate())=MONTH(tm) then 1 end) As This_Month_Login,
    MAX(tm) as Recent_Login
From MyTable
group by user_name 
order by user_name desc

Upvotes: 0

roman
roman

Reputation: 117380

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

Upvotes: 0

MatBailie
MatBailie

Reputation: 86716

DECLARE
  @this_month DATETIME
SELECT
  @this_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate), 0)

SELECT
  user_name,
  COUNT(*)                                                  AS total_log,
  SUM(CASE WHEN timestamp >= @this_month THEN 1 ELSE 0 END) AS log_this_month,
  MAX(timestamp)                                            AS recent_login
FROM
  "LogTable"
GROUP BY
  user_name

Upvotes: 2

Related Questions