|
nickt56r
Starting Member
1 Posts |
 Posted - 02/19/2013 : 04:42:01
|
I've been tasked with building a report on an unfamiliar database. Most of it is pretty straight forward but the sales items are categorised in a very strange way and I can't get my head around where to start. Any advice or help would be appreciated.
The sales items are in a straight forward table.
Itemid, Catid, Price, PLU
Ie,
5, 58, 6.70, 667
The system has a hierarchical category system but all held within a single table! And this is what I have a problem with. The items CATid points to a low level category, such as 58 soups (catlevel 5). But I need to work out which category of level 2 this belongs to? I can’t even work it out in my head, let along the SQL of it yet.
CATID RightCat Name CatLevel Order DownCat
1 0 SALES 0 1 2
2 5 FOOD 1 2 3
3 0 FOOD 2 3 73
5 12 BAR 1 6 6
6 0 BAR 2 7 81
7 85 house spirits 4 13 0
8 10 WINE 2 22 77
9 30 red wine 4 25 0
10 0 BEER 2 30 78
11 0 draft 4 11 0
12 0 MISC 1 29 91
13 0 MISC 2 34 14
14 0 Misc 3 35 0
20 0 NA BEV 2 15 74
24 25 starters 4 5 57
25 26 mains 4 9 60
26 28 desserts 4 12 0
27 0 add-ons 4 14 0
28 27 specials 4 13 0
29 0 liqueurs 4 17 0
30 83 white wine 4 26 0
32 11 bottles 4 10 0
33 34 modifiers 4 37 0
34 35 non-items 4 38 0
35 36 transformers 4 39 0
36 0 volume mods 4 40 0
57 58 apps 5 6 0
58 59 soups 5 7 0
59 0 salads 5 8 0
60 61 sands/burgers 5 10 0
61 0 entrees 5 11 0
62 63 gls red 5 25 0
63 0 btl red 5 26 0
64 66 gls wt 5 28 0
66 0 btl wt 5 29 0
73 88 Food 3 4 0
74 0 NA Bev 3 16 0
75 93 Spirits 3 12 7
77 0 Wine 3 24 9
78 75 Beer 3 9 32
79 0 RETAIL 2 41 80
80 0 Retail 3 42 0
81 78 Soft Drinks 3 8 0
82 0 cocktails 4 18 0
83 84 rose wine 4 27 0
84 0 sparkling 4 28 0
85 86 premium 1 4 14 0
86 87 premium 2 4 15 0
87 29 premium 3 4 16 0
88 0 Hot Beverages 3 5 0
89 0 Room Hire 3 31 0
90 0 Party Boxes 3 33 0
91 92 ROOMS 2 30 89
92 13 PARTY BOX 2 32 90
93 77 Cocktails 3 18 94
94 98 Top 10 4 19 0
95 96 Signature 4 21 0
96 97 Shots 4 22 0
97 0 Non Alc 4 23 0
98 95 Classic 4 20 0
Any help?
|
|
Can anyone help
