I need some help nesting this query

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

  1. am_wg

    am_wg

    Joined:
    Sep 24, 2019
    Messages:
    1
    Likes Received:
    0
    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, Sep 24, 2019
    #1
    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.