CREATE TABLE [dbo].[multiple](
[emp_code] [varchar](20) NULL,
[swip_in] [nvarchar](255) NULL,
[swipe_in_date] [nvarchar](255) NULL,
[swipe_out] [nvarchar](255) NULL,
[Swipe_out_date] [nvarchar](255) NULL
) ON [PRIMARY]

insert into multiple values ('H10004','04:48:34','08/12/2015','12:00:42','08/12/2015')
insert into multiple values ('H10004','14:36:30','08/12/2015','18:50:16','08/12/2015')
insert into multiple values ('H10005','05:50:08','08/12/2015','11:50:58','08/12/2015')
insert into multiple values ('H10005','14:48:06','08/12/2015','18:52:34','08/12/2015')
insert into multiple values ('H10005','20:22:56','08/12/2015','22:56:10','08/12/2015')
insert into multiple values ('H10009','05:45:44','08/12/2015','11:53:46','08/12/2015')
insert into multiple values ('H10009','14:52:52','08/12/2015','18:55:04','08/12/2015')
insert into multiple values ('H10013','05:57:32','08/12/2015','12:00:52','08/12/2015')
insert into multiple values ('H10013','14:46:44','08/12/2015','18:48:02','08/12/2015')

i want result like below 

Empcode swip_in1,   swip_out1,    swip_in2,   swip_out2,   swip_in3,   swip_out3

Condition :

1. 24 hrs who are multiple time punched 

• Edited by Mohamed Udhuman 2 hours 21 minutes ago

/*if SQL Server 2012 Or Above*/ WITH Swipe AS ( SELECT emp_Code , CAST( [swipe_in_date] AS DATETIME)+ [swip_in] AS swipe_In , CAST( [Swipe_out_date] AS DATETIME)+ [swipe_out] AS swipe_Out ,ROW_NUMBER() OVER( PARTITION BY emp_Code ORDER BY CAST( [swipe_in_date] AS DATETIME)+ [swip_in] ASC ) AS Cnt_swipe_In FROM [multiple] ) , Base AS ( SELECT Swipe.emp_Code, Swipe.swipe_In Swipe_In_1, Swipe.swipe_Out Swipe_Out_1,Cnt_swipe_In, LEAD(swipe_In,1) OVER( PARTITION BY emp_Code ORDER BY swipe_In ASC ) Swipe_In_2 , LEAD(swipe_Out,1) OVER( PARTITION BY emp_Code ORDER BY swipe_In ASC ) Swipe_Out_2, LEAD(swipe_In,2) OVER( PARTITION BY emp_Code ORDER BY swipe_In ASC ) Swipe_In_3 , LEAD(swipe_Out,2) OVER( PARTITION BY emp_Code ORDER BY swipe_In ASC ) Swipe_Out_3 FROM Swipe ) SELECT emp_Code, Swipe_In_1, Swipe_Out_1,Swipe_In_2,Swipe_Out_2,Swipe_In_3,Swipe_Out_3 FROM Base WHERE Cnt_swipe_In =1 /*

Did you know that multiple is adjective and a noun? By definition it cannot be a table name. Did you know that a table has to have a key? Why are you using insanely long Chinese Unicode phrases for temporal data? (the entire Heart Sutra is shorter than 255 characters) Why did you split a timestamp into a DATE and TIME column? This is so bad that it has a name: attribute splitting. Why did you not use the ISO-8601 format? A code is not an identifier. 

What you want is a report and not query, so we do not do it SQL. 

CREATE TABLE TimeClock
(emp_id CHAR(6) NOT NULL,
 swipe_in_timestamp DATETIME2(0) NOT NULL,
PRIMARY KEY (emp_id, swipe_in_timestamp),
 swipe_out_timestamp DATETIME2(0) NOT NULL,
 CHECK (swipe_in_timestamp < swipe_out_timestamp)
); 
INSERT INTO TimeClock 
VALUES ('H10004','2015-08-12 04:48:34','2015-08-12 12:00:42')
 ('H10004','2015-08-12 05:50:08','2015-08-12 11:50:58'),
 ('H10005','2015-08-12 14:48:06','2015-08-12 18:52:34'),
 ('H10005','2015-08-12 20:22:56','2015-08-12 22:56:10'),
 ('H10009','2015-08-12 05:45:44','2015-08-12 11:53:46'),
 ('H10009','2015-08-12 14:52:52','2015-08-12 18:55:04'),
 ('H10013','2015-08-12 05:57:32','2015-08-12 12:00:52'),
 ('H10013','2015-08-12 14:46:44','2015-08-12 18:48:02');

Nothing here is right; was this a joke? 

Try this double pivot

DECLARE @multiple TABLE (emp_code VARCHAR(20) NULL, swip_in NVARCHAR(255) NULL, swipe_in_date NVARCHAR(255) NULL, swipe_out NVARCHAR(255) NULL, Swipe_out_date NVARCHAR(255) NULL)

insert into @multiple values ('H10004','04:48:34','08/12/2015','12:00:42','08/12/2015')
insert into @multiple values ('H10004','14:36:30','08/12/2015','18:50:16','08/12/2015')
insert into @multiple values ('H10005','05:50:08','08/12/2015','11:50:58','08/12/2015')
insert into @multiple values ('H10005','14:48:06','08/12/2015','18:52:34','08/12/2015')
insert into @multiple values ('H10005','20:22:56','08/12/2015','22:56:10','08/12/2015')
insert into @multiple values ('H10009','05:45:44','08/12/2015','11:53:46','08/12/2015')
insert into @multiple values ('H10009','14:52:52','08/12/2015','18:55:04','08/12/2015')
insert into @multiple values ('H10013','05:57:32','08/12/2015','12:00:52','08/12/2015')
insert into @multiple values ('H10013','14:46:44','08/12/2015','18:48:02','08/12/2015')

SELECT emp_code, swipe_in_date, MIN([in1]) AS in1, MIN([in2]) AS in2, MIN([in3]) AS in3, MIN([in4]) AS in4, MIN([out1]) AS in1, MIN([out2]) AS in2, MIN([out3]) AS in3, MIN([out4]) AS in4
  FROM (SELECT CAST(swipe_in_date + ' ' +swip_in AS DATETIME) AS swipe_in_dateTime, CAST(swipe_out_date + ' ' +swipe_out AS DATETIME) AS swipe_out_dateTime, *, 'in'+CAST(ROW_NUMBER() OVER (PARTITION BY emp_code ORDER BY swipe_in_date, swip_in) AS VARCHAR)AS inseq, 'out'+CAST(ROW_NUMBER() OVER (PARTITION BY emp_code ORDER BY swipe_in_date, swip_in) AS VARCHAR) AS outseq FROM @multiple) a
    PIVOT (
	       MIN(swipe_in_dateTime) FOR inseq IN ([in1],[in2],[in3],[in4])
		 ) p1
    PIVOT (
	       MIN(swipe_out_dateTime) FOR outseq IN ([out1],[out2],[out3],[out4])
		 ) p2
 GROUP BY emp_code, swipe_in_date

Dear CelKo,

This is my user requirement , yes i agree your points and i am not experience in script , i am a SQL DBA and i have Microsoft SQL family so if i want any script i put here my family they provided lots of script so i will take that script and test from my side. 

Yes i forget to put the condition, after your comments only i am realize. but i am not joking this required by my users. 

My Condition is without 24 hrs who are multiple time punched that records only i want. here my friends are giving nice scripts so i am testing 

Thanks for your comments Mr. CELKO. 

• Edited by Mohamed Udhuman 2 hours 30 minutes ago