need help - 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

need help

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2013-07-28 : 04:55:14

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

549 Posts

Posted - 2013-07-28 : 11:20:46

[CODE]

-- 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;
[/CODE]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-28 : 16:18:07

[code]-- 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;[/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 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

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

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 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

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 MVP
http://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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources