Click here to Skip to main content
15,067,539 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I am having the table like this

SQL
insert into attn (StaffNo, ADate, ATime)  values ('12345',   '12-02-2013',   09.10)
insert into attn (StaffNo, ADate, ATime)  values ('12345',   '12-02-2013',   10.03)
insert into attn (StaffNo, ADate, ATime) values ('12345',    '12-02-2013',   10.40)
insert into attn (StaffNo, ADate, ATime)  values ('12345',   '12-02-2013',   17.30)

I want in this format

OutPut :
         StaffNo	       ADate                    InTime             OutTime <br />
<br />
          12333,              12-02-2013          09.10              10.03<br />
          12333,              12-02-2013          10.40              17.30
Posted
Updated 21-Feb-13 18:48pm
v2
Comments
joshrduncan2012 21-Feb-13 9:36am
   
What is your question? Where are you stuck? What have you done to accomplish this so far?
Sandeep Mewara 21-Feb-13 10:21am
   
And what have you tried to get this result? Any effort?
Rajesh Anuhya 22-Feb-13 0:49am
   
Not Clear..
--RA

Hi go through the following code block

SQL
DECLARE @attn TABLE (StaffNo INT, ADate VARCHAR(20), ATime VARCHAR(20));
 
INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 09.10);
INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 10.03);
INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 10.40);
INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 17.30);

WITH tmp AS (
SELECT ROW_NUMBER() OVER(ORDER BY StaffNo,ADate) AS RowNum,* FROM @attn
)

select tmp1.StaffNo,tmp1.ADate,tmp1.ATime InTime,tmp2.ATime OutTime 
FROM tmp as tmp1,tmp as tmp2 WHERE tmp1.RowNum = tmp2.RowNum - 1 
AND tmp2.RowNum % 2 = 0 AND tmp1.StaffNo = tmp2.StaffNo


Thank you.
   
v2
Here you go!

SQL
DECLARE @attn TABLE (StaffNo INT, ADate VARCHAR(20), ATime VARCHAR(20))

INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 09.10)
INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 10.03)
INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 10.40)
INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 17.30)


SELECT StaffNo,
       ADate,
       ATime
FROM   (
           SELECT ROW_NUMBER() OVER(ORDER BY StaffNo) AS RowNum,*
           FROM   @attn
       ) AS t
WHERE  t.RowNum % 2 = 0
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900