Row in Middle of two Rows - 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

General SQL Server Forums

New to SQL Server Programming

Row in Middle of two Rows

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2011-03-01 : 13:40:50

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-03 : 15:51:23

Add 10 more rows of data (in DML format)

THEN Tell us which is the "First" row you need to compare to the "second" row

And post some table DDL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

asif372
Posting Yak Master

100 Posts

Posted - 2011-03-04 : 14:51:36

Basically I am Working On Attendance System for a Firm it has two Branches i want to display time spend in traveling from one branch to another
My Data Is Like This
(Id)(Date) (TimeIn)(TimeOut)(Spend)(Branch)
(1) (2010-10-20)(09:50) (12:50) (3:00)(B1)
(1) (2010-10-20)(14:00) (18:00) (4:00)(B2)
(1) (2010-11-20)(08:50) (17:50) (9:00)(B1)
(1) (2010-12-20)(08:40) (18:40) (10:00)(B1)
(1) (2010-13-20)(09:10) (10:10) (1:00)(B1)
(1) (2010-13-20)(12:10) (18:00) (6:00)(B2)

I Want To Display A Row In Middle Of two Rows if any Employee on same date Go from One Branch To another Branch Difference between TImeOut of first Row and TimeIn Of Second Row should be Calculated and displayed in middle of these Rows as TravellingTime
Just Like This
order by Date and TimeIn

(Id)(Date) (TimeIn)(TimeOut)(Spend)(Branch)
(1) (2010-10-20)(09:50) (12:50) (3:00)(B1)
(1) ----------------------------(1:10)(Traveling Time)
(1) (2010-10-20)(14:00) (18:00) (4:00)(B2)
(1) (2010-11-20)(08:50) (17:50) (9:00)(B1)
(1) (2010-12-20)(08:40) (18:40) (10:00)(B1)
(1) (2010-13-20)(09:10) (10:10) (1:00)(B1)
(1) ----------------------------(2:00)(Traveling Time)
(1) (2010-13-20)(12:10) (18:00) (6:00)(B2)

any Ideas?????
Thanks In Advance

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-03-05 : 00:32:01

I think this should be handled in the application ... but if you want to do it in db, try this to see whether it works for you.

declare @temp table(i int, d datetime, tin datetime, tout datetime, s varchar(5), b varchar(2))
insert into @temp values(1,'2010-10-20','9:50','12:50','3:00','B1')
insert into @temp values(1,'2010-10-20','14:00','18:00','4:00','B2')
insert into @temp values(1,'2010-11-20','8:50','17:50','9:00','B1')
insert into @temp values(1,'2010-12-20','8:40','18:40','10:00','B1')
insert into @temp values(1,'2011-1-20','9:10','10:10','1:00','B1')
insert into @temp values(1,'2011-1-20','12:10','18:00','6:00','B2')

;with temp as
(select *, rn=ROW_NUMBER() over(partition by i, d order by i, d, b), rnn = ROW_NUMBER() over(order by i, d, b) from @temp)

select i,convert(varchar(10),d,103) as d,convert(varchar(5),tin,108) as tin,convert(varchar(5),tout,108) as tout,s,b,travelTime from
(
select t1.i,null as d,null as tin,null as tout,null as s,null as b, rnn,
(select convert(varchar(5),(t1.tin - t.tout),108) from temp t where t.i=t1.i and t.d=t1.d and t.rn=t1.rn-1) as travelTime
from temp t1 where rn=2
union all
select i,d,tin,tout,s,b,rnn, null from temp
) as test order by i,rnn,d

-- result


i           d          tin   tout  s     b    travelTime
----------- ---------- ----- ----- ----- ---- ----------
1           20/10/2010 09:50 12:50 3:00  B1   NULL
1           NULL       NULL  NULL  NULL  NULL 01:10
1           20/10/2010 14:00 18:00 4:00  B2   NULL
1           20/11/2010 08:50 17:50 9:00  B1   NULL
1           20/12/2010 08:40 18:40 10:00 B1   NULL
1           20/01/2011 09:10 10:10 1:00  B1   NULL
1           NULL       NULL  NULL  NULL  NULL 02:00
1           20/01/2011 12:10 18:00 6:00  B2   NULL

asif372
Posting Yak Master

100 Posts

Posted - 2011-03-05 : 14:40:37

I am Using SQL 2000 the Exception is Occuring 'ROW_NUMBER' is not a recognized function name.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-05 : 15:15:37

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. Here is my guess from your narrative:

Your “spend” was a computed value; we don't like to store them in a table. Your request is wrong; tables are made of rows which have no ordering (middle? No such concept!) and all have the same format. We do not display in SQL; front ends do display not queries. Try this, with keys and constraints:

CREATE TABLE TimeClock
(emp_id INTEGER NOT NULL,
work_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
clock_in_time TIME NOT NULL,
clock_out_time TIME, –- null is important
CHECK (clock_in_time < clock_out_time),
branch_id CHAR(2) NOT NULL
CHECK (branch_id IN ('B0', 'B1', 'B2'),
activity_code CHAR(10) DEFAULT 'work' NOT NULL
CHECK (activity_code IN ('work', 'travel'));

INSERT INTO TimeClock
VALUES (1, '2010-10-20', '09:50:00', '12:50:00', 'B1', 'work'),
(1, '2010-10-20', '14:00:00', '18:00:00', 'B2', 'work');

Proper DDL makes DML easy. The 'B0' branch is a dummy value for travel.

>> I want to create a row between these rows if any employee on same date go from one branch to another branch difference between timeout of first row and timein of second row should be calculated and displayed in middle of these rows as travel time <<

INSERT INTO TimeClock
WITH Seq_TimeClock
AS
(SELECT emp_id, work_date, clock_in_time, clock_out_time,
branch_id, activity_code,
ROW_NUMBER()
OVER (PARTITION BY emp_id, work_date
ORDER BY clock_in_time, clock_out_time)
AS activity_seq
SELECT T1.emp_id, T1.work_date,
T1.clock_out_time, T2.clock_in_time,
'B0', 'travel'
FROM Seq_TimeClock AS T1,
Seq_TimeClock AS T2
WHERE T1.emp_id = T2.emp_id
AND T1.work_date = T2.work_date
AND T1.clock_out_time < T2.clock_in_time
AND T2.activity_seq = T1.activity_seq +1;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-03-05 : 19:19:30

For sql 2000, try this ...

declare @temp table(i int, d datetime, tin datetime, tout datetime, s varchar(5), b varchar(2))
insert into @temp values(1,'2010-10-20','9:50','12:50','3:00','B1')
insert into @temp values(1,'2010-10-20','14:00','18:00','4:00','B2')
insert into @temp values(1,'2010-11-20','8:50','17:50','9:00','B1')
insert into @temp values(1,'2010-12-20','8:40','18:40','10:00','B1')
insert into @temp values(1,'2011-1-20','9:10','10:10','1:00','B1')
insert into @temp values(1,'2011-1-20','12:10','18:00','6:00','B2')

select
i,
case when tout is null then null else convert(varchar(10),d,103)end as date,
case when tout is null then null else convert(varchar(5),tin,108)end as timeInt,
case when tout is null then null else convert(varchar(5),tout,108)end as timeOut,
s,b,travelTime
from
(
select t.*, null travelTime from @temp t
union all
select t1.i,d,tin,null as tout,null as s,null as b,
(select convert(varchar(5),(t1.tin - t.tout),108) from @temp t where t.i=t1.i and t.d=t1.d and t.b='B1' and t1.b='B2') as travelTime
from @temp t1 where b = 'B2'
) test
order by i,d,tin,b

--result


i           date       timeInt timeOut s     b    travelTime
----------- ---------- ------- ------- ----- ---- ----------
1           20/10/2010 09:50   12:50   3:00  B1   NULL
1           NULL       NULL    NULL    NULL  NULL 01:10
1           20/10/2010 14:00   18:00   4:00  B2   NULL
1           20/11/2010 08:50   17:50   9:00  B1   NULL
1           20/12/2010 08:40   18:40   10:00 B1   NULL
1           20/01/2011 09:10   10:10   1:00  B1   NULL
1           NULL       NULL    NULL    NULL  NULL 02:00
1           20/01/2011 12:10   18:00   6:00  B2   NULL

asif372
Posting Yak Master

100 Posts

Posted - 2011-03-06 : 09:48:25

Thanks For Your Quick Responce it is Working Fine But Not as per my need

Basically I am Working On Attendance System for a Firm it has two Branches i want to display time spend in traveling from one branch to another
My Data Is Like This
(Id)(Date) (TimeIn)(TimeOut)(Spend)(Branch)
(1) (2010-10-20)(09:50) (12:50) (3:00)(B1)
(1) (2010-10-20)(14:00) (18:00) (4:00)(B2)
(1) (2010-11-20)(08:50) (17:50) (9:00)(B1)
(1) (2010-12-20)(08:40) (18:40) (10:00)(B1)
(1) (2010-13-20)(09:10) (10:10) (1:00)(B1)
(1) (2010-13-20)(12:10) (18:00) (6:00)(B2)
(1) (2010-13-20)(19:00) (20:00) (1:00)(B1)

I Want To Display A Row In Middle Of two Rows if any Employee on same date Go from One Branch To another Branch Difference between TImeOut of first Row and TimeIn Of Second Row should be Calculated and displayed in middle of these Rows as TravellingTime
Just Like This
order by Date and TimeIn

(Id)(Date) (TimeIn)(TimeOut)(Spend)(Branch)
(1) (2010-10-20)(09:50) (12:50) (3:00)(B1)
(1) (2010-10-20)-----------------(1:10)(Traveling Time)
(1) (2010-10-20)(14:00) (18:00) (4:00)(B2)
(1) (2010-11-20)(08:50) (17:50) (9:00)(B1)
(1) (2010-12-20)(08:40) (18:40) (10:00)(B1)
(1) (2010-13-20)(09:10) (10:10) (1:00)(B1)
(1) (2010-13-20)----------------(2:00)(Traveling Time)
(1) (2010-13-20)(12:10) (18:00) (6:00)(B2)
(1) (2010-13-20)----------------(1:00)(Traveling Time)
(1) (2010-13-20)(19:00) (20:00) (1:00)(B1)

any Ideas?????
Thanks In Advance

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-03-06 : 15:46:58

declare @temp table(i int, d datetime, tin datetime, tout datetime, s varchar(5), b varchar(2))
insert into @temp values(1,'2010-10-20','9:50','12:50','3:00','B1')
insert into @temp values(1,'2010-10-20','14:00','18:00','4:00','B2')
insert into @temp values(1,'2010-11-20','8:50','17:50','9:00','B1')
insert into @temp values(1,'2010-12-20','8:40','18:40','10:00','B1')
insert into @temp values(1,'2011-1-20','9:10','10:10','1:00','B1')
insert into @temp values(1,'2011-1-20','12:10','18:00','6:00','B2')
insert into @temp values(1,'2011-1-20','19:00','20:00','1:00','B1')
declare @ttt table(i int, d datetime, tin datetime, tout datetime, s varchar(5), b varchar(2), rn int)
insert into @ttt select *, (select COUNT(*) from @temp t where t.i=t1.i and t.d=t1.d and t1.tin>t.tin) as rn from @temp t1

select
i,
convert(varchar(10),d,103) as date,
case when tout is null then null else convert(varchar(5),tin,108)end as timeIn,
case when tout is null then null else convert(varchar(5),tout,108)end as timeOut,
s,b,travelTime
from
(
select t.*, null travelTime from @ttt t
union
select t1.i,d,tin,null as tout,null as s,null as b, rn,
(select convert(varchar(5),(t1.tin - t.tout),108) from @ttt t where t.i=t1.i and t.d=t1.d and t.rn=t1.rn-1 and t.b<>t1.b) as travelTime from @ttt t1
) test
where b is not null or travelTime is not null
order by i,d,tin,travelTime desc


i           date       timeIn timeOut s     b    travelTime
----------- ---------- ------ ------- ----- ---- ----------
1           20/10/2010 09:50  12:50   3:00  B1   NULL
1           20/10/2010 NULL   NULL    NULL  NULL 01:10
1           20/10/2010 14:00  18:00   4:00  B2   NULL
1           20/11/2010 08:50  17:50   9:00  B1   NULL
1           20/12/2010 08:40  18:40   10:00 B1   NULL
1           20/01/2011 09:10  10:10   1:00  B1   NULL
1           20/01/2011 NULL   NULL    NULL  NULL 02:00
1           20/01/2011 12:10  18:00   6:00  B2   NULL
1           20/01/2011 NULL   NULL    NULL  NULL 01:00
1           20/01/2011 19:00  20:00   1:00  B1   NULL

asif372
Posting Yak Master

100 Posts

Posted - 2011-03-08 : 03:53:50

thanks for your Reply it works fine but
Using Your Logic i have Implemented it to my structure but Exception is coming that rn is not a valid column name
my script is like this

declare @ttt table ("Employee Code" int, Date datetime, "Time In" datetime, "Time Out" datetime, Spend varchar(50), Branch varchar(50), rn int)
insert into @ttt select "Employee Code","Date","Time In","Time Out",Spend,Branch, (select COUNT(*) from HFAllAttendance t where t."Employee Code"=t1."Employee Code" and t.Date=t1.Date and t1."Time In">t."Time In") as rn from HFAllAttendance t1

select
"Employee Code",
convert(varchar(10),Date,103) as date,
case when "Time Out" is null then null else convert(varchar(5),"Time In",108)end as "Time In",
case when "Time Out" is null then null else convert(varchar(5),"Time Out",108)end as "Time Out",
Spend,Branch,TravelTime
from
(
select t."Employee Code",t."Date",t."Time In",t."Time Out",t.Spend,t.Branch, null travelTime from HFAllAttendance t
union
select t1."Employee Code",Date,"Time In",null as "Time Out",null as Spend,null as Branch,rn,
(select convert(varchar(5),(t1."Time In" - t."Time Out"),108) from HFAllAttendance t where t."Employee Code"=t1."Employee Code" and t.Date=t1.Date and t.rn=t1.rn-1 and t.Branch<>t1.Branch) as travelTime from HFAllAttendance t1
) test
where Branch is not null or travelTime is not null
order by "Employee Code",Date,"Time In",travelTime desc

any idea????

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-08 : 04:18:05

Try:

quote:
Originally posted by asif372

thanks for your Reply it works fine but
Using Your Logic i have Implemented it to my structure but Exception is coming that rn is not a valid column name
my script is like this

declare @ttt table ("Employee Code" int, Date datetime, "Time In" datetime, "Time Out" datetime, Spend varchar(50), Branch varchar(50), rn int)
insert into @ttt select "Employee Code","Date","Time In","Time Out",Spend,Branch, (select COUNT(*) from HFAllAttendance t where t."Employee Code"=t1."Employee Code" and t.Date=t1.Date and t1."Time In">t."Time In") as rn from HFAllAttendance t1

select
"Employee Code",
convert(varchar(10),Date,103) as date,
case when "Time Out" is null then null else convert(varchar(5),"Time In",108)end as "Time In",
case when "Time Out" is null then null else convert(varchar(5),"Time Out",108)end as "Time Out",
Spend,Branch,TravelTime
from
(
select t."Employee Code",t."Date",t."Time In",t."Time Out",t.Spend,t.Branch, null travelTime from HFAllAttendance t
union
select t1."Employee Code",Date,"Time In",null as "Time Out",null as Spend,null as Branch,rn,
(select convert(varchar(5),(t1."Time In" - t."Time Out"),108) from ~~HFAllAttendance~~ @ttt t where t."Employee Code"=t1."Employee Code" and t.Date=t1.Date and t.rn=t1.rn-1 and t.Branch<>t1.Branch) as travelTime from ~~HFAllAttendance~~ @ttt t1
) test
where Branch is not null or travelTime is not null
order by "Employee Code",Date,"Time In",travelTime desc

any idea????

You are already inserting rows from table HFAllAttendance in @ttt.
So again HFAllAttendance is not required.
Note: I have not carried out any testing;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources