Very Odd Query to Select Data

  • Hello Everyone

    I need some real help with this one. I have some data that is mostly all the same, but mostly all different, and I need to select some data, and select other data, and then select other data that is depending on the other select. I know, this is confusing, and I will do my best to explain what is needed, to the best of my ability.

    All of this data is not real, just sample data, but it is of the correct data type. Here is the data:

    ---------------------------------------------------------

    IF OBJECT_ID('TempDB..#Member','U') IS NOT NULL

    DROP TABLE #Member

    ---------------------------------------------------------

    CREATE TABLE #Member

    (

    DateEntered DATETIME

    , MemberControlID BIGINT

    , MemberExternalID BIGINT

    , MemberFullName VARCHAR(100)

    )

    --------------------------------------------------------

    INSERT INTO #Member

    (DateEntered, MemberControlID, MemberExternalID, MemberFullName)

    SELECT '2011-12-07 12:18:53.000', 20111207127338, 713887858, 'Sam C White' UNION ALL

    SELECT '2011-12-07 12:21:28.000', 20111207127573, 713887858, 'Sam C White' UNION ALL

    SELECT '2011-12-07 12:46:37.000', 20111207130010, 713558747, 'Roger E Saunders' UNION ALL

    SELECT '2011-12-07 12:47:17.000', 20111207130096, 713558747, 'Roger E Saunders' UNION ALL

    SELECT '2011-12-07 10:19:31.000', 20111207113756, 713880946, 'Mike L Carry' UNION ALL

    SELECT '2011-12-07 10:54:35.000', 20111207118805, 713880946, 'Mike L Carry' UNION ALL

    SELECT '2011-12-07 10:30:20.000', 20111207115324, 101377900, 'Alan G Rivers' UNION ALL

    SELECT '2011-12-07 10:29:50.000', 20111207115232, 101379911, 'Alan G Rivers' UNION ALL

    SELECT '2011-12-07 10:17:00.000', 20111207113463, 101381234, 'Alan G Rivers' UNION ALL

    SELECT '2011-12-07 10:29:50.000', 20111207115233, 101381234, 'Alan G Rivers' UNION ALL

    SELECT '2011-12-07 10:31:56.000', 20111207115515, 101381234, 'Alan G Rivers' UNION ALL

    SELECT '2011-12-07 08:21:07.000', 20111207100625, 713878123, 'Alice A Rogers' UNION ALL

    SELECT '2011-12-07 08:21:53.000', 20111207100705, 713878123, 'Alice A Rogers' UNION ALL

    SELECT '2011-12-07 09:55:13.000', 20111207110972, 713878123, 'Alice A Rogers' UNION ALL

    SELECT '2011-12-07 11:19:56.000', 20111207121532, 713886667, 'Albert A Michaels' UNION ALL

    SELECT '2011-12-07 12:48:43.000', 20111207130252, 101368889, 'Alex R Bennett' UNION ALL

    SELECT '2011-12-07 12:43:21.000', 20111207129702, 713887835, 'Alfred B Jones' UNION ALL

    SELECT '2011-12-07 12:47:22.000', 20111207130100, 713887835, 'Alfred B Jones' UNION ALL

    SELECT '2011-12-07 08:28:10.000', 20111207101327, 101381237, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 08:47:28.000', 20111207103393, 101381237, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 08:28:45.000', 20111207101392, 713886750, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 08:34:17.000', 20111207102125, 713886750, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 12:10:19.000', 20111207126555, 713886750, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 12:10:34.000', 20111207126581, 713886750, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 12:10:49.000', 20111207126607, 713886750, 'Gregory L Adkinson'

    ---------------------------------------------------------

    SELECT * FROM #Member

    I need to select the MAX(MemberExternalID) per each Member for each distinct MemberExternalID and for the MAX MemberControlID, and insert this data into a Table Variable, and UPDATE a simple bit flag indicating the Column had been selected

    No big deal on the last part, but how do I select said data to be inserted? I need help with selecting the data that is needed, each time.

    DateEntered MemberControlIDMemberExternalID MemberFullName

    2011-12-07 12:18:53.00020111207127338713887858Sam C White -- This one

    2011-12-07 12:21:28.00020111207127573713887858Sam C White

    2011-12-07 12:46:37.00020111207130010713558747Roger E Saunders -- This one

    2011-12-07 12:47:17.00020111207130096713558747Roger E Saunders

    2011-12-07 10:19:31.00020111207113756713880946Mike L Carry -- This one

    2011-12-07 10:54:35.00020111207118805713880946Mike L Carry

    2011-12-07 10:30:20.00020111207115324101377900Alan G Rivers -- This one

    2011-12-07 10:29:50.00020111207115232101379911Alan G Rivers -- This one

    2011-12-07 10:17:00.00020111207113463101381234Alan G Rivers

    2011-12-07 10:29:50.00020111207115233101381234Alan G Rivers

    2011-12-07 10:31:56.00020111207115515101381234Alan G Rivers

    2011-12-07 08:21:07.00020111207100625713878123Alice A Rogers -- This one

    2011-12-07 08:21:53.00020111207100705713878123Alice A Rogers

    2011-12-07 09:55:13.00020111207110972713878123Alice A Rogers

    2011-12-07 11:19:56.00020111207121532713886667Albert A Michaels -- This one

    2011-12-07 12:48:43.00020111207130252101368889Alex R Bennett -- This one

    2011-12-07 12:43:21.00020111207129702713887835Alfred B Jones -- This one

    2011-12-07 12:47:22.00020111207130100713887835Alfred B Jones

    2011-12-07 08:28:10.00020111207101327101381237Gregory L Adkinson -- This one

    2011-12-07 08:47:28.00020111207103393101381237Gregory L Adkinson

    2011-12-07 08:28:45.00020111207101392713886750Gregory L Adkinson -- This one

    2011-12-07 08:34:17.00020111207102125713886750Gregory L Adkinson

    2011-12-07 12:10:19.00020111207126555713886750Gregory L Adkinson

    2011-12-07 12:10:34.00020111207126581713886750Gregory L Adkinson

    2011-12-07 12:10:49.00020111207126607713886750Gregory L Adkinson

    I then need to select this data into another table Variable:

    DateEntered MemberControlIDMemberExternalIDMemberFullName

    2011-12-07 12:18:53.00020111207127338713887858Sam C White -- Done

    2011-12-07 12:21:28.00020111207127573713887858Sam C White -- Select This one

    2011-12-07 12:46:37.00020111207130010713558747Roger E Saunders -- Done

    2011-12-07 12:47:17.00020111207130096713558747Roger E Saunders -- Select This one

    2011-12-07 10:19:31.00020111207113756713880946Mike L Carry -- Done

    2011-12-07 10:54:35.00020111207118805713880946Mike L Carry -- Select This one

    2011-12-07 10:30:20.00020111207115324101377900Alan G Rivers -- Done

    2011-12-07 10:29:50.00020111207115232101379911Alan G Rivers -- Done

    2011-12-07 10:17:00.00020111207113463101381234Alan G Rivers -- Select This one

    2011-12-07 10:29:50.00020111207115233101381234Alan G Rivers

    2011-12-07 10:31:56.00020111207115515101381234Alan G Rivers

    2011-12-07 08:21:07.00020111207100625713878123Alice A Rogers -- Done

    2011-12-07 08:21:53.00020111207100705713878123Alice A Rogers -- Select This one

    2011-12-07 09:55:13.00020111207110972713878123Alice A Rogers

    2011-12-07 11:19:56.00020111207121532713886667Albert A Michaels -- Done

    2011-12-07 12:48:43.00020111207130252101368889Alex R Bennett -- Done

    2011-12-07 12:43:21.00020111207129702713887835Alfred B Jones -- Done

    2011-12-07 12:47:22.00020111207130100713887835Alfred B Jones -- Select This one

    2011-12-07 08:28:10.00020111207101327101381237Gregory L Adkinson -- Done

    2011-12-07 08:47:28.00020111207103393101381237Gregory L Adkinson -- Select This one

    2011-12-07 08:28:45.00020111207101392713886750Gregory L Adkinson -- Done

    2011-12-07 08:34:17.00020111207102125713886750Gregory L Adkinson -- Select This one

    2011-12-07 12:10:19.00020111207126555713886750Gregory L Adkinson

    2011-12-07 12:10:34.00020111207126581713886750Gregory L Adkinson

    2011-12-07 12:10:49.00020111207126607713886750Gregory L Adkinson

    Last of all, I now need to select this data into yet another table variable in order of the MemberControlID, DateEntered in Ascending order:

    DateEntered MemberControlIDMemberExternalIDMemberFullName

    2011-12-07 12:18:53.00020111207127338713887858Sam C White -- Done

    2011-12-07 12:21:28.00020111207127573713887858Sam C White -- Done

    2011-12-07 12:46:37.00020111207130010713558747Roger E Saunders -- Done

    2011-12-07 12:47:17.00020111207130096713558747Roger E Saunders -- Done

    2011-12-07 10:19:31.00020111207113756713880946Mike L Carry -- Done

    2011-12-07 10:54:35.00020111207118805713880946Mike L Carry -- Done

    2011-12-07 10:30:20.00020111207115324101377900Alan G Rivers -- Done

    2011-12-07 10:29:50.00020111207115232101379911Alan G Rivers -- Done

    2011-12-07 10:17:00.00020111207113463101381234Alan G Rivers -- Select This one #1

    2011-12-07 10:29:50.00020111207115233101381234Alan G Rivers -- Select This one #2

    2011-12-07 10:31:56.00020111207115515101381234Alan G Rivers -- Select This one #3

    2011-12-07 08:21:07.00020111207100625713878123Alice A Rogers -- Done

    2011-12-07 08:21:53.00020111207100705713878123Alice A Rogers -- Done

    2011-12-07 09:55:13.00020111207110972713878123Alice A Rogers -- Select This one #4

    2011-12-07 11:19:56.00020111207121532713886667Albert A Michaels -- Done

    2011-12-07 12:48:43.00020111207130252101368889Alex R Bennett -- Done

    2011-12-07 12:43:21.00020111207129702713887835Alfred B Jones -- Done

    2011-12-07 12:47:22.00020111207130100713887835Alfred B Jones -- Done

    2011-12-07 08:28:10.00020111207101327101381237Gregory L Adkinson -- Done

    2011-12-07 08:47:28.00020111207103393101381237Gregory L Adkinson -- Done

    2011-12-07 08:28:45.00020111207101392713886750Gregory L Adkinson -- Done

    2011-12-07 08:34:17.00020111207102125713886750Gregory L Adkinson -- Done

    2011-12-07 12:10:19.00020111207126555713886750Gregory L Adkinson -- Select This one #5

    2011-12-07 12:10:34.00020111207126581713886750Gregory L Adkinson -- Select This one #6

    2011-12-07 12:10:49.00020111207126607713886750Gregory L Adkinson -- Select This one #7

    I know this is a lot, and I greatly appreciate any and all help, comments or suggestions.

    Thank You in Advance for everything

    Andrew SQLDBA

  • Hi Andrew,

    I'm not sure if this would help you to get started:

    Instead of trying to select one at a time, you could use Row_Number() to place the rquired rows in order. Depending if you really need to process those data sequentially,you could then have some sort of l.o.o.p. *cough* to go through the values from pos=1 to pos=5. (you might need to extract the max value separately, if needed)

    SELECT *,

    ROW_NUMBER() OVER(PARTITION BY MemberExternalID ORDER BY MemberControlID desc ) pos

    FROM #Member

    ORDER BY MemberExternalID,pos



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks, but I do not need to process them in any order like that. And I do have a RowID column that is the table Pri Key column.

    I need them all separated into Temp tables as indicated in my original post.

    Thanks for your input and comment though. Any idea how I can select the data as indicated?

    Thanks always

    Andrew SQLDBA

  • Andrew,

    I took a look through this and your data and what you've described as the business rules and it doesn't make sense offhand. Hopefully you can clarify.

    Honestly, your initial description didn't make sense to me as "MAX(MemberExternalID) per each Member for each distinct MemberExternalID " seemed repetitive.

    So I went to the data. This made more sense, however, poor Alan confuses me. One of his distinct MemberExternalIDs is being ignored, yet the other two are still part of the first selection:

    2011-12-07 10:30:20.000 20111207115324 101377900 Alan G Rivers -- This one

    2011-12-07 10:29:50.000 20111207115232 101379911 Alan G Rivers -- This one

    2011-12-07 10:17:00.000 20111207113463 101381234 Alan G Rivers

    When we get to the second set, That finally gets chosen for Alan, yet if you look down below at Gregory he 'double selects' for each of his MemberExternalIDs.

    As an additional problem, they all seem out of order for your selectivity for the 'max' choices. Might just be a copy/paste issue, but it looks like you want all MINs, not MAXs, at each pass.

    What I think you MEANT to say (which of course will be wrong, but hopefully you can clarify) is that you need to select the MIN( MemberControlID) for each MemberExternalID.

    If that's so, you need to touch this iteratively, and treat it like a logging table. Then you apply iteration1 to iteration2, and then finally iterations 1 and 2 against the final result set, treating MemberControlID and MemberExternalID as a composite key.

    This gets you through steps one and two:

    ---------------------------------------------------------

    IF OBJECT_ID('TempDB..#Member','U') IS NOT NULL

    DROP TABLE #Member

    IF OBJECT_ID('TempDB..#Iteration1','U') IS NOT NULL

    DROP TABLE #Iteration1

    IF OBJECT_ID('TempDB..#Iteration2','U') IS NOT NULL

    DROP TABLE #Iteration2

    IF OBJECT_ID('TempDB..#Iteration3','U') IS NOT NULL

    DROP TABLE #Iteration3

    --------------------------------------------------------

    CREATE TABLE #Member

    (

    DateEntered DATETIME

    , MemberControlID BIGINT

    , MemberExternalID BIGINT

    , MemberFullName VARCHAR(100)

    )

    --------------------------------------------------------

    INSERT INTO #Member

    (DateEntered, MemberControlID, MemberExternalID, MemberFullName)

    SELECT '2011-12-07 12:18:53.000', 20111207127338, 713887858, 'Sam C White' UNION ALL

    SELECT '2011-12-07 12:21:28.000', 20111207127573, 713887858, 'Sam C White' UNION ALL

    SELECT '2011-12-07 12:46:37.000', 20111207130010, 713558747, 'Roger E Saunders' UNION ALL

    SELECT '2011-12-07 12:47:17.000', 20111207130096, 713558747, 'Roger E Saunders' UNION ALL

    SELECT '2011-12-07 10:19:31.000', 20111207113756, 713880946, 'Mike L Carry' UNION ALL

    SELECT '2011-12-07 10:54:35.000', 20111207118805, 713880946, 'Mike L Carry' UNION ALL

    SELECT '2011-12-07 10:30:20.000', 20111207115324, 101377900, 'Alan G Rivers' UNION ALL

    SELECT '2011-12-07 10:29:50.000', 20111207115232, 101379911, 'Alan G Rivers' UNION ALL

    SELECT '2011-12-07 10:17:00.000', 20111207113463, 101381234, 'Alan G Rivers' UNION ALL

    SELECT '2011-12-07 10:29:50.000', 20111207115233, 101381234, 'Alan G Rivers' UNION ALL

    SELECT '2011-12-07 10:31:56.000', 20111207115515, 101381234, 'Alan G Rivers' UNION ALL

    SELECT '2011-12-07 08:21:07.000', 20111207100625, 713878123, 'Alice A Rogers' UNION ALL

    SELECT '2011-12-07 08:21:53.000', 20111207100705, 713878123, 'Alice A Rogers' UNION ALL

    SELECT '2011-12-07 09:55:13.000', 20111207110972, 713878123, 'Alice A Rogers' UNION ALL

    SELECT '2011-12-07 11:19:56.000', 20111207121532, 713886667, 'Albert A Michaels' UNION ALL

    SELECT '2011-12-07 12:48:43.000', 20111207130252, 101368889, 'Alex R Bennett' UNION ALL

    SELECT '2011-12-07 12:43:21.000', 20111207129702, 713887835, 'Alfred B Jones' UNION ALL

    SELECT '2011-12-07 12:47:22.000', 20111207130100, 713887835, 'Alfred B Jones' UNION ALL

    SELECT '2011-12-07 08:28:10.000', 20111207101327, 101381237, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 08:47:28.000', 20111207103393, 101381237, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 08:28:45.000', 20111207101392, 713886750, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 08:34:17.000', 20111207102125, 713886750, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 12:10:19.000', 20111207126555, 713886750, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 12:10:34.000', 20111207126581, 713886750, 'Gregory L Adkinson' UNION ALL

    SELECT '2011-12-07 12:10:49.000', 20111207126607, 713886750, 'Gregory L Adkinson'

    ---------------------------------------------------------

    --SELECT * FROM #Member

    SELECT

    m.*

    INTO

    #Iteration1

    FROM

    #Member AS m

    JOIN

    (SELECT

    MemberExternalID,

    MIN(MemberControlID) AS MaxCID

    FROM

    #Member

    GROUP BY

    MemberExternalID

    ) AS drv

    ONm.MemberExternalID = drv.MemberExternalID

    AND m.MemberControlID = drv.MaxCID

    SELECT

    m.*

    INTO

    #Iteration2

    FROM

    #Member AS m

    JOIN

    (SELECT

    m.MemberExternalID,

    MIN( m.MemberControlID) AS MaxCID

    FROM

    #Member AS m

    LEFT JOIN

    #Iteration1 AS i1

    ONm.MemberExternalID = i1.MemberExternalID

    AND m.MemberControlID = i1.MemberControlID

    WHERE

    i1.MemberControlID IS NULL

    GROUP BY

    m.MemberExternalID

    ) AS drv

    ONm.MemberExternalID = drv.MemberExternalID

    AND m.MemberControlID = drv.MaxCID

    select * from #iteration1

    SELECT * FROM #iteration2

    Now, doing this via straight data patterns...

    For the final query, you want the information ordered by MemberExternalID, then MemberControlID, and with a row-position indicator.

    Simple enough...

    SELECT

    ROW_NUMBER() OVER( ORDER BY m.MemberExternalID, m.MemberControlID) AS RowNum,

    m.*

    INTO

    #Iteration3

    FROM

    #Member AS m

    JOIN

    (SELECT

    m.MemberExternalID,

    m.MemberControlID

    FROM

    #Member AS m

    LEFT JOIN

    (SELECT MemberExternalID, MemberControlID

    FROM#Iteration1

    UNION ALL

    SELECT MemberExternalID, MemberControlID

    FROM#Iteration2

    ) AS unioner

    ONm.MemberExternalID = unioner.MemberExternalID

    AND m.MemberControlID = unioner.MemberControlID

    WHERE

    unioner.MemberControlID IS NULL

    ) AS drv

    ONm.MemberExternalID = drv.MemberExternalID

    AND m.MemberControlID = drv.MemberControlID

    SELECT * FROM #Iteration3 ORDER BY RowNum

    Two of Alan's falls out here because of the data assumptions made to passes #1 and #2 about the MemberExternalID usage.

    Let me know what I'm not understanding, I'll see if I can clean this up once we've nailed those down.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig

    Thank You so very much. This is extremely close. Yes, I agree, my description of what the data needed to be coming out was not so good.

    I will try again after a nights sleep. LOL.

    ---------------------------------------------------------

    IF OBJECT_ID('TempDB..#Member','U') IS NOT NULL

    DROP TABLE #Member

    IF OBJECT_ID('TempDB..#Iteration1','U') IS NOT NULL

    DROP TABLE #Iteration1

    IF OBJECT_ID('TempDB..#Iteration2','U') IS NOT NULL

    DROP TABLE #Iteration2

    IF OBJECT_ID('TempDB..#Iteration3','U') IS NOT NULL

    DROP TABLE #Iteration3

    --------------------------------------------------------

    CREATE TABLE #Member

    (

    DateEntered DATETIME

    , MemberControlID BIGINT

    , MemberExternalID BIGINT

    , MemberFullName VARCHAR(100)

    )

    --------------------------------------------------------

    INSERT INTO #Member

    (DateEntered, MemberControlID, MemberExternalID, MemberFullName)

    SELECT '2011-12-07 12:18:53.000', 20111207127338, 713887858, 'Sam C White' UNION ALL -- #Table1

    SELECT '2011-12-07 12:21:28.000', 20111207127573, 713887858, 'Sam C White' UNION ALL -- #Table2

    SELECT '2011-12-07 12:46:37.000', 20111207130010, 713558747, 'Roger E Saunders' UNION ALL -- #Table1

    SELECT '2011-12-07 12:47:17.000', 20111207130096, 713558747, 'Roger E Saunders' UNION ALL -- #Table2

    SELECT '2011-12-07 10:19:31.000', 20111207113756, 713880946, 'Mike L Carry' UNION ALL -- #Table1

    SELECT '2011-12-07 10:54:35.000', 20111207118805, 713880946, 'Mike L Carry' UNION ALL -- #Table2

    SELECT '2011-12-07 10:30:20.000', 20111207115324, 101377900, 'Alan G Rivers' UNION ALL -- #Table1

    SELECT '2011-12-07 10:29:50.000', 20111207115232, 101379911, 'Alan G Rivers' UNION ALL -- #Table1

    SELECT '2011-12-07 10:17:00.000', 20111207113463, 101381234, 'Alan G Rivers' UNION ALL -- #Table1

    SELECT '2011-12-07 10:29:50.000', 20111207115233, 101381234, 'Alan G Rivers' UNION ALL -- #Table2

    SELECT '2011-12-07 10:31:56.000', 20111207115515, 101381234, 'Alan G Rivers' UNION ALL -- #Table3

    SELECT '2011-12-07 08:21:07.000', 20111207100625, 713878123, 'Alice A Rogers' UNION ALL -- #Table1

    SELECT '2011-12-07 08:21:53.000', 20111207100705, 713878123, 'Alice A Rogers' UNION ALL -- #Table2

    SELECT '2011-12-07 09:55:13.000', 20111207110972, 713878123, 'Alice A Rogers' UNION ALL -- #Table3

    SELECT '2011-12-07 11:19:56.000', 20111207121532, 713886667, 'Albert A Michaels' UNION ALL -- #Table1

    SELECT '2011-12-07 12:48:43.000', 20111207130252, 101368889, 'Alex R Bennett' UNION ALL -- #Table1

    SELECT '2011-12-07 12:43:21.000', 20111207129702, 713887835, 'Alfred B Jones' UNION ALL -- #Table1

    SELECT '2011-12-07 12:47:22.000', 20111207130100, 713887835, 'Alfred B Jones' UNION ALL -- #Table2

    SELECT '2011-12-07 08:28:10.000', 20111207101327, 101381237, 'Gregory L Adkinson' UNION ALL -- #Table1

    SELECT '2011-12-07 08:47:28.000', 20111207103393, 101381237, 'Gregory L Adkinson' UNION ALL -- #Table2

    SELECT '2011-12-07 08:28:45.000', 20111207101392, 713886750, 'Gregory L Adkinson' UNION ALL -- #Table1

    SELECT '2011-12-07 08:34:17.000', 20111207102125, 713886750, 'Gregory L Adkinson' UNION ALL -- #Table2

    SELECT '2011-12-07 12:10:19.000', 20111207126555, 713886750, 'Gregory L Adkinson' UNION ALL -- #Table3

    SELECT '2011-12-07 12:10:34.000', 20111207126581, 713886750, 'Gregory L Adkinson' UNION ALL -- #Table3

    SELECT '2011-12-07 12:10:49.000', 20111207126607, 713886750, 'Gregory L Adkinson' -- #Table3

    ---------------------------------------------------------

    If the ExternalID is the same, order the row by the MIN(ControlID), and choose only one distinct ExternalID for #Table1

    You are correct about Alan Rivers, he does have three distinct ExternalIDs, so the three distinct records need to me in #Table1

    Now, since the third record for Alan Rivers has the same ExternalID for multiple other rows, look for the MIN(ControlID) as the qualifier to select into #Table1

    SELECT '2011-12-07 10:30:20.000', 20111207115324, 101377900, 'Alan G Rivers' UNION ALL -- #Table1

    SELECT '2011-12-07 10:29:50.000', 20111207115232, 101379911, 'Alan G Rivers' UNION ALL -- #Table1

    SELECT '2011-12-07 10:17:00.000', 20111207113463, 101381234, 'Alan G Rivers' UNION ALL -- #Table1

    SELECT '2011-12-07 10:29:50.000', 20111207115233, 101381234, 'Alan G Rivers' UNION ALL -- #Table2

    SELECT '2011-12-07 10:31:56.000', 20111207115515, 101381234, 'Alan G Rivers' UNION ALL -- #Table3

    But since there are other records for Alan Rivers with the same ExternalID, must use the same rule, and select the row by using the MIN(ControlID), along with ExternalID's that are left

    SELECT '2011-12-07 10:29:50.000', 20111207115233, 101381234, 'Alan G Rivers' UNION ALL -- #Table2

    And Last, since there are more records for Alan Rivers, any remaining records are inserted into #Table3 in ControlID ASC order. I know the rows will not stay in order inside the table, but you know what I mean.

    SELECT '2011-12-07 10:31:56.000', 20111207115515, 101381234, 'Alan G Rivers' UNION ALL -- #Table3

    And a good example of this is records forGregory Adkinson.

    SELECT '2011-12-07 08:28:10.000', 20111207101327, 101381237, 'Gregory L Adkinson' UNION ALL -- #Table1

    SELECT '2011-12-07 08:47:28.000', 20111207103393, 101381237, 'Gregory L Adkinson' UNION ALL -- #Table2

    SELECT '2011-12-07 08:28:45.000', 20111207101392, 713886750, 'Gregory L Adkinson' UNION ALL -- #Table1

    SELECT '2011-12-07 08:34:17.000', 20111207102125, 713886750, 'Gregory L Adkinson' UNION ALL -- #Table2

    SELECT '2011-12-07 12:10:19.000', 20111207126555, 713886750, 'Gregory L Adkinson' UNION ALL -- #Table3

    SELECT '2011-12-07 12:10:34.000', 20111207126581, 713886750, 'Gregory L Adkinson' UNION ALL -- #Table3

    SELECT '2011-12-07 12:10:49.000', 20111207126607, 713886750, 'Gregory L Adkinson' -- #Table3

    All records follow that rule, in that order. If there is only one record for a Member, then that row will be in #Table1, then the next row with the same

    ExternalID, you must use the MIN(ControlID) to insert that one into #Table2, and the third record with the same ExternalID, and the Min(ControlID)

    goes into #Table3, and all other records for that ExternalID are stored in #Table3 in ASC order of ControlID. Grogory Adkinson is a good example of this.

    I have tried massaging the code that you wrote to get it correct, but I am missing something small. I greatly appreciate all your work that you have done.

    Andrew SQLDBA

  • Can you run the code I offered and tell me exactly which row(s) are not positioning correctly?

    Apologies, but I just don't have time to dig through that at the moment, very busy day. I'm not ignoring this but I won't have time for heavy digging until later this afternoon. If you can tell me the one or two places I'm a little off, I can probably tag the issue pretty easily.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Sure will

    I have been working on it, and going thru the small list by hand to see exactly what rows are not in the correct place. I am coming close to the exact solution, and I will continue to work on that. I understand about getting busy. Only on the days do not think that you will be, do you always get covered.

    Andrew SQLDBA

  • AndrewSQLDBA (12/8/2011)


    Sure will

    I have been working on it, and going thru the small list by hand to see exactly what rows are not in the correct place. I am coming close to the exact solution, and I will continue to work on that. I understand about getting busy. Only on the days do not think that you will be, do you always get covered.

    Andrew SQLDBA

    I did a quick review and as far as I can tell my code hit that spot on. I'm not sure what I'm not seeing here. Take a look at this result set, as far as I can tell it's what you wanted (after running all previous code), it will combine the result set into a single batch for review.

    SELECT

    *, 0 AS RowNum, '1st Table' AS TableUsed

    FROM

    #Iteration1

    UNION ALL

    SELECT

    *, 0 AS RowNum, '2nd Table' AS TableUsed

    FROM

    #Iteration2

    UNION ALL

    SELECT

    DateEntered, MemberControlID, MemberExternalID, MemberFullName, RowNum, '3rd Table' AS TableUsed

    FROm

    #Iteration3

    ORDER BY

    MemberExternalID, MemberControlID, TableUsed, RowNum


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig

    After going over the results very closely, you are correct, and your initial code was perfect.

    Not sure what I had seen that I thought was not correct.

    I apologize to you for that.

    Everything looks just perfect. I am going over your code to see how you did that, and to learn for myself how to code it for next time.

    I greatly appreciate your help. I am beginning to enjoy SQL Development more and more. It sure beats the DBA stuff.

    Thank You

    Andrew SQLDBA

  • AndrewSQLDBA (12/8/2011)


    Craig

    After going over the results very closely, you are correct, and your initial code was perfect.

    Not sure what I had seen that I thought was not correct.

    I apologize to you for that.

    Pah, no worries. Eventually all the data just swims together when you've stared at it too long. 😉

    Everything looks just perfect. I am going over your code to see how you did that, and to learn for myself how to code it for next time.

    I greatly appreciate your help. I am beginning to enjoy SQL Development more and more. It sure beats the DBA stuff.

    Thank You

    Andrew SQLDBA

    My pleasure. Dev's where it's at, that's where the new challenges are at in my opinion. I can do the administrative work but really it's just the same thing next month, next year, with some new tools in the belt each version. Massaging and manipulating raw data into actual business usable information is where I find the most enjoyable challenge.

    The process itself is relatively easy, if you break it down to an algorithm instead of code. First do a logging table check for the first record of each batch. Next, repeat the process but doing a LEFT JOIN IS NULL check so you strip out the first set. The third set is merely a union of the first two for your LEFT JOIN IS NULL check and then applying a rownumber to what's left in the desired order by.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply