I need some help with SQL

[AM_WG]

Hey Everyone,
Our accounting dept wants to add the tax collected or percentage for an item to the Consolidated Menu Item Sales Detail report. I have some experience with SQL, but it's been a long time since I used it on a regular basis and could use some help putting this query together. Below are the queries I put together to determine what tables and fields I need to work with.

select * from micros.mi_def where obj_num='9230188' - Menu item table, var will be supplied by batch process. Obj_num is menu item #
select * from micros.mi_type_class_def where mi_type_seq='116' - mi_type_seq is the menu item class number. The class controls taxes charged for that item.
select * from micros.tax_class_def where tax_class_seq='3' - tax_class is the menu item tax classification
Result:
tax_class_seq,obj_num,name,ob_tax_1_active,ob_tax_2_active,ob_tax_3_active,ob_tax_4_active,ob_tax_5_active,ob_tax_6_active,ob_tax_7_active,ob_tax_8_active,ob_rsvd01,ob_rsvd02,ob_rsvd03,ob_rsvd04,ob_rsvd05,ob_rsvd06,ob_rsvd07,ob_rsvd08,last_updated_by,last_updated_date
3,102,'Wine/Liqu Tax = State+Liq','T','T','F','F','F','F','F','F','F','F','F','F','F','F','F','F',462,1997-06-16 16:06:18.437
the 'T''s about that are in bold say to charge each of those taxes defined in the tax_rate_def table

select * from micros.tax_rate_def - Table with tax rates.

Finally, sum the values that returned True. In this case, both rates in the field percentage from records that have seq 1 and 2 need to be totalled. The sum of the percentages that are marked as true is what I am trying to pull into the report.
select sum(percentage) from micros.tax_rate_def where tax_rate_seq between 1 and 2

We are using the Sybase DB that comes with Micros 5.7 build 5341. All help is appreciated. Thanks.
Andy

 

[AM_WG]

Reposting with better formatting to make it easier to read and understand.

Our accounting dept wants to add the tax collected or percentage for an item to the Consolidated Menu Item Sales Detail report. I have some experience with SQL, but it's been a long time since I used it on a regular basis and could use some help putting this query together. Below are the queries I put together to determine what tables and fields I need to work with. This is the menu item table. The obj_num is the primary key. The value will need to be a variable so it can run this for each item. Menu item table, var will be supplied by report process.

select * from micros.mi_def where obj_num='9230188'
I need the menu item class number to check the tax tables for which ones apply, so I can pass it to the next query. mi_type_seq is the menu item class number. The class controls taxes charged for that item. The item above has a menu class of 116, but this needs to work for whatever class the above item is.

select * from micros.mi_type_class_def where mi_type_seq='116'
tax_class is the menu item tax classification. This defines what taxes apply to that menu item class. Below I am passing '3" because that's the tax classification number for this item.

select * from micros.tax_class_def where tax_class_seq='3'
Result:

tax_class_seq,obj_num,name,ob_tax_1_active,ob_tax_2_active,ob_tax_3_active,ob_tax_4_active,ob_tax_5_active,ob_tax_6_active,ob_tax_7_active,ob_tax_8_active,ob_rsvd01,ob_rsvd02,ob_rsvd03,ob_rsvd04,ob_rsvd05,ob_rsvd06,ob_rsvd07,ob_rsvd08,last_updated_by,last_updated_date
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3,102,'Wine/Liqu Tax = State+Liq',**'T','T'**,'F','F','F','F','F','F','F','F','F','F','F','F','F','F',462,1997-06-16 16:06:18.437
What I really need here is probably an array to capture the values so I can query the percentages in the rate table. It could also work if I had a variable for hold the temp tax rate and retrieve the rate for each one marked true. The 'T''s above in bold are the taxes that need to be charged. Those taxes defined in the tax_rate_def table. I only want to sum the values marked true above.

select * from micros.tax_rate_def
Table with tax rates.

Finally, sum the values that returned True. In this case, both rates in the field percentage from records that have seq 1 and 2 need to be totalled. The sum of the percentages that are marked as true is what I am trying to pull into the report. select sum(percentage) from micros.tax_rate_def where tax_rate_seq between 1 and 2

We are using the Sybase DB that comes with Micros 5.7 build 5341. All help is appreciated. Thanks. Andy

 

[AM_WG]

Sample data from the first table.
17736,8007532,Tws cbl o f c a,112 17737,8007533,Tws pom hat,112 17738,8007534,Tws chn p l f a,112 17739,8007535,Twos open wv sha,112 17740,8521685,Tws octps bnc b,112 17741,8520065,Tws fish dish,112 17742,8520066,Tws shell dish,112 17743,8007536,Bd LT Lmn T 6 B,189 17744,8007537,M U I L&P P 6 B,189 Second data set from second query. 184,701,Tobacco,15 188,778,Market Lotto Not Taxable,4 189,702,Alcohol,15 179,777,Market Lottery Open NoTaxable,4 180,204,Citrus Liquor Item LEVELS,3

Sample data from second table
184,701,Tobacco,15 188,778,Market Lotto Not Taxable,4 189,702,Alcohol,15 179,777,Market Lottery Open NoTaxable,4 180,204,Citrus Liquor Item LEVELS,3

3rd data set provided above.
4th data set.
1,Food Tax 6.5%,Fd Tx 6.5.%,Food Sales,6.5000 2,Alcohol Tax 6.5%,Alc Tax 6.5%,Alc Sales,6.5000 3,Retail Tax 6.5%,Ret Tx 6.5.%,Ret Net Sls,6.5000 4,Food Tax Incl Cafe,Incl Fd T Tx,Incl Fd Sls,6.5000 – am_wg 5 mins ago

SQLAnywhere 17 is the db

 

[SkipVought]

What is your db table structure?

You apparently need to JOIN these tables together, but HOW? What keys are to be related?

Need to know the field names.

May also need to have coherent sample table data that can be joined and yield expected results.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[AM_WG]

Here are some data sets that might help.

select mi_seq, obj_num, name_1, mi_type_seq from micros.mi_def
mi_seq,obj_num,name_1,mi_type_seq
17736,8007532,'Tws cbl o f c a',112
17737,8007533,'Tws pom hat',112
17738,8007534,'Tws chn p l f a',112

The mi_type_seq ties these two tables together.

select mi_type_seq, obj_num, name, tax_class_seq from micros.mi_type_class_def
1,252,Citrus Breeze Food,1
3,225,Citrus Food Open Item,1
5,201,Citrus Liquor Item ,3
7,202,Citrus Liquor Open Item,3

The tax_class_seq is what ties these two

select tax_class_seq, obj_num, name, ob_tax_1_active, ob_tax_2_active, ob_tax_3_active, ob_tax_4_active, ob_tax_5_active, ob_tax_6_active, ob_tax_7_active from micros.tax_class_def
tax_class_seq,obj_num,name,ob_tax_1_active,ob_tax_2_active,ob_tax_3_active,ob_tax_4_active,ob_tax_5_active,ob_tax_6_active,ob_tax_7_active
1,101,'Food Tax 6.5% Tax','T','F','F','F','F','F','F'
2,1090,'NU Alcohol Tax Inclusive','T','F','T','T','T','T','T'
3,102,'Alcohol 6.5% Tax','F','T','F','F','F','F','F'

The tax rate is derived by summing the rates below that are marked T or true above

select tax_rate_seq, name, tax_coll_name, percentage from micros.tax_rate_def
tax_rate_seq,name,tax_coll_name,percentage
1,'Food Tax 6.5%','Fd Tx 6.5.%',6.5000
2,'Alcohol Tax 6.5%','Alc Tax 6.5%',6.5000
3,'Retail Tax 6.5%','Ret Tx 6.5.%',6.5000

 

[SkipVought]

What fields join?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[AM_WG]

I believe I already answered this question with the comments above where I say.

The mi_type_seq ties these two tables together - joined here
The tax_class_seq is what ties these two - joined here
The tax_class_seq ties the last two tables together as well.

 

[SkipVought]

Sorry, there's no tax_class_seq in your last table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[AM_WG]

I am still searching for the connection. I am posting the schema here as you might be able to find it faster than I can. [URL unfurl="true"]https://res.cloudinary.com/engineering-com/image/upload/v1569955554/tips/Micros_DB_Schema_vaxs1e.pdf[/url]

 

[SkipVought]

323 pages!!!
???

I gotta fill my coffee cup and check the most likely parings for the Leagues' Championship Series.

And I think my sock drawer needs re-organization...again.

And I'm almost positive that I'll get an important call, so....gotta go!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Hate to break it tu-ya.

There's no
tax_class_seq
in that file.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[AM_WG]

Thanks for checking Skip. I had to put this on hold for a while because of the holidays etc. I am back on the case and will try to provide the information you were asking about. I hope that sock drawer is looking good now! LOL

 

[SkipVought]

I am back on the case…

..you and Paula Zahn.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[AM_WG]

LOL