Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nickt56r
Starting Member
1 Post |
 Posted - 2013-02-19 : 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, PLUIe,5, 58, 6.70, 667The 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 DownCat1 0 SALES 0 1 22 5 FOOD 1 2 33 0 FOOD 2 3 735 12 BAR 1 6 66 0 BAR 2 7 817 85 house spirits 4 13 08 10 WINE 2 22 779 30 red wine 4 25 010 0 BEER 2 30 7811 0 draft 4 11 012 0 MISC 1 29 9113 0 MISC 2 34 1414 0 Misc 3 35 020 0 NA BEV 2 15 7424 25 starters 4 5 5725 26 mains 4 9 6026 28 desserts 4 12 027 0 add-ons 4 14 028 27 specials 4 13 029 0 liqueurs 4 17 030 83 white wine 4 26 032 11 bottles 4 10 033 34 modifiers 4 37 034 35 non-items 4 38 035 36 transformers 4 39 036 0 volume mods 4 40 057 58 apps 5 6 058 59 soups 5 7 059 0 salads 5 8 060 61 sands/burgers 5 10 061 0 entrees 5 11 062 63 gls red 5 25 063 0 btl red 5 26 064 66 gls wt 5 28 066 0 btl wt 5 29 073 88 Food 3 4 074 0 NA Bev 3 16 075 93 Spirits 3 12 777 0 Wine 3 24 978 75 Beer 3 9 3279 0 RETAIL 2 41 8080 0 Retail 3 42 081 78 Soft Drinks 3 8 082 0 cocktails 4 18 083 84 rose wine 4 27 084 0 sparkling 4 28 085 86 premium 1 4 14 086 87 premium 2 4 15 087 29 premium 3 4 16 088 0 Hot Beverages 3 5 089 0 Room Hire 3 31 090 0 Party Boxes 3 33 091 92 ROOMS 2 30 8992 13 PARTY BOX 2 32 9093 77 Cocktails 3 18 9494 98 Top 10 4 19 095 96 Signature 4 21 096 97 Shots 4 22 097 0 Non Alc 4 23 098 95 Classic 4 20 0Any help? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-19 : 05:16:29
|
| this should give you a starthttp://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
Can anyone help