date with maxium log-ins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

date with maxium log-ins

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-11 : 21:15:05

The following query return:

orgid time number of log in
10079 2004-05-29 08:00:00.000 8
10079 2004-05-03 15:00:00.000 6
10079 2004-05-28 16:00:00.000 5
10078 2004-05-03 11:00:00.000 5
10078 2004-05-11 14:00:00.000 1
10080 2004-05-19 08:00:00.000 5
10080 2004-05-28 13:00:00.000 3

--------------------------------------------
select (po.org_id) as [orgid],
convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000') as time
,count(*) as [number of log-in]

from login_history lh
inner join login l on l.login_id = lh.login_id
inner join p_org po on po.p_id = l._p_id
inner join p_login pl on pl.login_id = lh.login_id
inner join p_account pa on pa.p_id = pl.p_id

where
login_history_dt between '20000101' and '20040708'
group by
po.org_id,convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000')
order by
po.org_id,count(*) desc, convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000')

I would like to return only the org_id, and time which has the maximum number of login-in group by org_id. The result should looks like:

orgid time number of log in
10079 2004-05-29 08:00:00.000 8
10078 2004-05-03 11:00:00.000 5
10080 2004-05-19 08:00:00.000 5

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-11 : 23:36:00

Do your select into a temp table or table variable. Then just select the results with two inner joins to the table. One will be on all the columns of the table. The other will be on (SELECT orgid, MAX(loginnumber)). I'm sure there are about a million other ways to do it if someone else wants to jump in with something more efficient. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-11 : 23:43:00

Thank you!

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-12 : 02:25:32

As Derrick said:


select (po.org_id) as [orgid],
convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000') as time
,count(*) as [number of log-in]

into #login_history


from login_history lh
inner join login l on l.login_id = lh.login_id
inner join p_org po on po.p_id = l._p_id
inner join p_login pl on pl.login_id = lh.login_id
inner join p_account pa on pa.p_id = pl.p_id
where 
login_history_dt between '20000101' and '20040708' 
group by 
po.org_id,convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000')
order by 
po.org_id,count(*) desc, convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000')


select orgid, 
	(select [time] from #login_history where orgid = o.orgid 
	and [number of log-in] = max(o.[number of log-in])),
max([number of log-in])
from #login_history o
group by orgid

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources