December 7, 2011 at 4:44 pm
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
December 7, 2011 at 5:16 pm
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
December 7, 2011 at 6:06 pm
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
December 7, 2011 at 6:36 pm
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.
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
December 8, 2011 at 6:17 am
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
December 8, 2011 at 11:55 am
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.
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
December 8, 2011 at 11:59 am
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
December 8, 2011 at 1:46 pm
AndrewSQLDBA (12/8/2011)
Sure willI 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
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
December 8, 2011 at 2:53 pm
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
December 8, 2011 at 3:10 pm
AndrewSQLDBA (12/8/2011)
CraigAfter 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.
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