October 21, 2009 at 5:20 am
I have a table with 3 columns:
datetime | applicationtype | SystemUser
e.g.
2006-08-21 08:25:04:633 | SQLAgent | smithj
2006-08-21 09:20:11:633 | SQLAgent | smithj
I'm trying to acheive a distinct selection on days (not time) and I would like to return rows where the date is most the current date for each SystemUser.
I've tried:
select distinct left(LoginTime,11) as date, [Applicationtype], [SystemUser] from DW.ServerLogonHistory
ORDER BY date
But this just gives me the same result as a 'select all' except where the first column is truncated, displaying only the first 11 characters. i.e:
2006-08-21 | SQLAgent | smithj
2006-08-21 | SQLAgent | smithj
Cheers
Andy
October 21, 2009 at 5:26 am
Try this:
SELECT DISTINCT CONVERT(datetime, CONVERT(char(8),LoginTime,112), 112) AS [date], [Applicationtype], [SystemUser]
FROM DW.ServerLogonHistory
ORDER BY CONVERT(datetime, CONVERT(char(8),LoginTime,112), 112)
-- Gianluca Sartori
October 21, 2009 at 5:27 am
You need to 'floor' out the time.
select distinct DATEDIFF(DAY,0,LoginTime) as date, [Applicationtype], [SystemUser] from DW.ServerLogonHistory
ORDER BY date
October 21, 2009 at 5:35 am
Do you want to group it by sysuser and day?
CREATE TABLE #T1(dt datetime, AT varchar(50), SysUser Varchar(50))
INSERT INTO #T1 VALUES ('2006-08-21 08:25:04:633', 'SQLAgent', 'smithj')
INSERT INTO #T1 VALUES ('2006-08-22 08:25:04:633', 'SQLAgent', 'smithj')
INSERT INTO #T1 VALUES ('2006-08-21 09:20:11:633', 'SQLAgent', 'smithj')
INSERT INTO #T1 VALUES ('2006-08-22 09:20:11:633', 'SQLAgent', 'Andy')
INSERT INTO #T1 VALUES ('2006-08-22 08:25:04:633', 'SQLAgent', 'Andy')
Select * from (
Select *,Row_Number()
OVER (PARTITION BY DATEADD( day, 0, DATEDIFF( day, 0, dt)), sysuser order by dt desc) as row_no
from #T1) Test
Where row_no = 1
---------------------------------------------------------------------------------
October 21, 2009 at 6:51 am
Or the simple version:
Select distinct DATEADD(day, 0, DATEDIFF(day, 0, dt)) as DateOnly, AT, sysuser
from #T1
order by DateOnly, AT, sysuser
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 21, 2009 at 7:34 am
Thank you for these replys.
I think the nearest that came to what I am after was:
SELECT DISTINCT CONVERT(datetime, CONVERT(char(8),LoginTime,112), 112) AS [date], [Application], [SystemUser]
FROM DW.ServerLogonHistory
ORDER BY CONVERT(datetime, CONVERT(char(8),LoginTime,112), 112)
which returned:
2008-08-01 00:00:00.000 | SQLAgent | Smithj
2008-08-01 00:00:00.000 | SQLAgent | Smithj
But this still returns non-distinct dates. I'm trying select rows on the latest date entry for each SystemUser.
October 21, 2009 at 8:31 am
It's very strange that you don't get distinct values, there must be something different within the two records you posted.
Try grouping by SystemUser, I think this is what you are after:
SELECT MAX(CONVERT(datetime, CONVERT(char(8),LoginTime,112), 112)) AS [date], MAX([Application]) AS [Application], [SystemUser]
FROM DW.ServerLogonHistory
GROUP BY [SystemUser]
ORDER BY 1
-- Gianluca Sartori
October 21, 2009 at 8:35 am
that did the trick excellent!
Thank you
October 21, 2009 at 8:41 am
Glad I could help.
-- Gianluca Sartori
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply