Row to column and AM/PM for datetime

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)

Row to column and AM/PM for datetime

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-11 : 14:02:02

The following query returns the result looks like:

Highest Log-in Volume Day/Hour- Account Holder
------------------------------
2004-04-23 10:00:00.000

Highest Log-in Volume Day/Hour-Holder
------------------------------
2004-03-23 11:00:00.000

Highest Log-in Volume Day/Hour -member
------------------------------
2004-07-23 16:00:00.000

--Query start from here

declare @start_date datetime
declare @end_date datetime
declare @orgid int

select @start_date ='19000630 23:59:59'
select @end_date = '20040701 23:59:59'
select @orgid = 2345678

----- [TopLoginHour-Account Holder]
select top 1

convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000') as [Highest Log-in Volume Day/Hour-Account Holder]

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 @start_date and @end_date and
po.org_id in (@orgid)

group by convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000')
order by count(*) desc, convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000')

----[TopLoginHour- Holder]

select top 1
convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000') as [Highest Log-in Volume Day/Hour- Holder]

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 @start_date and @end_date and
po.org_id in (@orgid)

group by convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000')
order by count(*) desc, convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000')

-----[TopLoginHour- Member]

select top 1
convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000') as [Highest Log-in Volume Day/ Member]

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.medicive_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 @start_date and @end_date and
po.org_id in (@orgid)

group by convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000')
order by count(*) desc, convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000')

How can i make the output looks like:( put them in one line)


 
 Account Holder      Holder                   Member
----------------   -------------------       ----------------
2004-04-23 10:00am    2004-03-23 11:00am      2004-07-23 4:00 pm

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-11 : 14:14:10

The following example show how you could use union along with max to pivot the data into 1 row:

create table t ( v int)

insert into t
select 1 union all
select 2 union all
select 3


select max(a) a, max(b) b,max(c) c
from
(
select v a,0 b,0 c
from  t
where v = 1
union 
select 0,v,0
from  t
where v = 2
union 
select 0,0,v
from  t
where v = 3
) d


drop table t

The following UDF might be useful for formatting those dates also.

http://weblogs.sqlteam.com/leed/archive/2004/06/04/1503.aspx?Pending=true

Kristen
Test

22859 Posts

Posted - 2004-07-11 : 15:16:16

The use of convert(datetime, convert(varchar(13), lh.login_history_dt, 120) + ':00:00.000') to get the Hour is horribly slow in practice
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35296&SearchTerms=seconds

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources