May 28, 2008 at 10:07 am
I am running into an issue I do not have and idea to resolve:
Code:
--/*
DECLARE @OrderNumber AS Int
SET @orderNumber = 8392776
--*/
SELECTDistinct A.OrderGroupNumber
,A.OrderNumber
,B.ItemNumber
,B.ItemDescription
,B.[TotalQuantity] AS Quantity
,B.LineItemTotal
,B.ParentItemNumber
,B.ParentLineItemID
,B.GiftPack
,B.ExplodingKit
INTO #Temp1 FROM
dbo.uvw_OrderListingSummary As A With (NoLock)
LEFT oUTER JOIN orders.OrderFormLineItem As B With (NoLock) ON A.OrderGroupNumber = B.OrderGroupNumber
ANDA.OrderNumber = B.OrderNumber
LEFT OUTER JOIN [SharedDimension].dbo.DimOrderType AS C With (NoLock) ONA.OrderTypeXID = C.XID
LEFT OUTER JOIN [SharedDimension].dbo.DimlineItemStatus AS D With (NoLock) ON B.LineItemStatusXID = D.XID
WhereA.OrderNumber = @OrderNumber
--
Select * from #Temp1 t1
Drop Table #Temp1
Recordset:
OrderGroupNumber OrderNumber LineItemId ItemNumber ItemDescription Quantity LineItemTotal ParentItemNumber ParentLineItemID GiftPack ExplodingKit
---------------- ----------- ----------- ---------- ------------------------------ ----------- ------------------------------ ---------------- ---------------- -------- ------------
103051386 8392776 1 100570 THE PERFECT 10 1 75.99 NULL 0 0 1
103051386 8392776 2 127108 CORN BLACK BEAN SALSA 1 0.00 100570 1 0 0
103051386 8392776 3 151108 SPINACH AND HERB DIP MIX 1 0.00 100570 1 0 0
103051386 8392776 4 155108 BOUNTIFUL BEER BREAD MIX 1 0.00 100570 1 0 0
103051386 8392776 5 157108 NANAS APPLE CAKE MIX 1 0.00 100570 1 0 0
103051386 8392776 6 165107 SEASONED SALT 1 0.00 100570 1 0 0
103051386 8392776 7 168006 PERFECTLY POTATO CHEDDAR SOUP 1 0.00 100570 1 0 0
103051386 8392776 8 175108 ABSOLUTELY ALMOND POUND CAKE M 1 0.00 100570 1 0 0
103051386 8392776 9 232106 FIESTA PARTY DIP MIX 1 0.00 100570 1 0 0
103051386 8392776 10 267107 KEY LIME CHEESE BALL MIX 1 0.00 100570 1 0 0
103051386 8392776 11 357106 THE TRIO 1 0.00 100570 1 0 0
103051386 8392776 12 100608 SAVOR SUMMER 2 119.98 NULL 0 0 1
103051386 8392776 13 235207 PRICKLY PEAR COOLER 2 0.00 100608 12 0 0
103051386 8392776 14 284208 CLASSY CHOCOLATE POUND CAKE MI 2 0.00 100608 12 0 0
103051386 8392776 15 285208 CILANTRO AND OLIVE SALSA 2 0.00 100608 12 0 0
103051386 8392776 16 286208 CRAZY CUCUMBER SPICY DIP MIX 2 0.00 100608 12 0 0
103051386 8392776 17 288208 GIDDYUP GUACAMOLE MIX 2 0.00 100608 12 0 0
103051386 8392776 18 289208 MANGO BREEZE MARGARITA MIX 2 0.00 100608 12 0 0
103051386 8392776 19 290208 SOUTHWEST SEASONING SALT 2 0.00 100608 12 0 0
103051386 8392776 20 295208 TEMPTING TACO CHEESE BALL MIX 2 0.00 100608 12 0 0
103051386 8392776 21 518800 BUSINESS BLAST OFF KIT 1 170.00 NULL 0 0 0
103051386 8392776 22 100680 STANDARD SUGGESTION CARD PREPA 1 0.00 518800 21 0 0
103051386 8392776 23 110808 SUCCESS MADE SIMPLE WORKBOOK 1 0.00 518800 21 0 0
103051386 8392776 24 116005 THE STORY OF TASTEFULLY SIMPLE 1 0.00 518800 21 0 0
103051386 8392776 25 116206 THE PARTY DVD 1 0.00 518800 21 0 0
103051386 8392776 26 116306 RECRUITING MADE SIMPLE AUDIO C 1 0.00 518800 21 0 0
103051386 8392776 27 117407 WHAT DO YOU WANT DVD 1 0.00 518800 21 0 0
103051386 8392776 28 124707 RHUBARB STRAWBERRY FRUIT SPREA 1 0.00 518800 21 0 0
103051386 8392776 29 127108 CORN BLACK BEAN SALSA 1 0.00 518800 21 0 0
103051386 8392776 30 129706 VIDALIA ONION DRESSING SAMPLE 1 0.00 518800 21 0 0
103051386 8392776 31 151108 SPINACH AND HERB DIP MIX 1 0.00 518800 21 0 0
103051386 8392776 32 155108 BOUNTIFUL BEER BREAD MIX 1 0.00 518800 21 0 0
103051386 8392776 33 157108 NANAS APPLE CAKE MIX 1 0.00 518800 21 0 0
103051386 8392776 34 165107 SEASONED SALT 1 0.00 518800 21 0 0
103051386 8392776 35 165704 SEASONED SALT SAMPLE 4 0.00 518800 21 0 0
103051386 8392776 36 168006 PERFECTLY POTATO CHEDDAR SOUP 1 0.00 518800 21 0 0
103051386 8392776 37 175108 ABSOLUTELY ALMOND POUND CAKE M 1 0.00 518800 21 0 0
103051386 8392776 38 209707 DRIED TOMATO AND GARLIC PESTO 4 0.00 518800 21 0 0
103051386 8392776 39 232106 FIESTA PARTY DIP MIX 1 0.00 518800 21 0 0
103051386 8392776 40 235707 PRICKLY PEAR COOLER SAMPLE 4 0.00 518800 21 0 0
103051386 8392776 41 236707 HONEY TERIYAKI SAUCE SAMPLE 6 1 0.00 518800 21 0 0
103051386 8392776 42 267107 KEY LIME CHEESE BALL MIX 1 0.00 518800 21 0 0
103051386 8392776 43 285708 CILANTRO AND OLIVE SALSA SAMPL 1 0.00 518800 21 0 0
103051386 8392776 44 292708 ORANGE DREAM CHILLER SAMPLE 4 0.00 518800 21 0 0
103051386 8392776 45 321706 BLUE AZTEC CHIPS SAMPLE 4 0.00 518800 21 0 0
103051386 8392776 46 357106 THE TRIO 1 0.00 518800 21 0 0
103051386 8392776 47 384708 TWISTY GRAHAMS SAMPLE 4 0.00 518800 21 0 0
103051386 8392776 48 432070 DELECTABLE DESSERTS 1 0.00 518800 21 1 0
103051386 8392776 49 100294 DELECTABLE DESSERTS GIFT BOX 1 0.00 432070 48 0 0
103051386 8392776 50 100295 DELECTABLE DESSERTS GIFT PACK 1 0.00 432070 48 0 0
103051386 8392776 51 158106 TRUFFLE FUDGE BROWNIE MIX 1 0.00 432070 48 0 0
103051386 8392776 52 175108 ABSOLUTELY ALMOND POUND CAKE M 1 0.00 432070 48 0 0
103051386 8392776 53 388108 CREAMY CARAMEL SAUCE 1 0.00 432070 48 0 0
103051386 8392776 54 432071 DELECTABLE DESSERTS ENVELOPE W 1 0.00 432070 48 0 0
103051386 8392776 55 780070 LEAF ADORNMENT 1 0.00 432070 48 0 0
103051386 8392776 56 501080 CATALOG STANDARD SPRING SUMMER 2 0.00 518800 21 0 0
103051386 8392776 57 503080 ORDER FORM CLIENT SPRING SUMME 1 0.00 518800 21 0 0
103051386 8392776 58 505080 PLANNING YOUR PARTY SPRING SUM 1 0.00 518800 21 0 0
103051386 8392776 59 514080 WHAT DO YOU WANT PACKET 2 FO 2 0.00 518800 21 0 0
103051386 8392776 60 501081 CATALOG STANDARD SPRING SUMMER 4 0.00 514080 59 0 0
103051386 8392776 61 514062 BURGUNDY FOLDER BULK 4 0.00 514080 59 0 0
103051386 8392776 62 517071 WHAT DO YOU WANT RECRUITING BR 4 0.00 514080 59 0 0
103051386 8392776 63 968080 PARTY TO SUCCESS BROCHURE 2008 4 0.00 514080 59 0 0
103051386 8392776 64 515080 PARTY SCRIPT CARDS INCLUDES SP 1 0.00 518800 21 0 0
103051386 8392776 65 515108 LAMIN PHOTO STANDARD SUGG CARD 1 0.00 518800 21 0 0
103051386 8392776 66 515208 LAMIN PHOTO SPRING SUMMER SUGG 1 0.00 518800 21 0 0
103051386 8392776 67 517070 WHAT DO YOU WANT RECRUITING BR 1 0.00 518800 21 0 0
103051386 8392776 68 554080 INVITATION LARGE SPRING SUMMER 1 0.00 518800 21 0 0
103051386 8392776 69 567080 ORDER FORM HOST SPRING SUMMER 1 0.00 518800 21 0 0
103051386 8392776 70 582050 CONSULTANT GUIDE 1 0.00 518800 21 0 0
103051386 8392776 71 582070 OPEN ME FIRST ENVELOPE/2 ENVEL 1 0.00 518800 21 0 0
103051386 8392776 72 602001 SOUFFLE CUPS 2 OZ 250 PK 1 0.00 518800 21 0 0
103051386 8392776 73 603004 TASTER SPOONS 500PK 1 0.00 518800 21 0 0
103051386 8392776 74 605001 SOUFFLE CUP 5.5 OZ 250 PK 1 0.00 518800 21 0 0
103051386 8392776 75 605002 SOUFFLE CUP LID 5.5 OZ 100 PK 1 0.00 518800 21 0 0
103051386 8392776 76 609008 PLASTIC GLOVES 100PK 1 0.00 518800 21 0 0
103051386 8392776 77 658070 BLACK SAMPLING PLATES WITH WEL 1 0.00 518800 21 0 0
103051386 8392776 78 969160 REORDER REMINDER CARDS 24/PK 2 0.00 518800 21 0 0
103051386 8392776 79 432070 DELECTABLE DESSERTS 1 31.95 NULL 0 1 0
103051386 8392776 80 100294 DELECTABLE DESSERTS GIFT BOX 1 0.00 432070 79 0 0
103051386 8392776 81 100295 DELECTABLE DESSERTS GIFT PACK 1 0.00 432070 79 0 0
103051386 8392776 82 158106 TRUFFLE FUDGE BROWNIE MIX 1 0.00 432070 79 0 0
103051386 8392776 83 175108 ABSOLUTELY ALMOND POUND CAKE M 1 0.00 432070 79 0 0
103051386 8392776 84 388108 CREAMY CARAMEL SAUCE 1 0.00 432070 79 0 0
103051386 8392776 85 432071 DELECTABLE DESSERTS ENVELOPE W 1 0.00 432070 79 0 0
103051386 8392776 86 780070 LEAF ADORNMENT 1 0.00 432070 79 0 0
The issue is when the Exploding = 1 I need to show the underling children record (ParentLineItem = LineItemID)
If GiftPack = 1 THen I need to see just the Parent row and none of the children: DELECTABLE DESSERTS
But needs to be all done in one recordset.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
May 28, 2008 at 10:50 am
I think I am working myself in to this:
Took the previous #Temp1 and filtered outthe top level kit:
Select * From #Temp1 t1
WHERE ParentLineItemID = 0
OrderGroupNumber OrderNumber LineItemId ItemNumber ItemDescription Quantity LineItemTotal ParentItemNumber ParentLineItemID GiftPack ExplodingKit
---------------- ----------- ----------- ---------- ------------------------------ ----------- ------------------------------ ---------------- ---------------- -------- ------------
103051386 8392776 1 100570 THE PERFECT 10 1 75.99 NULL 0 0 1
103051386 8392776 12 100608 SAVOR SUMMER 2 119.98 NULL 0 0 1
103051386 8392776 21 518800 BUSINESS BLAST OFF KIT 1 170.00 NULL 0 0 0
103051386 8392776 79 432070 DELECTABLE DESSERTS 1 31.95 NULL 0 1 0
Now I have these I would need to circle back to the orders.OrderFormLineItem table that contain the child information but I would only need the children information for the records that have ExplodingKit = 1 otherwise just the parent record.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply