×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

I need some help with SQL

I need some help with SQL

I need some help with SQL

(OP)
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

RE: I need some help with SQL

(OP)
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

RE: I need some help with SQL

(OP)
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

RE: I need some help with SQL

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: I need some help with SQL

(OP)
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


RE: I need some help with SQL

What fields join?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: I need some help with SQL

(OP)
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.

RE: I need some help with SQL

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: I need some help with SQL

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: I need some help with SQL

Hate to break it tu-ya.

There's no
tax_class_seq
in that file.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: I need some help with SQL

(OP)
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

RE: I need some help with SQL

Quote:

I am back on the case…

..you and Paula Zahn. 😉

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: I need some help with SQL

(OP)
LOL

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close