February 25, 2008 at 8:59 am
I have a table called LOGIN with columns USER, DATEIN, DATEOUT
I am having trouble with a query that will count the number of log ins (DATEIN) each user has for January 2008 and February 2008.
The output would ideally be something like..
USER JAN FEB
-----------------------------
user1 25 13
user2 16 22
etc...
Any help is appreciated.
Thanks
February 25, 2008 at 9:02 am
Are you trying to get a count by month by user? or do you just want a count by user for the period?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 25, 2008 at 9:05 am
Matt Miller (2/25/2008)
Are you trying to get a count by month by user? or do you just want a count by user for the period?
Yes, count by month by user so user1, 2 etc ... should have a count of logins in January and February.
Thanks.
February 25, 2008 at 10:21 am
Select user,
datepart(yyyy,datein)*100+datepart(mm,datein) as yearmonth,
count(datein) as LoginCount
from login
WHERE
datein >=cast('20080101' as datetime) and datein<cast('20080301' as datetime)
group by
user, datepart(yyyy,datein)*100+datepart(mm,datein)
Edit...forgot the WHERE clause
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 25, 2008 at 7:54 pm
Hi,
You can even try out with PIVOT functions.
http://technet.microsoft.com/en-us/library/ms177410.aspx
Thanks -- Vj
February 26, 2008 at 6:55 am
Matt Miller (2/25/2008)
Select user,
datepart(yyyy,datein)*100+datepart(mm,datein) as yearmonth,
count(datein) as LoginCount
from login
WHERE
datein >=cast('20080101' as datetime) and datein<cast('20080301' as datetime)
group by
user, datepart(yyyy,datein)*100+datepart(mm,datein)
Edit...forgot the WHERE clause
Thank you Matt (I knew this problem wasn't as obvious as I thought it was).
Is there any way to get the Jan and Feb data in side-by-side columns
e.g.
User Jan Feb
----------------
user1 12 17
etc
February 26, 2008 at 7:35 am
Sure - using the "old-style" pivotting syntax:
Select user, datepart(yyyy,datein) as Yr,
sum(case when datepart(mm,datein) =1 then 1 else 0 end) as Jan,
sum(case when datepart(mm,datein) =2 then 1 else 0 end) as Feb
from login
WHERE
datein >=cast('20080101' as datetime) and datein<cast('20080301' as datetime)
group by
user, datepart(yyyy,datein)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 11:01 am
Matt Miller (2/26/2008)
Sure - using the "old-style" pivotting syntax:
Select user, datepart(yyyy,datein) as Yr,
sum(case when datepart(mm,datein) =1 then 1 else 0 end) as Jan,
sum(case when datepart(mm,datein) =2 then 1 else 0 end) as Feb
from login
WHERE
datein >=cast('20080101' as datetime) and datein<cast('20080301' as datetime)
group by
user, datepart(yyyy,datein)
Very interesting, thank you Matt.
I can add the "datepart" code to my (minimal but growing) repertoire 🙂 Didn't know we can break the date into pieces.
Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply