May 26, 2011 at 12:18 pm
Have an interesting problem can’t quite get my head around to parse in tsql.
We have these 3 tables as the result of a procedurally created flat file (the Fortran type e.g. written from right to left, top to bottom) and read into sql. The file is created basically by reading in scanned paper orders and here's the results I'm forced to work with:
3 resultant tables:
***Meal Table***
Item Type | item descr | Order found in flat file
MEAL | Happy Meal | 1
MEAL |Big Breakfast | 5
MEAL| Fish Special | 12
MEAL| Chicken Platter | 14
***Food Table***
Item Type | item descr | Order found in flat file
FOOD | small burger | 2
FOOD |small fries | 3
FOOD| Bacon | 6
FOOD| Pancakes | 7
FOOD| Hash Browns | 8
FOOD| Cup of Oatmeal | 9
FOOD| Cod Planks | 13
FOOD| Chicken Tenders | 15
FOOD| Corn on the cob | 16
FOOD| Coleslaw | 17
***Beverage Table ****
Item Type | item descr | Order found in flat file
DRINK| small Coke| 4
DRINK| Coffee| 10
DRINK| Orange Juice| 11
DRINK| Budweiser| 18
The question is how can I key/join the tables using only the "Order found in flat file" column and assuming Meals is the parent, Food is the child, and beverages are the optional grandchild?? And how best to key/join them expecting 100K records per day?
Any and all suggestion would be of GREAT help.
Thanks,
Nick
May 26, 2011 at 12:24 pm
Is this homework, cus it feels that way..
CEWII
May 26, 2011 at 12:31 pm
Man, I feel like slime posting this but for its real, I'm taSked to solve it and for the life of me can't get started.
Even hints would help.
MUCH OBLIGED:-)
May 26, 2011 at 12:38 pm
If its real then fine, but I don't "do homework". Let me think about it over lunch..
CEWII
May 26, 2011 at 12:41 pm
Thanks.
Much appreciated.
Nick
May 26, 2011 at 1:58 pm
What is the desired output?
CEWII
May 26, 2011 at 2:23 pm
The desired output was an inner join from Meals to Food and a left join from Food to Beverage.
This helps. Thanks.
May 26, 2011 at 2:55 pm
This:
SELECT 'MEAL',
F1.meal_seq AS meal_start_seq,
(MAX(F2.meal_seq)-1) AS meal_end_seq,
MAX(F1.item_descr) AS item_descr
FROM MealFlatFile AS F1
LEFT OUTER JOIN
MealFlatFile AS F2
ON F1.meal_seq < F2.meal_seq
GROUP BY F1.meal_seq;
Actually reutrns this:
(No column name) meal_start_seq meal_end_seq item_descr
MEAL 1 13 Happy Meal
MEAL 5 13 Big Breakfast
MEAL 12 13 Fish Special
MEAL 14 NULL Chicken Platter
Did I miss something?
May 26, 2011 at 3:01 pm
Not sure, is the problem you didn't get a name for MEAL? You could do a 'MEAL' AS Meal
I didn't look over the query..
CEWII
May 26, 2011 at 3:26 pm
In retrospect I should have used "Condiments" instead of "Beverages" since they should join only to the "Food" items. So for instance, if you subsituted the "Coffee" and "Orange Juice" for "Raisins" and "Brown Sugar" they should join only to the "Oatmeal" and not to the meal itself. Some Food items will not have any "condiments" while some may. Their appearance in the "order" column dictates to which "Food" item they should map to.
In short, should be multiple levels deep.
May 26, 2011 at 3:51 pm
I would use a technique like this. Some notes:
1) Notice how I built out the dataset to be easily consumed for testing.
2) This can be continuously expanded as you go down levels. I've used some generic naming here since your original data is not what you're actually trying to solve directly, but is an example.
3) It is chewy and uses correlated subqueries, but it will get you there. There are probably cleaner ways but not simpler to explain if you have some difficulties. Let's get the concepts into play first. 🙂 Then we'll go for optimizations.
IF OBJECT_ID ( 'tempdb..#DataItems') IS NOT NULL
DROP TABLE #DataItems
IF OBJECT_ID ( 'tempdb..#Level1') IS NOT NULL
DROP TABLE #Level1
IF OBJECT_ID ( 'tempdb..#Level2') IS NOT NULL
DROP TABLE #Level2
-- This would be the staging table, get everything from the file IN first.
CREATE TABLE #DataItems (LevelCode VARCHAR(15), ItemDesc VARCHAR(50), FilePosition INT)
INSERT INTO #DataItems VALUES ( 'LEVEL 1', 'A', 1)
INSERT INTO #DataItems VALUES ( 'LEVEL 1', 'B', 5)
INSERT INTO #DataItems VALUES ( 'LEVEL 1', 'C', 12)
INSERT INTO #DataItems VALUES ( 'LEVEL 1', 'D', 19)
INSERT INTO #DataItems VALUES ( 'LEVEL 1', 'E', 20)
INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'A.a', 2)
INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'A.b', 3)
INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'A.b.1', 4)
INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'B.a', 6)
INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'B.a.1', 7)
INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'B.a.2', 8)
INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'B.b', 9)
INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'B.b.1', 10)
INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'B.b.2', 11)
INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'C.a', 13)
INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'C.b', 14)
INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'C.c', 15)
INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'C.d', 16)
INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'C.d.1', 17)
INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'C.d.1', 18)
INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'E.a', 21)
select * from #DataItems
ORDER BY FilePosition
-- The rest of your staging tables:
CREATE TABLE #Level1 ( L1ID INT IDENTITY( 1, 1) NOT NULL, ItemDesc VARCHAR(50), FilePos INT)
INSERT INTO #Level1 (ItemDesc, FilePos)
SELECT ItemDesc, FilePosition
FROM #DataItems
WHERE LevelCode = 'LEVEL 1'
CREATE TABLE #Level2 ( L2ID INT IDENTITY( 1, 1) NOT NULL, L1ID INT NOT NULL, ItemDesc VARCHAR(50), FilePos INT)
INSERT INTO #Level2 (L1ID, ItemDesc, FilePos)
SELECT
(SELECT L1ID FROM #Level1 WHERE FilePos = drv.MaxFP) AS L1ID,
di.ItemDesc, di.FilePosition
FROM
#DataItems AS di
CROSS APPLY
(SELECT MAX( FilePos) AS MaxFP FROM #Level1 AS L1 WHERE di.FilePosition > L1.FilePos) AS drv
WHERE
LevelCode = 'LEVEL 2'
SELECT * FROM #Level1
SELECT * FROM #Level2
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
May 26, 2011 at 4:19 pm
Many thanks!!
Will give it a go!!
I luv this site!!
May 27, 2011 at 8:14 am
Craig-
This works perfectly.
Was not familiar with "Cross Apply",
What would you see in the way of optimizations? Indexes on the "id"s on the Level tables?
Much obliged for your assistance.
Nick
May 27, 2011 at 12:52 pm
al_nick (5/27/2011)
Craig-This works perfectly.
Was not familiar with "Cross Apply",
Glad to hear, and my pleasure. 🙂
What would you see in the way of optimizations? Indexes on the "id"s on the Level tables?
That will depend on where this structure is going when it's done. Is it a Delta load? If so, it'll depend on how you're detecting the deltas. How often are you modifying the final tables... things like that.
However, yes, some quick indexes on the L1ID/L2IDs to allow for faster joining and an NC index or two on the descriptions would probably speed you up tremendously.
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 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply