Sql order structure?

[mp2admin]

Hi.. I am try to take the following data and order the structure as seen below. But do not know enough SQL to accomplish this. Can someone lead me in the right direction? If I can supply more information I can try.

Thank You

Header datetime Value
capack4_Dat 11/1/2013 23:52:47 27720
capack3_Dat 11/2/2013 1:15:20 27720
capack4_Dat 11/2/2013 2:45:23 27720
capack3_Dat 11/2/2013 4:15:16 27720
capack4_Dat 11/2/2013 5:45:06 27720
capack3_Dat 11/2/2013 7:15:07 27720
capack3_Dat 11/2/2013 7:34:52 27720


and have the output like below..

Machine Start End Value
capack4_Dat 11/1/2013 23:52:47 Invalid 27720
capack3_Dat 11/2/2013 1:15:20 Invalid 27720
capack4_Dat 11/2/2013 2:45:23 Invalid 27720
capack3_Dat 11/2/2013 4:15:16 Invalid 27720
capack4_Dat 11/2/2013 5:45:06 Invalid 27720
capack3_Dat 11/2/2013 7:15:07 11/2/2013 7:34:52 27720

 

[SkipVought]

hi,

Would you care to explain in detail the logic of how you get from one to the other.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[yelworcm]

I am guessing you will want to be looking up the LEAD or LAG functions ordering on the datetime column.

 

[DaveInIowa]

If you're not on SQL Server 2012, you could create a function. I'd also avoid using keywords (datetime, End, Value, etc.) for column names.

CREATE FUNCTION dbo.YourFunction
(
)
RETURNS 
@ResultTable TABLE 
(
	Machine VARCHAR(50),
	[Start] DATETIME,
	[End] DATETIME,
	[Value] VARCHAR(50)
)
AS
BEGIN
	
	DECLARE @Header VARCHAR(50)
	DECLARE @datetime DATETIME
	DECLARE @Value VARCHAR(50)
	
	DECLARE @Machine VARCHAR(50)
	DECLARE @StartedOn DATETIME
	DECLARE @FirstValue VARCHAR(50)
	
	DECLARE myCursor CURSOR FAST_FORWARD FOR
	SELECT Header, [datetime], [Value]
	  FROM YourTable
	 ORDER BY [datetime]
	
	OPEN myCursor
	FETCH NEXT FROM myCursor INTO @Header, @datetime, @Value
	WHILE (@@FETCH_STATUS = 0) BEGIN
				
		IF (@Machine IS NULL OR @Machine != @Header) BEGIN -- Capture 1st of 2 rows
		
			-- Produce an invalid row if a second machine was found before its companion row
			IF (@Machine != @Header) INSERT INTO @ResultTable VALUES (@Machine, @StartedOn, NULL, 'Invalid ' + @FirstValue)
		
			SET @Machine = @Header
			SET @StartedOn = @datetime
			SET @FirstValue = @Value
		END
		ELSE BEGIN -- The previous row's companion row was found
			INSERT INTO	@ResultTable VALUES (@Machine, @StartedOn, @datetime, @FirstValue)
			SET @Machine = NULL -- Prepare for new set of data
		END
	
		FETCH NEXT FROM myCursor INTO @Header, @datetime, @Value
		
		IF (@@FETCH_STATUS != 0 AND @Machine IS NOT NULL) INSERT INTO @ResultTable VALUES (@Machine, @StartedOn, NULL, 'Invalid ' + @FirstValue)	

	END
	CLOSE myCursor;
	DEALLOCATE myCursor
	
	RETURN
	
END