Combining multiple rows of data from the Source table into just one single row of data with additional columns in the Destination table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Combining multiple rows of data from the...

Combining multiple rows of data from the Source table into just one single row of data with additional columns in the Destination table

Rate Topic

Display Mode

Topic Options

Author

Message

sql1411

Posted Monday, September 17, 2012 2:32 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:41 PM
Points: 44, Visits: 77

Below are some of the sample SQL scripts and the given scenarios.

USE [db_test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON

--Create SourceTable
GO

CREATE TABLE FlightInfo_source (
[DepartureDate] [datetime] NOT NULL,
[FlightNumber] [int] NOT NULL,
[ScheduledTail] [nvarchar](6) NULL,
[ActualDateOfDeparture] [datetime] NULL,
[LastModifiedDate] [nvarchar](10) NULL,
[LastModifiedtime] [nvarchar](4) NULL,
[TakeOutTime] [nvarchar](4) NULL,
[TakeOffTime] [nvarchar](4) NULL,
[LandOnTime] [nvarchar](4) NULL,
[TakeInTime] [nvarchar](4) NULL,
[ETALocal] [nvarchar](4) NULL,
[ETDLocal] [nvarchar](4) NULL,
[TurnBackIndicator] [char](1) NULL
) ON [PRIMARY]

GO

--Insert some data into Source table
GO
INSERT INTO dbo.FlightInfo_source
(DepartureDate,FlightNumber,ScheduledTail,ActualDateOfDeparture,LastModifiedDate,LastModifiedTime, TakeOutTime,TakeOffTime,LandOnTime,TakeInTime,ETALocal, ETDLocal, TurnBackIndicator)

SELECT '2012-09-06 00:00:00.000',633,932,'2012-08-17 00:00:00.000','07SEP12',1302,1415,NULL,NULL,1428,0820,0830,'Y'
UNION ALL
SELECT '2012-09-06 00:00:00.000',633,932,'2012-08-17 00:00:00.000','07SEP12',0600,1444,1459,1473,1748,0840,1048,'N'

GO

-- Select from the source table
SELECT * FROM dbo.FlightInfo_source

--Create Destination Table
GO

CREATE TABLE FlightInfo_Destination (
[DepartureDate] [datetime] NOT NULL,
[FlightNumber] [int] NOT NULL,
[ScheduledTail] [nvarchar](6) NULL,
[ActualTail] [nvarchar](6) NULL,
[ActualDateOfDeparture] [datetime] NULL,
[LastModifiedDate] [nvarchar](10) NULL,
[LastModifiedtime] [nvarchar](4) NULL,
[TakeOutTime] [nvarchar](4) NULL,
[TakeOffTime] [nvarchar](4) NULL,
[LandOnTime] [nvarchar](4) NULL,
[TakeInTime] [nvarchar](4) NULL,
[ETALocal] [nvarchar](4) NULL,
[ETDLocal] [nvarchar](4) NULL,
[TurnBackIndicator] [char](1) NULL,
[FlightTurnBackTail1][nvarchar](6) NULL,
[FlightTurnBackDateOut1] [datetime] NULL,
[FlightTurnBackTimeOut1] [nvarchar] (4) NULL,
[FlightTurnBackDateIn1] [datetime] NULL,
[FlightTurnBackTimeIn1] [nvarchar](4) NULL,
[FlightTurnBackTail2][nvarchar](6) NULL,
[FlightTurnBackDateOut2] [datetime] NULL,
[FlightTurnBackTimeOut2] [nvarchar] (4) NULL,
[FlightTurnBackDateIn2] [datetime] NULL,
[FlightTurnBackTimeIn2] [nvarchar](4) NULL

) ON [PRIMARY]

GO

--Insert some data manually into the destination table. This is however the desired result that I want to see in this table
GO
INSERT INTO dbo.FlightInfo_Destination
(DepartureDate, FlightNumber, ScheduledTail,ActualTail,ActualDateOfDeparture,LastModifiedDate,LastModifiedtime, TakeOutTime,TakeOffTime,LandOnTime,TakeInTime,ETALocal,ETDLocal,TurnBackIndicator, FlightTurnBackTail1,FlightTurnBackDateOut1,FlightTurnBackTimeOut1,FlightTurnBackDateIn1,FlightTurnBackTimeIn1,FlightTurnBackTail2,FlightTurnBackDateOut2,FlightTurnBackTimeOut2,FlightTurnBackDateIn2,FlightTurnBackTimeIn2)

SELECT '2012-09-06 00:00:00.000',633,932,932,'2012-09-06 00:00:00.000','07SEP12',1302,1444,1459,1743,1748,0840,1048,'Y',932,'2012-09-06 00:00:00.000',1415,'2012-09-06 00:00:00.000',1428,NULL,NULL,NULL,NULL,NULL

GO

SELECT * FROM dbo.FlightInfo_Destination

GO

--Now, here the logic is that we're taking the most recently modified rows based on LastModifiedDate column and LastModifiedTime column.
--However, the 1st row information from our source table should also be captured in our destination table because that particular flight was taken out and taken in. It never took off.
--In technical terms, TurnBackIndicator is 'Y' because TakeOutTime and TakeInTime are NOT NULL, and TakeOffTime and LandOnTime time are NULL.
--there can be multiple tail numbers for the given flightnumber and departure date, and they can be "taken out" and "taken in" multiple times.
--Each time it happens for the given flight number on given date, we've to record in the same row with new set of columns. Right now, I've added just two set of columns as 1 & 2 because we've only two distinct rows in our source table.
--Let me know if I need to clarify further.

Post #1360447

Sean Lange

Posted Monday, September 17, 2012 2:51 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 8,567, Visits: 8,218

Pretty sure you want a cross tab query for this. If at all possible this would be far more efficient in the front end. Take a look at the links in my signature about cross tabs.

FWIW, you really should be using the datetime datatype instead of varchar. Date math is so much easier when you don't have to figure out how to combine columns together to get it into a string representation that will cast to a datetime later.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1360458

laurie-789651

Posted Tuesday, September 18, 2012 3:52 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 274, Visits: 783

This does the job. I've included all code as I've added an extra row & corrected a time.


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

--========= DROP TABLES IF THEY EXIST ========================
IF OBJECT_ID('dbo.FlightInfo_source') IS NOT NULL
	DROP TABLE dbo.FlightInfo_source;
IF OBJECT_ID('dbo.FlightInfo_Destination') IS NOT NULL
	DROP TABLE dbo.FlightInfo_Destination;

--========= CREATE SOURCE TABLE ========================
CREATE TABLE FlightInfo_source (
 [DepartureDate] [datetime] NOT NULL,
 [FlightNumber] [int] NOT NULL,
 [ScheduledTail] [nvarchar](6) NULL,
 [ActualDateOfDeparture] [datetime] NULL,
 [LastModifiedDate] [nvarchar](10) NULL,
 [LastModifiedtime] [nvarchar](4) NULL,
 [TakeOutTime] [nvarchar](4) NULL,
 [TakeOffTime] [nvarchar](4) NULL,
 [LandOnTime] [nvarchar](4) NULL,
 [TakeInTime] [nvarchar](4) NULL,
 [ETALocal] [nvarchar](4) NULL,
 [ETDLocal] [nvarchar](4) NULL,
 [TurnBackIndicator] [char](1) NULL
) ON [PRIMARY]

GO

--========= INSERT SOURCE DATA ========================
-- I've added an extra turnback row.
INSERT INTO dbo.FlightInfo_source
 (DepartureDate,FlightNumber,ScheduledTail,ActualDateOfDeparture,LastModifiedDate,LastModifiedTime, TakeOutTime,TakeOffTime,LandOnTime,TakeInTime,ETALocal, ETDLocal, TurnBackIndicator)

SELECT '2012-09-06 00:00:00.000',633,932,'2012-08-17 00:00:00.000','07SEP12',1200,1315,NULL,NULL,1328,0720,0730,'Y'
 UNION ALL
SELECT '2012-09-06 00:00:00.000',633,932,'2012-08-17 00:00:00.000','07SEP12',1302,1415,NULL,NULL,1428,0820,0830,'Y'
 UNION ALL
SELECT '2012-09-06 00:00:00.000',633,932,'2012-08-17 00:00:00.000','07SEP12',0600,1444,1459,1743,1748,0840,1048,'N'

 GO

-- Select from the source table
SELECT * FROM dbo.FlightInfo_source

GO

--========= CREATE EXPECTED OUTPUT ========================
CREATE TABLE FlightInfo_Destination (
 [DepartureDate] [datetime] NOT NULL,
 [FlightNumber] [int] NOT NULL,
 [ScheduledTail] [nvarchar](6) NULL,
 [ActualTail] [nvarchar](6) NULL,
 [ActualDateOfDeparture] [datetime] NULL,
 [LastModifiedDate] [nvarchar](10) NULL,
 [LastModifiedtime] [nvarchar](4) NULL,
 [TakeOutTime] [nvarchar](4) NULL,
 [TakeOffTime] [nvarchar](4) NULL,
 [LandOnTime] [nvarchar](4) NULL,
 [TakeInTime] [nvarchar](4) NULL,
 [ETALocal] [nvarchar](4) NULL,
 [ETDLocal] [nvarchar](4) NULL,
 [TurnBackIndicator] [char](1) NULL,
 [FlightTurnBackTail1][nvarchar](6) NULL,
 [FlightTurnBackDateOut1] [datetime] NULL,
 [FlightTurnBackTimeOut1] [nvarchar] (4) NULL,
 [FlightTurnBackDateIn1] [datetime] NULL,
 [FlightTurnBackTimeIn1] [nvarchar](4) NULL,
 [FlightTurnBackTail2][nvarchar](6) NULL,
 [FlightTurnBackDateOut2] [datetime] NULL,
 [FlightTurnBackTimeOut2] [nvarchar] (4) NULL,
 [FlightTurnBackDateIn2] [datetime] NULL,
 [FlightTurnBackTimeIn2] [nvarchar](4) NULL
 
) ON [PRIMARY]

GO

--Insert some data manually into the destination table. This is however the desired result that I want to see in this table
GO
INSERT INTO dbo.FlightInfo_Destination 
(DepartureDate, FlightNumber, ScheduledTail,ActualTail,ActualDateOfDeparture,LastModifiedDate,LastModifiedtime, TakeOutTime,TakeOffTime,LandOnTime,TakeInTime,ETALocal,ETDLocal,TurnBackIndicator, FlightTurnBackTail1,FlightTurnBackDateOut1,FlightTurnBackTimeOut1,FlightTurnBackDateIn1,FlightTurnBackTimeIn1,FlightTurnBackTail2,FlightTurnBackDateOut2,FlightTurnBackTimeOut2,FlightTurnBackDateIn2,FlightTurnBackTimeIn2)

SELECT '2012-09-06 00:00:00.000',633,932,932,'2012-09-06 00:00:00.000','07SEP12',1302,1444,1459,1743,1748,0840,1048,'Y',932,'2012-09-06 00:00:00.000',1415,'2012-09-06 00:00:00.000',1428,NULL,NULL,NULL,NULL,NULL
 
GO
 
SELECT * FROM dbo.FlightInfo_Destination

 GO
 
--Now, here the logic is that we're taking the most recently modified rows based on LastModifiedDate 
-- column and LastModifiedTime column.
 --However, the 1st row information from our source table should also be captured in our destination table 
 -- because that particular flight was taken out and taken in. It never took off.
 --In technical terms, TurnBackIndicator is 'Y' because TakeOutTime and TakeInTime are NOT NULL, 
 -- and TakeOffTime and LandOnTime time are NULL.
 --there can be multiple tail numbers for the given flightnumber and departure date, 
 -- and they can be "taken out" and "taken in" multiple times. 
--Each time it happens for the given flight number on given date, we've to record in the same row 
-- with new set of columns. Right now, I've added just two set of columns as 1 & 2 
-- because we've only two distinct rows in our source table.
 --Let me know if I need to clarify further.


SELECT * FROM dbo.FlightInfo_Source -- Original source
SELECT * FROM dbo.FlightInfo_Destination -- Expected results

--========= SUGGESTED SOLUTION ========================
-- This should give you a good starting point at least...
-- You would need to add more columns as required, until you covered all the turn-backs.
-- (Also not sure about ActualDateOfDeparture?)
SELECT
	DepartureDate,
	FlightNumber,
	ScheduledTail = MAX(ScheduledTail),
	ActualTail = MAX(ScheduledTail), -- Not sure about this.
	ActualDateOfDeparture = MAX(ActualDateOfDeparture),
	LastModifiedDate = MAX(LastModifiedDate),
	LastModifiedTime = MAX(LastModifiedTime),
	TakeOutTime = MAX(CASE WHEN TurnBack=0 THEN TakeOutTime ELSE '' END),
	TakeOffTime = MAX(CASE WHEN TurnBack=0 THEN TakeOffTime ELSE '' END),
	LandOnTime = MAX(CASE WHEN TurnBack=0 THEN LandOnTime ELSE '' END),
	TakeInTime = MAX(CASE WHEN TurnBack=0 THEN TakeInTime ELSE '' END),
	ETALocal = MAX(CASE WHEN TurnBack=0 THEN ETALocal ELSE '' END),
	ETDLocal = MAX(CASE WHEN TurnBack=0 THEN ETDLocal ELSE '' END),
	TurnBackIndicator = MAX(TurnBackIndicator),
	FlightTurnBackTail1 = MAX(CASE WHEN TurnBack=1 THEN ScheduledTail ELSE NULL END),
	FlightTurnBackDateOut1 = MAX(CASE WHEN TurnBack=1 THEN DepartureDate ELSE NULL END),
	FlightTurnBackTimeOut1 = MAX(CASE WHEN TurnBack=1 THEN TakeOutTime ELSE NULL END),
	FlightTurnBackDateIn1 = MAX(CASE WHEN TurnBack=1 THEN DepartureDate ELSE NULL END),
	FlightTurnBackTimeIn1 = MAX(CASE WHEN TurnBack=1 THEN TakeInTime ELSE NULL END),
	FlightTurnBackTail2 = MAX(CASE WHEN TurnBack=2 THEN ScheduledTail ELSE NULL END),
	FlightTurnBackDateOut2 = MAX(CASE WHEN TurnBack=2 THEN DepartureDate ELSE NULL END),
	FlightTurnBackTimeOut2 = MAX(CASE WHEN TurnBack=2 THEN TakeOutTime ELSE NULL END),
	FlightTurnBackDateIn2 = MAX(CASE WHEN TurnBack=2 THEN DepartureDate ELSE NULL END),
	FlightTurnBackTimeIn2 = MAX(CASE WHEN TurnBack=2 THEN TakeInTime ELSE NULL END),
	FlightTurnBackTail3 = MAX(CASE WHEN TurnBack=3 THEN ScheduledTail ELSE NULL END),
	FlightTurnBackDateOut3 = MAX(CASE WHEN TurnBack=3 THEN DepartureDate ELSE NULL END),
	FlightTurnBackTimeOut3 = MAX(CASE WHEN TurnBack=3 THEN TakeOutTime ELSE NULL END),
	FlightTurnBackDateIn3 = MAX(CASE WHEN TurnBack=3 THEN DepartureDate ELSE NULL END),
	FlightTurnBackTimeIn3 = MAX(CASE WHEN TurnBack=3 THEN TakeInTime ELSE NULL END)
-- INTO dbo.FlightInfo_Destination
FROM
(
	-- Add a sequence number to the Turn Backs:
	SELECT *,
		TurnBack = CASE
					WHEN TurnBackIndicator='Y'
					THEN ROW_NUMBER() OVER (PARTITION BY DepartureDate, FlightNumber ORDER BY TakeOutTime) 
					ELSE 0
					END
	FROM 
	(
		-- Reformat LastModifiedDate.  You're still going to have problems with LastModifiedDate though, as Sean said.
		SELECT
			DepartureDate,
			FlightNumber,
			ScheduledTail,
			ActualDateOfDeparture,
			LastModifiedDate,
			LastModifiedTime = CASE WHEN LEN(LastModifiedTime) = 3 THEN '0' + LastModifiedTime ELSE LastModifiedTime END,
			TakeOutTime,
			TakeOffTime,
			LandOnTime,
			TakeInTime,
			ETALocal,
			ETDLocal,
			TurnBackIndicator
		FROM dbo.FlightInfo_Source
	) X
) Base
GROUP BY DepartureDate, FlightNumber;

Post #1360655

sql1411

Posted Tuesday, September 18, 2012 10:52 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:41 PM
Points: 44, Visits: 77

Thanks for both of your responses. I agree with both of you that having a varchar datatype for the datetime field is going to give us trouble. However, changing that datatype in the tables is beyond my authority at this point of time.

Anyway, the sample query from Laurie seems to be working for this particular example. I'll try to apply this with few other samples and let you know if I face any issues of if the requirement changes.

Thanks once again.

Post #1360896

sql1411

Posted Thursday, September 20, 2012 11:03 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:41 PM
Points: 44, Visits: 77

Hi Laurie,

Your query worked fine with the original samples. Now, the requirements has been changed and they want to add new columns in the destination table. From past three days, I've been trying to play around with your logic for our new requirements but unable to get it worked Sad

Right now, I'm not even sure whatever they're asking is possible. However, I've reposted all of my sample queries again here.
Please take a look at it and let me know if you can help

Thanks in advance

USE [db_test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON

--Create SourceTable
GO

CREATE TABLE FlightInfo_source (
[DepartureDate] [datetime] NOT NULL,
[FlightNumber] [int] NOT NULL,
[ScheduledTail] [nvarchar](6) NULL,
[ActualDateOfDeparture] [datetime] NULL,
[LastModifiedDateTime] [datetime]NULL,
[Origin][nvarchar](4)NULL,
[Destination][nvarchar](4)NULL,
[TakeOutTime] [nvarchar](4) NULL,
[TakeOffTime] [nvarchar](4) NULL,
[LandOnTime] [nvarchar](4) NULL,
[TakeInTime] [nvarchar](4) NULL,
[CancelledFlightIndicator][char](1)NULL,
[GroundTurnBackIndicator] [char](1) NULL,
[AirTurnBackIndicator][char](1)NULL
) ON [PRIMARY]

GO

--Insert some data into Source table
GO
INSERT INTO dbo.FlightInfo_source
(DepartureDate,FlightNumber,ScheduledTail,ActualDateOfDeparture,LastModifiedDateTime,Origin,Destination,TakeOutTime,TakeOffTime,LandOnTime,TakeInTime,CancelledFlightIndicator,GroundTurnBackIndicator,AirTurnBackIndicator)

SELECT '2012-09-06 00:00:00.000',631,8933,'2012-09-07 00:00:00.000','2012-09-07 13:21:00.000','MSN','DEN',NULL,NULL,NULL,NULL,'Y',NULL,NULL
UNION ALL
SELECT '2012-09-06 00:00:00.000',631, 933, '2012-09-06 00:00:00.000','2012-09-07 13:01:00.000','MSN','MSN',1421,NULL,NULL,1434,NULL,'Y',NULL
UNION ALL
SELECT '2012-09-06 00:00:00.000',631, 933, '2012-09-06 00:00:00.000','2012-09-07 13:01:00.000','MSN','MSN',1513,NULL,NULL,1434,NULL,'Y',NULL
UNION ALL
SELECT '2012-09-06 00:00:00.000',631, 933, '2012-09-06 00:00:00.000','2012-09-07 13:10:00.000','MSN','MSN',1400,1430,1450,1510,NULL,NULL,'Y'
GO
-- Select from the source table
SELECT * FROM dbo.FlightInfo_source

--Create Destination Table
GO

CREATE TABLE FlightInfo_Destination (
[DepartureDate] [datetime] NOT NULL,
[FlightNumber] [int] NOT NULL,
[ScheduledTail] [nvarchar](6) NULL,
[ActualTail] [nvarchar](6) NULL,
[ActualDateOfDeparture] [datetime] NULL,
[LastModifiedDatetime] [datetime] NULL,
[Origin][nvarchar](4)NULL,
[Destination][nvarchar](4)NULL,
[CancelledFlightIndicator][char](1)NULL,
--GroundTurnBackColumns
[GroundTurnBackIndicator] [char](1) NULL,
--for first GroundTurnBack
[GroundTurnBackTail1][nvarchar](6) NULL,
[GroundTurnBackDateOut1] [datetime] NULL,
[GroundTurnBackTimeOut1] [nvarchar] (4) NULL,
[GroundTurnBackDateIn1] [datetime] NULL,
[GroundTurnBackTimeIn1] [nvarchar](4) NULL,
--for second GroundTurnBack
[GroundTurnBackTail2][nvarchar](6) NULL,
[GroundTurnBackDateOut2] [datetime] NULL,
[GroundTurnBackTimeOut2] [nvarchar] (4) NULL,
[GroundTurnBackDateIn2] [datetime] NULL,
[GroundTurnBackTimeIn2] [nvarchar](4) NULL,
--AirturnBackColumns
[AirTurnBackIndicator][char](1)NULL,
--for first AirTrunBack
[AirTurnBackTail1][nvarchar](6) NULL,
[AirTurnBackDateOut1][datetime]NULL,
[AirTurnBackTimeOut1][nvarchar] (4) NULL,
[AirTurnBackDateOff1][datetime]NULL,
[AirTurnBackTimeOff1][nvarchar] (4) NULL,
[AirTurnBackDateOn1][datetime]NULL,
[AirTurnBackTimeOn1][nvarchar] (4) NULL,
[AirTurnBackDateIn1][datetime]NULL,
[AirTurnBackTimeIn1][nvarchar] (4) NULL,
--for second AirTurnBack
[AirTurnBackTail2][nvarchar](6) NULL,
[AirTurnBackDateOut2][datetime]NULL,
[AirTurnBackTimeOut2][nvarchar] (4) NULL,
[AirTurnBackDateOff2][datetime]NULL,
[AirTurnBackTimeOff2][nvarchar] (4) NULL,
[AirTurnBackDateOn2][datetime]NULL,
[AirTurnBackTimeOn2][nvarchar] (4) NULL,
[AirTurnBackDateIn2][datetime]NULL,
[AirTurnBackTimeIn2][nvarchar] (4) NULL,
) ON [PRIMARY]

GO

--Insert some data manually into the destination table. This is, however, the desired result that I want to see in this destination table
GO
INSERT INTO dbo.FlightInfo_Destination
(
DepartureDate, FlightNumber, ScheduledTail,ActualTail,ActualDateOfDeparture,LastModifiedDateTime
,CancelledFlightIndicator,GroundTurnBackIndicator
,GroundTurnBackTail1,GroundTurnBackDateOut1,GroundTurnBackTimeOut1,GroundTurnBackDateIn1,GroundTurnBackTimeIn1
,GroundTurnBackTail2,GroundTurnBackDateOut2,GroundTurnBackTimeOut2,GroundTurnBackDateIn2,GroundTurnBackTimeIn2
,AirTurnBackIndicator
,AirTurnBackTail1,AirTurnBackDateOut1,AirTurnBackTimeOut1,AirTurnBackDateOff1,AirTurnBackTimeOff1,AirTurnBackDateOn1,AirTurnBackTimeOn1,AirTurnBackDateIn1,AirTurnBackTimeIn1
,AirTurnBackTail2,AirTurnBackDateOut2,AirTurnBackTimeOut2,AirTurnBackDateOff2,AirTurnBackTimeOff2,AirTurnBackDateOn2,AirTurnBackTimeOn2,AirTurnBackDateIn2,AirTurnBackTimeIn2
)

SELECT '2012-09-06 00:00:00.000',631,933,NULL,'2012-09-06 00:00:00.000','2012-09-07 13:01:00.000'
,'Y','Y'
,933,'2012-09-06 00:00:00.000',1421,'2012-09-06 00:00:00.000',1434
,933,'2012-09-06 00:00:00.000',1513,'2012-09-06 00:00:00.000',1530
,'Y'
,933,'2012-09-06 00:00:00.000',1400,'2012-09-06 00:00:00.000',1430,'2012-09-06 00:00:00.000',1450,'2012-09-06 00:00:00.000',1510
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

GO

SELECT * FROM dbo.FlightInfo_Destination;

--Now here the logic is that we're taking the most recently modified rows based on "LastModifiedDateTime" column.
--There can be multiple tail numbers for a particular FlightNumber
--However, all other rows of information from our source table should also be captured in our destination table because that particular flight has ben cancelled once, has the GroundTurnBackIndicator twice, and AirturnBackIndicator once.
-- GroundTurnBackIndicator = 'Y'
When Origin = Destination,TakeOutTime IS NOT NULL, TakeOffTime IS NULL, LandOnTime IS NULL,TakeInTime IS NOT NULL

-- AirTurnBackIndicator = 'Y'
WHEN Origin = Destination,TakeOutTime IS NOT NULL, TakeOffTime IS NOT NULL, LandOnTime IS NOT NULL,TakeInTime IS NOT NULL

--Each time it happens for the given flight number on given date, we've to record it in the same row with new set of columns. Right now, I've added just two set of columns as 1 & 2 (for both GroundTurnBack and AirturnBack groups).
--Let me know if I need to clarify further.

Post #1362173

laurie-789651

Posted Monday, September 24, 2012 5:53 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 274, Visits: 783

This is quite similar to the other one - the main change is the section for allocating sequences to the ground- & air- turnbacks.

Again you will need to add/delete columns as required, & I've got a couple of data differences from the supplied output.

Other than that - hope it helps!


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

--========= DROP TABLES IF THEY EXIST ========================
IF OBJECT_ID('dbo.FlightInfo_source') IS NOT NULL
	DROP TABLE dbo.FlightInfo_source;
IF OBJECT_ID('dbo.FlightInfo_Destination') IS NOT NULL
	DROP TABLE dbo.FlightInfo_Destination;

--========= CREATE SOURCE TABLE ========================
CREATE TABLE FlightInfo_source (
 [DepartureDate] [datetime] NOT NULL,
 [FlightNumber] [int] NOT NULL,
 [ScheduledTail] [nvarchar](6) NULL,
 [ActualDateOfDeparture] [datetime] NULL,
 [LastModifiedDateTime] [datetime]NULL,
 [Origin][nvarchar](4)NULL,
 [Destination][nvarchar](4)NULL,
 [TakeOutTime] [nvarchar](4) NULL,
 [TakeOffTime] [nvarchar](4) NULL,
 [LandOnTime] [nvarchar](4) NULL,
 [TakeInTime] [nvarchar](4) NULL,
 [CancelledFlightIndicator][char](1)NULL,
 [GroundTurnBackIndicator] [char](1) NULL,
 [AirTurnBackIndicator][char](1)NULL
) ON [PRIMARY]
GO

--========= INSERT SOURCE DATA ========================
-- Corrected 'TakeInTime' on record 3 to 1530.
INSERT INTO dbo.FlightInfo_source
 (DepartureDate,FlightNumber,ScheduledTail,ActualDateOfDeparture,LastModifiedDateTime,Origin,Destination,TakeOutTime,TakeOffTime,LandOnTime,TakeInTime,CancelledFlightIndicator,GroundTurnBackIndicator,AirTurnBackIndicator)
SELECT '2012-09-06 00:00:00.000',631,8933,'2012-09-07 00:00:00.000','2012-09-07 13:21:00.000','MSN','DEN',NULL,NULL,NULL,NULL,'Y',NULL,NULL
 UNION ALL
SELECT '2012-09-06 00:00:00.000',631, 933, '2012-09-06 00:00:00.000','2012-09-07 13:01:00.000','MSN','MSN',1421,NULL,NULL,1434,NULL,'Y',NULL
 UNION ALL
SELECT '2012-09-06 00:00:00.000',631, 933, '2012-09-06 00:00:00.000','2012-09-07 13:01:00.000','MSN','MSN',1513,NULL,NULL,1530,NULL,'Y',NULL
 UNION ALL
SELECT '2012-09-06 00:00:00.000',631, 933, '2012-09-06 00:00:00.000','2012-09-07 13:10:00.000','MSN','MSN',1400,1430,1450,1510,NULL,NULL,'Y'
GO

-- Select from the source table
SELECT * FROM dbo.FlightInfo_source
GO

--========= CREATE EXPECTED OUTPUT ========================
CREATE TABLE FlightInfo_Destination (
 [DepartureDate] [datetime] NOT NULL,
 [FlightNumber] [int] NOT NULL,
 [ScheduledTail] [nvarchar](6) NULL,
 [ActualTail] [nvarchar](6) NULL,
 [ActualDateOfDeparture] [datetime] NULL,
 [LastModifiedDatetime] [datetime] NULL,
 [Origin][nvarchar](4)NULL,
 [Destination][nvarchar](4)NULL,
 [CancelledFlightIndicator][char](1)NULL,
 --GroundTurnBackColumns
 [GroundTurnBackIndicator] [char](1) NULL,
 --for first GroundTurnBack
 [GroundTurnBackTail1][nvarchar](6) NULL,
 [GroundTurnBackDateOut1] [datetime] NULL,
 [GroundTurnBackTimeOut1] [nvarchar] (4) NULL,
 [GroundTurnBackDateIn1] [datetime] NULL,
 [GroundTurnBackTimeIn1] [nvarchar](4) NULL,
 --for second GroundTurnBack
 [GroundTurnBackTail2][nvarchar](6) NULL,
 [GroundTurnBackDateOut2] [datetime] NULL,
 [GroundTurnBackTimeOut2] [nvarchar] (4) NULL,
 [GroundTurnBackDateIn2] [datetime] NULL,
 [GroundTurnBackTimeIn2] [nvarchar](4) NULL,
 --AirturnBackColumns
 [AirTurnBackIndicator][char](1)NULL,
 --for first AirTrunBack
 [AirTurnBackTail1][nvarchar](6) NULL,
 [AirTurnBackDateOut1][datetime]NULL,
 [AirTurnBackTimeOut1][nvarchar] (4) NULL,
 [AirTurnBackDateOff1][datetime]NULL,
 [AirTurnBackTimeOff1][nvarchar] (4) NULL,
 [AirTurnBackDateOn1][datetime]NULL,
 [AirTurnBackTimeOn1][nvarchar] (4) NULL,
 [AirTurnBackDateIn1][datetime]NULL,
 [AirTurnBackTimeIn1][nvarchar] (4) NULL,
 --for second AirTurnBack
 [AirTurnBackTail2][nvarchar](6) NULL,
 [AirTurnBackDateOut2][datetime]NULL,
 [AirTurnBackTimeOut2][nvarchar] (4) NULL,
 [AirTurnBackDateOff2][datetime]NULL,
 [AirTurnBackTimeOff2][nvarchar] (4) NULL,
 [AirTurnBackDateOn2][datetime]NULL,
 [AirTurnBackTimeOn2][nvarchar] (4) NULL,
 [AirTurnBackDateIn2][datetime]NULL,
 [AirTurnBackTimeIn2][nvarchar] (4) NULL,
) ON [PRIMARY]
GO

--Insert some data manually into the destination table. This is however the desired result that I want to see in this table
INSERT INTO dbo.FlightInfo_Destination 
(
 DepartureDate, FlightNumber, ScheduledTail,ActualTail,ActualDateOfDeparture,LastModifiedDateTime
 ,CancelledFlightIndicator,GroundTurnBackIndicator 
,GroundTurnBackTail1,GroundTurnBackDateOut1,GroundTurnBackTimeOut1,GroundTurnBackDateIn1,GroundTurnBackTimeIn1
 ,GroundTurnBackTail2,GroundTurnBackDateOut2,GroundTurnBackTimeOut2,GroundTurnBackDateIn2,GroundTurnBackTimeIn2
 ,AirTurnBackIndicator
 ,AirTurnBackTail1,AirTurnBackDateOut1,AirTurnBackTimeOut1,AirTurnBackDateOff1,AirTurnBackTimeOff1,AirTurnBackDateOn1,AirTurnBackTimeOn1,AirTurnBackDateIn1,AirTurnBackTimeIn1
 ,AirTurnBackTail2,AirTurnBackDateOut2,AirTurnBackTimeOut2,AirTurnBackDateOff2,AirTurnBackTimeOff2,AirTurnBackDateOn2,AirTurnBackTimeOn2,AirTurnBackDateIn2,AirTurnBackTimeIn2
 )
 SELECT '2012-09-06 00:00:00.000',631,933,NULL,'2012-09-06 00:00:00.000','2012-09-07 13:01:00.000'
 ,'Y','Y'
 ,933,'2012-09-06 00:00:00.000',1421,'2012-09-06 00:00:00.000',1434
 ,933,'2012-09-06 00:00:00.000',1513,'2012-09-06 00:00:00.000',1530
 ,'Y'
 ,933,'2012-09-06 00:00:00.000',1400,'2012-09-06 00:00:00.000',1430,'2012-09-06 00:00:00.000',1450,'2012-09-06 00:00:00.000',1510
 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
GO
 
SELECT * FROM dbo.FlightInfo_Destination
GO

--Now here the logic is that we're taking the most recently modified rows based on "LastModifiedDateTime" 
-- column.
 --There can be multiple tail numbers for a particular FlightNumber 
--However, all other rows of information from our source table should also be captured in our destination 
--   table because that particular flight has ben cancelled once, has the GroundTurnBackIndicator twice, 
--   and AirturnBackIndicator once. 
-- GroundTurnBackIndicator = 'Y'
 When Origin = Destination,TakeOutTime IS NOT NULL, TakeOffTime IS NULL, LandOnTime IS NULL,TakeInTime IS NOT NULL

 -- AirTurnBackIndicator = 'Y'
 WHEN Origin = Destination,TakeOutTime IS NOT NULL, TakeOffTime IS NOT NULL, LandOnTime IS NOT NULL,TakeInTime IS NOT NULL
 
--Each time it happens for the given flight number on given date, we've to record it in the same row 
--   with new set of columns. Right now, I've added just two set of columns as 1 & 2 
--   (for both GroundTurnBack and AirturnBack groups).
 --Let me know if I need to clarify further. 

SELECT * FROM dbo.FlightInfo_Source -- Original source
SELECT * FROM dbo.FlightInfo_Destination -- Expected results

--========= SUGGESTED SOLUTION ========================
-- This should give you a good starting point at least...
-- You would need to add more columns as required, until you covered all the turn-backs.
-- I've left NULL where you had NULL.  My ActualDateOfDeparture is different. 
SELECT
	DepartureDate,
	FlightNumber,
	ScheduledTail = MAX(ScheduledTail),
	ActualTail = MAX(ScheduledTail), -- Not sure about this.
	ActualDateOfDeparture = MAX(ActualDateOfDeparture),
	LastModifiedDateTime = MAX(LastModifiedDateTime),
	Origin = NULL,
	Destination = NULL,
	CancelledFlightIndicator = MAX(CancelledFlightIndicator),
	--TakeOutTime = MAX(CASE WHEN (GroundTurnBack=0 AND AirTurnBack=0) THEN TakeOutTime ELSE '' END),
	--TakeOffTime = MAX(CASE WHEN (GroundTurnBack=0 AND AirTurnBack=0) THEN TakeOffTime ELSE '' END),
	--LandOnTime = MAX(CASE WHEN (GroundTurnBack=0 AND AirTurnBack=0) THEN LandOnTime ELSE '' END),
	--TakeInTime = MAX(CASE WHEN (GroundTurnBack=0 AND AirTurnBack=0) THEN TakeInTime ELSE '' END),
	--ETALocal = MAX(CASE WHEN (GroundTurnBack=0 AND AirTurnBack=0) THEN ETALocal ELSE '' END),
	--ETDLocal = MAX(CASE WHEN (GroundTurnBack=0 AND AirTurnBack=0) THEN ETDLocal ELSE '' END),
	GroundTurnBackIndicator = MAX(GroundTurnBackIndicator),
	GroundTurnBackTail1 = MAX(CASE WHEN GroundTurnBack=1 THEN ScheduledTail ELSE NULL END),
	GroundTurnBackDateOut1 = MAX(CASE WHEN GroundTurnBack=1 THEN DepartureDate ELSE NULL END),
	GroundTurnBackTimeOut1 = MAX(CASE WHEN GroundTurnBack=1 THEN TakeOutTime ELSE NULL END),
	GroundTurnBackDateIn1 = MAX(CASE WHEN GroundTurnBack=1 THEN DepartureDate ELSE NULL END),
	GroundTurnBackTimeIn1 = MAX(CASE WHEN GroundTurnBack=1 THEN TakeInTime ELSE NULL END),
	GroundTurnBackTail2 = MAX(CASE WHEN GroundTurnBack=2 THEN ScheduledTail ELSE NULL END),
	GroundTurnBackDateOut2 = MAX(CASE WHEN GroundTurnBack=2 THEN DepartureDate ELSE NULL END),
	GroundTurnBackTimeOut2 = MAX(CASE WHEN GroundTurnBack=2 THEN TakeOutTime ELSE NULL END),
	GroundTurnBackDateIn2 = MAX(CASE WHEN GroundTurnBack=2 THEN DepartureDate ELSE NULL END),
	GroundTurnBackTimeIn2 = MAX(CASE WHEN GroundTurnBack=2 THEN TakeInTime ELSE NULL END),
	GroundTurnBackTail3 = MAX(CASE WHEN GroundTurnBack=3 THEN ScheduledTail ELSE NULL END),
	GroundTurnBackDateOut3 = MAX(CASE WHEN GroundTurnBack=3 THEN DepartureDate ELSE NULL END),
	GroundTurnBackTimeOut3 = MAX(CASE WHEN GroundTurnBack=3 THEN TakeOutTime ELSE NULL END),
	GroundTurnBackDateIn3 = MAX(CASE WHEN GroundTurnBack=3 THEN DepartureDate ELSE NULL END),
	GroundTurnBackTimeIn3 = MAX(CASE WHEN GroundTurnBack=3 THEN TakeInTime ELSE NULL END),

	AirTurnBackIndicator = MAX(AirTurnBackIndicator),
	AirTurnBackTail1 = MAX(CASE WHEN AirTurnBack=1 THEN ScheduledTail ELSE NULL END),
	AirTurnBackDateOut1 = MAX(CASE WHEN AirTurnBack=1 THEN DepartureDate ELSE NULL END),
	AirTurnBackTimeOut1 = MAX(CASE WHEN AirTurnBack=1 THEN TakeOutTime ELSE NULL END),
	AirTurnBackDateOff1 = MAX(CASE WHEN AirTurnBack=1 THEN DepartureDate ELSE NULL END),
	AirTurnBackTimeOff1 = MAX(CASE WHEN AirTurnBack=1 THEN TakeOffTime ELSE NULL END),
	AirTurnBackDateOn1 = MAX(CASE WHEN AirTurnBack=1 THEN DepartureDate ELSE NULL END),
	AirTurnBackTimeOn1 = MAX(CASE WHEN AirTurnBack=1 THEN LandOnTime ELSE NULL END),
	AirTurnBackDateIn1 = MAX(CASE WHEN AirTurnBack=1 THEN DepartureDate ELSE NULL END),
	AirTurnBackTimeIn1 = MAX(CASE WHEN AirTurnBack=1 THEN TakeInTime ELSE NULL END),
	AirTurnBackTail2 = MAX(CASE WHEN AirTurnBack=2 THEN ScheduledTail ELSE NULL END),
	AirTurnBackDateOut2 = MAX(CASE WHEN AirTurnBack=2 THEN DepartureDate ELSE NULL END),
	AirTurnBackTimeOut2 = MAX(CASE WHEN AirTurnBack=2 THEN TakeOutTime ELSE NULL END),
	AirTurnBackDateOff2 = MAX(CASE WHEN AirTurnBack=2 THEN DepartureDate ELSE NULL END),
	AirTurnBackTimeOff2 = MAX(CASE WHEN AirTurnBack=2 THEN TakeOffTime ELSE NULL END),
	AirTurnBackDateOn2 = MAX(CASE WHEN AirTurnBack=2 THEN DepartureDate ELSE NULL END),
	AirTurnBackTimeOn2 = MAX(CASE WHEN AirTurnBack=2 THEN LandOnTime ELSE NULL END),
	AirTurnBackDateIn2 = MAX(CASE WHEN AirTurnBack=2 THEN DepartureDate ELSE NULL END),
	AirTurnBackTimeIn2 = MAX(CASE WHEN AirTurnBack=2 THEN TakeInTime ELSE NULL END)
-- INTO dbo.FlightInfo_Destination
FROM
(
	-- Add a sequence number to the Turn Backs:
	SELECT *,
		GroundTurnBack = row_number() OVER ( PARTITION BY FlightNumber ORDER BY TakeOutTime ),
		AirTurnBack = 0
	FROM dbo.FlightInfo_Source
	WHERE GroundTurnbackIndicator='Y'
	UNION
	SELECT *,
		GroundTurnBack = 0,
		AirTurnBack = row_number() OVER ( PARTITION BY FlightNumber ORDER BY TakeOutTime )
	FROM dbo.FlightInfo_Source
	WHERE AirTurnbackIndicator='Y'
	UNION
	SELECT *,
		GroundTurnBack = 0,
		AirTurnBack = 0
	FROM dbo.FlightInfo_Source
	WHERE (GroundTurnbackIndicator='N' OR GroundTurnbackIndicator IS NULL)
		and (AirTurnbackIndicator='N' OR AirTurnbackIndicator IS NULL)
) Base
GROUP BY DepartureDate, FlightNumber;

Post #1363433

« Prev Topic | Next Topic »

Permissions