August 30, 2012 at 11:21 am
Please take a look at the below details and let me know if anyone can help. I'm trying to write a simple stored proc to load some data from the source to destination table. Howeverr, there are some new columns in the destination table and some adding logics to be placed while loading the data.
Please let me know if I need to clarify more to get this done.
Below are the sample queries.
USE [db_ABC]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Create Source Table
CREATE TABLE [dbo].[DailySchedule](
[FlightDate] [datetime] NULL,
[FlightNumber] [int] NULL,
[ScheduleOrder] [nvarchar](2) NULL,
[AircraftNumber] [nvarchar](6) NULL,
[LastDateModified] [nvarchar](8) NULL,
[LastTimeModified] [nvarchar](8) NULL
) ON [PRIMARY]
GO
--Insert some data into source table
GO
INSERT INTO dbo.DailySchedule
(FlightDate, FlightNumber, ScheduleOrder, AircraftNumber,LastDateModified,LastTimeModified)
SELECT '2012-08-26 00:00:00.000',5555,1,225,'26AUG12', 1628
UNION ALL
SELECT '2012-08-26 00:00:00.000',5555,1,230,'27AUG12', 1718
UNION ALL
SELECT '2012-08-26 00:00:00.000',5555,2,586,'27AUG12', 2203
GO
--Select all the fields from the Source table. We should get three rows
SELECT * FROM dbo.DailySchedule
--Now create the Destination table
GO
CREATE TABLE [dbo].[DailySchedule_Destination](
[FlightDate] [datetime] NULL,
[FlightNumber] [int] NULL,
[ScheduleOrder] [nvarchar](2) NULL,
[OriginalAircraftNumber] [nvarchar](6) NULL,
[FinalAircraftNumber] [nvarchar](6) NULL,
[ChangeOfAircraftIndicator] [char](1) NULL
) ON [PRIMARY]
GO
-- Inserting some data manually in the destination table. However, this should be the desired result that has to be inserted from the source table
GO
INSERT INTO dbo.DailySchedule_Destination
(FlightDate, FlightNumber, ScheduleOrder, OriginalAircraftNumber,FinalAircraftNumber,ChangeOfAircraftIndicator)
SELECT '2012-08-26 00:00:00.000',5555,1,225,230,'Y'
UNION ALL
SELECT '2012-08-26 00:00:00.000',5555,2,586,586,'N'
GO
--Desired result set in the destination table
SELECT * FROM dbo.DailySchedule_Destination
-- the logic while iniserting the data in the destination table is ....
--(1) we need to do grouping on FlightDate, FlightNumber, and ScheduleOrder.
-- (2) In our destination table, if the FinalAircraftNumber is different from the OriginalAircraftNumber
--then the ChangeOfAircraftindicator should be 'Y' or else 'N'
August 30, 2012 at 12:58 pm
nice job posting ddl and sample data in a readily consumable format. It make a lot of difference for those of us willing to work on your issue. I think I understand what you are after but I have a couple questions.
How do you know which row is "first"? Consider this query.
SELECT * FROM dbo.DailySchedule where ScheduleOrder = 1
You have two rows here and one of them is the OriginalAircraftNumber and one becomes the FinalAircraftNumber. What can you use to sort these so you know which one is which. Also, how do you want to handle situations where there are 3 rows with a ScheduleOrder of 1?
I would strongly urge you to discontinue the use of varchar fields for portions of your datetime information. LastDateModified and LastTimeModified should be combined into a single column with a datatype of datetime. This varchar implementation is going to cause you nothing but agony as you continue to wrestle with constant conversions to get it to a usable datatype.
_______________________________________________________________
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/
August 30, 2012 at 1:14 pm
I came out with two queries but I'm sure someone can get something better.
The first one works with only one change (2 rows per schedule), the second one will work for any number of changes but will compare only the first and the last.
WITH CTE AS(
SELECT FlightDate,
FlightNumber,
ScheduleOrder,
AircraftNumber,
ROW_NUMBER() OVER( PARTITION BY FlightNumber, scheduleOrder ORDER BY CAST( LastDateModified + ' '+ STUFF( LastTimeModified,3,0,':') AS datetime) DESC) RN
FROM #DailySchedule
)
SELECT a.FlightDate,
a.FlightNumber,
a.ScheduleOrder,
ISNULL(b.AircraftNumber,a.AircraftNumber)OriginalAircraftNumber,
a.AircraftNumberFinalAircraftNumber,
CASE WHEN b.AircraftNumber IS NULL THEN 'N' ELSE 'Y' END ChangeOfAircraftIndicator
FROM CTE a
LEFT
JOIN CTE b ON a.FlightNumber = b.FlightNumber
AND a.ScheduleOrder = b.ScheduleOrder
AND a.RN = b.RN - 1
WHERE a.RN = 1
ORDER BY a.RN;
WITH CTE AS(
SELECT FlightDate,
FlightNumber,
ScheduleOrder,
AircraftNumber,
ROW_NUMBER() OVER( PARTITION BY FlightNumber, scheduleOrder ORDER BY CAST( LastDateModified + ' '+ STUFF( LastTimeModified,3,0,':') AS datetime)) RN
FROM #DailySchedule
),
CTE2 AS(
SELECT FlightDate,
FlightNumber,
ScheduleOrder,
MIN(RN) First_Row,
MAX(RN) Last_Row
FROM CTE
GROUP BY FlightDate,
FlightNumber,
ScheduleOrder)
SELECT CTE2.FlightDate,
CTE2.FlightNumber,
CTE2.ScheduleOrder,
f.AircraftNumber AS OriginalAircraftNumber,
l.AircraftNumber AS FinalAircraftNumber,
CASE WHEN f.AircraftNumber = l.AircraftNumber THEN 'N' ELSE 'Y' END AS ChangeOfAircraftIndicator
FROM CTE2
JOIN CTE f ON f.FlightNumber = CTE2.FlightNumber
AND f.ScheduleOrder = CTE2.ScheduleOrder
AND f.RN = CTE2.First_Row
JOIN CTE l ON l.FlightNumber = CTE2.FlightNumber
AND l.ScheduleOrder = CTE2.ScheduleOrder
AND l.RN = CTE2.Last_Row
August 30, 2012 at 1:29 pm
Based on your sample data and DLL this should work:
SELECTFlightDate,
FlightNumber,
ScheduleOrder,
MIN(AircraftNumber) OriginalAircraftNumber,
MAX(AircraftNumber) FinalAircraftNumber,
ChangeOfAircraftIndicator=
CASE
WHEN COUNT(ScheduleOrder)=1 THEN 'N' ELSE 'Y'
END
FROM [dbo].[DailySchedule]
GROUP BY FlightDate, FlightNumber, ScheduleOrder
ORDER BY FlightDate, FlightNumber, ScheduleOrder
This will not work for FlightDates that change (e.g. A flight that starts on Jan1 and ends Jan2) or if you include times in your FlightDate Column. But this should get you started.
-- Itzik Ben-Gan 2001
August 30, 2012 at 1:34 pm
XMLSQLNinja (8/30/2012)
Based on your sample data and DLL this should work:
SELECTFlightDate,
FlightNumber,
ScheduleOrder,
MIN(AircraftNumber) OriginalAircraftNumber,
MAX(AircraftNumber) FinalAircraftNumber,
ChangeOfAircraftIndicator=
CASE
WHEN COUNT(ScheduleOrder)=1 THEN 'N' ELSE 'Y'
END
FROM [dbo].[DailySchedule]
GROUP BY FlightDate, FlightNumber, ScheduleOrder
ORDER BY FlightDate, FlightNumber, ScheduleOrder
This will not work for FlightDates that change (e.g. A flight that starts on Jan1 and ends Jan2) or if you include times in your FlightDate Column. But this should get you started.
And it won't work if the OriginalAircraftNumber should be 847 and the FinalAircraftNumber should be 55. Also I am not quite sure the case statement will work exactly. What if there are two rows with the same aircraft number? In that case it should be 'N' but the count would be 2.
I don't think any of us are going to get to the conclusion until we hear back from the OP about what the actual specs are.
_______________________________________________________________
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/
August 30, 2012 at 1:50 pm
Sean Lange (8/30/2012)
And it won't work if the OriginalAircraftNumber should be 847 and the FinalAircraftNumber should be 55. Also I am not quite sure the case statement will work exactly. What if there are two rows with the same aircraft number? In that case it should be 'N' but the count would be 2.
I don't think any of us are going to get to the conclusion until we hear back from the OP about what the actual specs are.
Good catch. This is one of those times where a sequential primary key would really help.
-- Itzik Ben-Gan 2001
August 30, 2012 at 2:09 pm
Thanks for trying to answer my queries. To Seans' questions...
You have two rows here and one of them is the OriginalAircraftNumber and one becomes the FinalAircraftNumber. What can you use to sort these so you know which one is which. Also, how do you want to handle situations where there are 3 rows with a ScheduleOrder of 1?
If you look at the source table, the last two columns (LastDateModified and LastTimeModified) are used to see when was that record modified originally and what is the latest modification. It doesn't matters where there are 3 or more rows with a scheduleOrder of 1. As long as it fall on the particular FlightDate ,FlightNumber, and ScheduleOrder.... we've to look at the LastDatemodified and LasttimeModified and the subsequent AircraftNumber. All other AircraftNumber between the first and latest date and time will be ignored.
Also, I agree with you on not using the varchar for the datetime. However, the source table is the production table and thats the only thing I've got to use.
Let me know if you need any further clarifications.
August 30, 2012 at 2:33 pm
sql1411 (8/30/2012)
Thanks for trying to answer my queries. To Seans' questions...You have two rows here and one of them is the OriginalAircraftNumber and one becomes the FinalAircraftNumber. What can you use to sort these so you know which one is which. Also, how do you want to handle situations where there are 3 rows with a ScheduleOrder of 1?
If you look at the source table, the last two columns (LastDateModified and LastTimeModified) are used to see when was that record modified originally and what is the latest modification. It doesn't matters where there are 3 or more rows with a scheduleOrder of 1. As long as it fall on the particular FlightDate ,FlightNumber, and ScheduleOrder.... we've to look at the LastDatemodified and LasttimeModified and the subsequent AircraftNumber. All other AircraftNumber between the first and latest date and time will be ignored.
Also, I agree with you on not using the varchar for the datetime. However, the source table is the production table and thats the only thing I've got to use.
Let me know if you need any further clarifications.
Given your explanation I think that the second query that Luis posted above should do exactly what you are looking for.
_______________________________________________________________
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 6, 2012 at 10:28 am
Thanks u all. That helped
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply