September 17, 2012 at 2:32 pm
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.
September 17, 2012 at 2:51 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2012 at 3:52 am
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;
September 18, 2012 at 10:52 am
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.
September 20, 2012 at 11:03 am
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 🙁
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.
September 24, 2012 at 5:53 am
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;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply