Loading the distinct rows in the destination table fromt the source table with some logic

  • 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'

  • 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/

  • 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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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/

  • 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.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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.

  • 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/

  • 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