Getting the next row in this row

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

General SQL Server Forums

New to SQL Server Programming

Getting the next row in this row

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2011-02-24 : 07:32:12

Basically I Am Developing Attendance System data of attendance like this
(UserID) (CheckTime) (Checktype(In or Out))
(1) (10/18/2010 2:39:08 PM) (I)
(1) (10/18/2010 9:12:46 PM) (o)
(1) (10/19/2010 8:56:21 AM) (I)
(1) (10/19/2010 8:24:13 PM) (O)

I want TimeIn and TimeOut In front Of UserID like this

(UserId) (TimeIn) (TimeOut)
(1) (10/18/2010 2:39:08 PM) (10/18/2010 9:12:46 PM)
(1) (10/19/2010 8:56:21 AM) (10/19/2010 8:24:13 PM)

any ideas????
Thanks In Advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-24 : 08:09:59

[code]
select *
from (
select *,
row_no = row_number() over (partition by UserID, Checktype order by CheckTime)
from yourtable
) d
pivot
(
min (CheckTime)
for Checktype in ([I], [O])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

asif372
Posting Yak Master

100 Posts

Posted - 2011-02-24 : 13:33:13

Sir Thanks For Your Quick Responce
i have tried but the Error is Coming 'row_number' is not a recognized function name.
I Am Using Sql Server 2000

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-02-24 : 23:14:10

For 2000, using aggregate functions. Code is perhaps a bit ugly but getting the same result as khtan's. If you need help in detail, post a message.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-25 : 02:32:51

[code]
-- Sample Table
declare @tbl table
(
UserID int,
CheckTime datetime,
CheckType char
)

-- Sample Data
insert into @tbl
select 1, '2010-10-18 14:39:08', 'I' union all
select 1, '2010-10-18 21:12:46', 'O' union all
select 1, '2010-10-19 08:56:21', 'I' union all
select 1, '2010-10-19 08:24:13', 'O'

-- Query
select UserID,
min(CheckTime) as TimeIn,
max(CheckTime) as TimeOut
from (
select *,
row_no = (select count(*) from @tbl x where x.UserID = t.UserID and x.CheckType = t.CheckType and x.CheckTime <= t.CheckTime)
from @tbl t
) t
group by UserID, row_no

-- Result
/*
UserID TimeIn TimeOut
----------- ------------------------ -----------------------
1 2010-10-18 14:39:08.000 2010-10-18 21:12:46.000
1 2010-10-19 08:24:13.000 2010-10-19 08:56:21.000

(2 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

asif372
Posting Yak Master

100 Posts

Posted - 2011-02-25 : 05:54:45

Sir Thanks For Your Quick Responce I Dont Have Words To Thank You I Have Tried Your Question It is Working Fine but not as i am Expecting

Basically I Am Developing Attendance System data of attendance like this
(UserID) (CheckTime) (Checktype(In or Out))
(1) (10/18/2010 2:39:08 PM) (I)
(1) (10/18/2010 9:12:46 PM) (o)
(1) (10/19/2010 8:56:21 AM) (I)
(1) (10/20/2010 8:50:21 AM) (I)
(1) (10/20/2010 8:24:13 PM) (O)

I want TimeIn and TimeOut In front Of UserID like this

(UserId) (TimeIn) (TimeOut)
(1) (10/18/2010 2:39:08 PM) (10/18/2010 9:12:46 PM)
(1) (10/19/2010 8:56:21 AM) (Null)
(1) (10/20/2010 8:50:21 AM) (10/20/2010 8:24:13 PM)

First Order By Usereid and CheckTime Select TimeOut From Next Row Of TimeIn If Exists TimeOut If Not Exists TimeOut In Next Row Of TimeIn Then Save Null In It Then In Front Of UserID and TimeIn Mark TimeOut

How Can We Do This Any Ideas
Thanks In Advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-25 : 06:51:41

you will need a temp table


-- create a temp table
create table #temp
(
	row_no		int	identity(1,1),
	UserID		int,
	CheckTime	datetime,
	CheckType	char
)

-- insert into temp table from your table
insert into #temp (UserID, CheckTime, CheckType)
select UserID, CheckTime, CheckType
from   yourtable
order by CheckTime

-- the result query
select	t1.UserID, TimeIn = t1.CheckTime, TimeOut = t2.CheckTime
from	#temp t1
	left join #temp t2	on	t1.UserID	= t2.UserID
				and	t1.row_no	= t2.row_no - 1
				and	t2.CheckType	= 'O'
where	t1.CheckType	= 'I'

KH
[spoiler]Time is always against us[/spoiler]

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-02-25 : 22:20:52

Look like you change BR ....

Try this to see if it works for you.

declare @temp table(UserID int,CheckTime datetime,Checktype char(1))
insert into @temp values(1, '10/18/2010 2:39:08 PM', 'I')
insert into @temp values(1, '10/18/2010 9:12:46 PM', 'o')
insert into @temp values(1, '10/19/2010 8:56:21 AM', 'I')
insert into @temp values(1, '10/20/2010 8:50:21 AM', 'I')
insert into @temp values(1, '10/20/2010 8:24:13 PM', 'o')

select t.UserID, t.CheckTime as checkIn,
(select top 1 case when t1.Checktype='o' then checktime else null end from @temp t1 where t1.UserID=t.UserID and t1.CheckTime > t.CheckTime order by CheckTime) as checkOut
from @temp t where Checktype='i' order by CheckTime

-- test result
UserID checkIn checkOut
----------- ----------------------- -----------------------
1 2010-10-18 14:39:08.000 2010-10-18 21:12:46.000
1 2010-10-19 08:56:21.000 NULL
1 2010-10-20 08:50:21.000 2010-10-20 20:24:13.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-26 : 04:16:22

remove the ORDER BY clause from your VIEW definition

KH
[spoiler]Time is always against us[/spoiler]

asif372
Posting Yak Master

100 Posts

Posted - 2011-02-26 : 14:36:26

Thanks A Lot It is Working 100% as My Need

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources