Author |
Topic |
pnasz
Posting Yak Master
101 Posts |
Posted - 2013-07-28 : 04:55:14
|
shift 1Date:20/07/201313:00-04:30 (Same Day)Shift 2Date: 20/07/2013 - 21/07/201321:00-01:30EmpNo Date Time1 20/07/2013 13:001 20/07/2013 16:30 1 20/07/2013 09:001 21/07/2013 01:00how to i bring the value of table above to empNo Date time1 time21 20/07/2013 13:00 16:301 20/07/2013 09:00 01:00 |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-28 : 11:20:46
|
[CODE]-- TEST DATADECLARE @Temp TABLE(EmpNo INT, [Date] DATE, [Time] Time);INSERT INTO @Temp VALUES(1, '2013-07-20', '13:00'),(1, '2013-07-20', '16:30'),(1, '2013-07-20', '09:00'),(1, '2013-07-21', '01:00');-- QUERY; WITH CTE AS(SELECT *, ROW_NUMBER() OVER(PARTITION BY EmpNo ORDER BY [DATE], [TIME]) as RN FROM @Temp)SELECT T1.EmpNo, T1.[DATE], T1.[Time] as Time1, T2.[Time] as Time2 FROM CTE T1 LEFT JOIN CTE T2 ON T1.RN = T2.RN-1 and T1.RN%2 = 1 WHERE T2.[Time] IS NOT NULL;[/CODE] |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-28 : 16:18:07
|
[code]-- TEST DATADECLARE @Temp TABLE(pkCol int primary key clustered, EmpNo INT, [Date] DATE, [Time] Time(0));INSERT INTO @Temp VALUES(45, 1, '2013-07-20', '13:00'),(78, 1, '2013-07-20', '16:30'),(84, 1, '2013-07-20', '09:00'),(99, 1, '2013-07-21', '01:00');-- QUERY; WITH CTE AS(SELECT *, ROW_NUMBER() OVER(PARTITION BY EmpNo ORDER BY pkcol) as RN FROM @Temp)SELECT T1.EmpNo, T1.[DATE], T1.[Time] as Time1, T2.[Time] as Time2 FROM CTE T1 LEFT JOIN CTE T2 ON T1.RN = T2.RN-1 and T1.RN%2 = 1 WHERE T2.[Time] IS NOT NULL;-- SwePesoWITH cteSource(EmpNo, [Date], [Time], rn)AS ( SELECT EmpNo, [Date], [Time], ROW_NUMBER() OVER (PARTITION BY EmpNo ORDER BY pkCol) - 1 AS rn FROM @Temp)SELECT EmpNo, MAX(CASE WHEN rn % 2 = 0 THEN [Date] ELSE NULL END) AS [Date], MAX(CASE WHEN rn % 2 = 0 THEN [Time] ELSE NULL END) AS Time1, MAX(CASE WHEN rn % 2 = 1 THEN [Time] ELSE NULL END) AS Time2FROM cteSourceGROUP BY EmpNo, rn / 2;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
mp2_admin
Starting Member
10 Posts |
Posted - 2013-11-12 : 11:13:10
|
Hi, question how would you add a column name for EquipNo? I tried but data get corrupted.. My example: added EquipNo added-- TEST DATADECLARE @Temp TABLE(pkCol int primary key clustered, EquipNo INT, EmpNo INT, [Date] DATE, [Time] Time(0));INSERT INTO @Temp VALUES(10, '0884', 1713, '2013-07-20', '07:45'),(11, '0884', 1713, '2013-07-20', '08:30'),(12, '0885', 1713, '2013-07-20', '08:45'),(13, '0885', 1713, '2013-07-20', '11:30'),(14, '0888', 1713, '2013-07-20', '09:45'),(134, '0889', 1713, '2013-07-20', '10:45'),(135, '0889', 1713, '2013-07-20', '10:46');-- QUERY; WITH CTE AS(SELECT *, ROW_NUMBER() OVER(PARTITION BY EmpNo ORDER BY pkcol) as RN FROM @Temp)SELECT T1.[EmpNo], T1.[DATE], T1.[Time] as Time1, T2.[Time] as Time2 FROM CTE T1 LEFT JOIN CTE T2 ON T1.RN = T2.RN-1 and T1.RN%2 = 1 WHERE T2.[Time] IS NOT NULL;-- SwePesoWITH cteSource(EquipNo, EmpNo, [Date], [Time], rn)AS ( SELECT EquipNo, EmpNo, [Date], [Time], ROW_NUMBER() OVER (PARTITION BY EmpNo ORDER BY pkCol) - 1 AS rn FROM @Temp)SELECT EquipNo, EmpNo, MAX(CASE WHEN rn % 2 = 0 THEN [Date] ELSE NULL END) AS [Date], MAX(CASE WHEN rn % 2 = 0 THEN [Time] ELSE NULL END) AS Time1, MAX(CASE WHEN rn % 2 = 1 THEN [Time] ELSE NULL END) AS Time2FROM cteSourceGROUP BY EquipNo, EmpNo, rn / 2; quote: Originally posted by SwePeso
-- TEST DATADECLARE @Temp TABLE(pkCol int primary key clustered, EmpNo INT, [Date] DATE, [Time] Time(0));INSERT INTO @Temp VALUES(45, 1, '2013-07-20', '13:00'),(78, 1, '2013-07-20', '16:30'),(84, 1, '2013-07-20', '09:00'),(99, 1, '2013-07-21', '01:00');-- QUERY; WITH CTE AS(SELECT *, ROW_NUMBER() OVER(PARTITION BY EmpNo ORDER BY pkcol) as RN FROM @Temp)SELECT T1.EmpNo, T1.[DATE], T1.[Time] as Time1, T2.[Time] as Time2 FROM CTE T1 LEFT JOIN CTE T2 ON T1.RN = T2.RN-1 and T1.RN%2 = 1 WHERE T2.[Time] IS NOT NULL;-- SwePesoWITH cteSource(EmpNo, [Date], [Time], rn)AS ( SELECT EmpNo, [Date], [Time], ROW_NUMBER() OVER (PARTITION BY EmpNo ORDER BY pkCol) - 1 AS rn FROM @Temp)SELECT EmpNo, MAX(CASE WHEN rn % 2 = 0 THEN [Date] ELSE NULL END) AS [Date], MAX(CASE WHEN rn % 2 = 0 THEN [Time] ELSE NULL END) AS Time1, MAX(CASE WHEN rn % 2 = 1 THEN [Time] ELSE NULL END) AS Time2FROM cteSourceGROUP BY EmpNo, rn / 2; Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-12 : 12:44:33
|
[code]WITH cteSource(EquipNo, EmpNo, [Date], [Time], rn)AS ( SELECT EquipNo, EmpNo, [Date], [Time], ROW_NUMBER() OVER (PARTITION BY EquipNo,EmpNo ORDER BY pkCol) - 1 AS rn FROM @Temp)SELECT EquipNo, EmpNo, MAX(CASE WHEN rn % 2 = 0 THEN [Date] ELSE NULL END) AS [Date], MAX(CASE WHEN rn % 2 = 0 THEN [Time] ELSE NULL END) AS Time1, MAX(CASE WHEN rn % 2 = 1 THEN [Time] ELSE NULL END) AS Time2FROM cteSourceGROUP BY EquipNo, EmpNo, rn / 2;[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
mp2_admin
Starting Member
10 Posts |
Posted - 2013-11-12 : 13:37:40
|
Cool I will have to digest my data.Awesome!quote: Originally posted by visakh16
WITH cteSource(EquipNo, EmpNo, [Date], [Time], rn)AS ( SELECT EquipNo, EmpNo, [Date], [Time], ROW_NUMBER() OVER (PARTITION BY EquipNo,EmpNo ORDER BY pkCol) - 1 AS rn FROM @Temp)SELECT EquipNo, EmpNo, MAX(CASE WHEN rn % 2 = 0 THEN [Date] ELSE NULL END) AS [Date], MAX(CASE WHEN rn % 2 = 0 THEN [Time] ELSE NULL END) AS Time1, MAX(CASE WHEN rn % 2 = 1 THEN [Time] ELSE NULL END) AS Time2FROM cteSourceGROUP BY EquipNo, EmpNo, rn / 2; ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
mp2_admin
Starting Member
10 Posts |
Posted - 2013-12-05 : 11:28:40
|
Hi, thank you for the assist with the qry. I have had time review my data and have one question. when I see a null value in time2 is it possible to retrieve the next records time1 and use that inplace of the null?Thank You AgainWITH cteSource(tagname, value, [Date], [Time], rn)AS ( SELECT TagName, Value, convert(char(10),DateTime,101) as date, convert(char(10),DateTime,108) as time, ROW_NUMBER() OVER (PARTITION BY TagName,value ORDER BY tagname) - 1 AS rn FROM StringHistory where TagName like '%machine1%' and LEN(value) >= 4 and DateTime >= '12/05/2013 03:30:00' and DateTime <= GETDATE())SELECT tagname, value, MAX(CASE WHEN rn % 2 = 0 THEN [Date] ELSE NULL END) AS [Date], MAX(CASE WHEN rn % 2 = 0 THEN [Time] ELSE NULL END) AS Time1, MAX(CASE WHEN rn % 2 = 1 THEN [Time] ELSE NULL END) AS Time2FROM cteSourceGROUP BY tagname, value, rn / 2 order by Time1;Expected outcome--Machine name emp Date time1 time2machine1 27894 12/05/2013 07:41:17 07:42:32machine1 27894 12/05/2013 07:42:32 06:44:59 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-05 : 11:52:48
|
do you mean next tim1 for same tagname and value combination?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
mp2_admin
Starting Member
10 Posts |
Posted - 2013-12-05 : 12:18:08
|
Yes, sorry should have been more clear. Also how would I insert into table from query or not possible?quote: Originally posted by visakh16 do you mean next tim1 for same tagname and value combination?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
|
|
|