I need some help nesting this query

Discussion in 'General MSSQL Discussion' started by am_wg, Sep 24, 2019.

  1. am_wg


    Sep 24, 2019
    Likes Received:
    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
    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.
    am_wg, Sep 24, 2019
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.