SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 need help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnasz
Posting Yak Master

101 Posts

Posted - 07/28/2013 :  04:55:14  Show Profile  Reply with Quote
shift 1

Date:20/07/2013

13:00-04:30 (Same Day)

Shift 2

Date: 20/07/2013 - 21/07/2013

21:00-01:30


EmpNo Date Time

1 20/07/2013 13:00
1 20/07/2013 16:30
1 20/07/2013 09:00
1 21/07/2013 01:00

how to i bring the value of table above to


empNo Date time1 time2
1 20/07/2013 13:00 16:30
1 20/07/2013 09:00 01:00

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/28/2013 :  11:20:46  Show Profile  Reply with Quote


-- TEST DATA
DECLARE @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;
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/28/2013 :  16:18:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- TEST DATA
DECLARE @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;

-- SwePeso
WITH  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 Time2
FROM		cteSource
GROUP BY	EmpNo,
		rn / 2;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

mp2_admin
Starting Member

10 Posts

Posted - 11/12/2013 :  11:13:10  Show Profile  Reply with Quote
Hi, question how would you add a column name for EquipNo? I tried but data get corrupted..

My example: added EquipNo added

-- TEST DATA
DECLARE @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;


-- SwePeso
WITH  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 Time2
FROM		cteSource
GROUP BY	EquipNo, EmpNo, 
		rn / 2;




quote:
Originally posted by SwePeso

-- TEST DATA
DECLARE @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;

-- SwePeso
WITH  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 Time2
FROM		cteSource
GROUP BY	EmpNo,
		rn / 2;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/12/2013 :  12:44:33  Show Profile  Reply with Quote

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 Time2
FROM		cteSource
GROUP BY	EquipNo, EmpNo, 
		rn / 2;


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mp2_admin
Starting Member

10 Posts

Posted - 11/12/2013 :  13:37:40  Show Profile  Reply with Quote
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 Time2
FROM		cteSource
GROUP BY	EquipNo, EmpNo, 
		rn / 2;


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

mp2_admin
Starting Member

10 Posts

Posted - 12/05/2013 :  11:28:40  Show Profile  Reply with Quote
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 Again

WITH 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 Time2
FROM cteSource
GROUP BY tagname, value,
rn / 2
order by Time1;

Expected outcome--
Machine name emp Date time1 time2
machine1 27894 12/05/2013 07:41:17 07:42:32
machine1 27894 12/05/2013 07:42:32 06:44:59
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/05/2013 :  11:52:48  Show Profile  Reply with Quote
do you mean next tim1 for same tagname and value combination?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mp2_admin
Starting Member

10 Posts

Posted - 12/05/2013 :  12:18:08  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Edited by - mp2_admin on 12/05/2013 12:22:36
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000