I'm pretty new here so I am not sure if this correct place to ask these questions but please redirect me if it is not.
I am little rusty on my T-SQL for MS so I need a little assistance.
I'm trying to figure out a way to get the day split into 3 times, morning, noon and night using the [Audit].[COGIPF_USERLOGON].[TIME STAMP] column so I can track the logins throughout a day. This will help me trend for the month/year so I can plan for my capacity needs.
I am trying to get a SQL statement that will give me distinct user count/sum from using the COGIPF_USERLOGON table grouped by date or maybe even just the day of the month (06,07,08).
USERNAME TIME STAMP
aa Feb 06, 2010 12:10:58
aa Feb 06, 2010 13:20:30
bb Feb 07, 2010 12:20:45
aa Feb 07, 2010 12:20:37
I want to see a Total of 1 distinct user on Feb 06 and a Total of 2 distinct users on Feb 07.
DISTINCT USER COUNT DATE
1 Feb 06, 2010
2 Feb 07, 2010
I am using MSSQL 2005 for the Audit dB.
Can anyone help?
Thanks,
Eric
hi eric,
i put this together real quick. we use oracle. it appears to return accurate values per day. probably can tweak to split out shifts within days i imagine. in order to group by day, i had to format timestamp seemingly because in our environment, hh:mm:ss was not visible which was messing up the count.
select to_char(cogipf_localtimestamp,'Mon-dd-yyyy') as timestamp,
count(distinct cogipf_username) as cnt
FROM cogipf_userlogon
group by to_char(cogipf_localtimestamp,'Mon-dd-yyyy')
order by to_char(cogipf_localtimestamp,'Mon-dd-yyyy') desc
hope this helps.
thanks,
joe