Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62

    Unanswered: Query Filtering Entries

    Here's one for you guys:

    I have a table with sales entries in it. To make things simple, I will put it this way:

    There is an entry in the table for every item scanned. There may be some items that get voided out, but there will still be an entry in the table. No problem for the one transaction that was voided, because there is a negative dollar amount which I've already filtered out easily. However, there will still be an entry in the table for the same Item that got voided with a positive dollar amount. Make sense? I want to filter out the one entry with the positive dollar amount because it's not really a sale. It was voided in another entry in the table... So, If I come to the store, and buy a pack of gum for $0.99. The cashier scans it, then I decide I don't want it, so the cashier voids it off. I've got two entries in the table for that pack of gum. One for $0.99 where the cashier scanned, and one for -$0.99 where the cashier voided it off. I've got to get rid of the positive transaction.



    Too confusing?

  2. #2
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Re: Query Filtering Entries

    Any chance you could add a column to use as a void flag? This would
    make filtering this item much less cumbersome...

  3. #3
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62

    re: adding column

    nope. I can't make structural changes like that.

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Query Filtering Entries

    Originally posted by AnSQLQuery
    Here's one for you guys:

    I have a table with sales entries in it. To make things simple, I will put it this way:

    There is an entry in the table for every item scanned. There may be some items that get voided out, but there will still be an entry in the table. No problem for the one transaction that was voided, because there is a negative dollar amount which I've already filtered out easily. However, there will still be an entry in the table for the same Item that got voided with a positive dollar amount. Make sense? I want to filter out the one entry with the positive dollar amount because it's not really a sale. It was voided in another entry in the table... So, If I come to the store, and buy a pack of gum for $0.99. The cashier scans it, then I decide I don't want it, so the cashier voids it off. I've got two entries in the table for that pack of gum. One for $0.99 where the cashier scanned, and one for -$0.99 where the cashier voided it off. I've got to get rid of the positive transaction.

    Too confusing?
    Not too confusing....but it will be easier if you are running this through several tables and views.

    Assuming that the transactions have a date time stamp and/or indexing column on it, and item number you could work it this way

    Assuming that the sales_table is something like this

    Index Item_Number Sales_Amt ......

    Make a view called voided_sales which just sees the voided transactions and the sales_amt*-1

    Insert into Voided_Indexes
    Select max(index)
    From sales_table
    group by index
    having index < (select index from sales_table, voided_sales
    where sales_table.sales_Amt = voided_sales.Sales_Amt
    and sales_table.Item_Number = voided_sales.Item_Number)

    Once you get the voided_indexes list built then your query becomes
    Select *
    From sales_table
    where index not in (select index from voided_sales)
    and index not in (select index from voided_Indexes)

    There are probably more efficient ways of doing it, but this is a quick scratch off the top of my head.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  5. #5
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Re: Query Filtering Entries

    select * from sales where saleAmt not in (select saleAmt * -1 from sales1)

    The subquery may not make it efficient but this is probably the simplest
    form of query...

  6. #6
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Query Filtering Entries

    Originally posted by rocket39
    select * from sales where saleAmt not in (select saleAmt * -1 from sales1)

    The subquery may not make it efficient but this is probably the simplest
    form of query...
    The problem with that is that if the item subsequently sells it will be blocked out as well as the valid void.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  7. #7
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Re: Query Filtering Entries

    Originally posted by jimpen
    The problem with that is that if the item subsequently sells it will be blocked out as well as the valid void.
    Yep, I guess I was assuming that there would be some piece of information that would relate the void to the sale transaction.

  8. #8
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62
    It just seems like there should be a more simple solution. (I haven't exactly figured out the creating a view suggestion just yet.)

    It seems like i should be able to say if there is an entry that matches this TransactionID # and this dollar amt*-1 within the query, so that I'm using whatever transaction Id the query is on at that moment..don't count it. I know that is kind of funny sounding, but ....

  9. #9
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by AnSQLQuery
    It just seems like there should be a more simple solution. (I haven't exactly figured out the creating a view suggestion just yet.)

    It seems like i should be able to say if there is an entry that matches this TransactionID # and this dollar amt*-1 within the query, so that I'm using whatever transaction Id the query is on at that moment..don't count it. I know that is kind of funny sounding, but ....
    Create or Replace View voided_sales as Select Index, Item_Number, (Sales_Amt*-1) As Voided_Sales_Amt, ... or something similar...I've been doing more Oracle this week than MSSQL.

    I rushed it should be
    sales_table.sales_Amt = voided_sales.Voided_Sales_Amt

    The reason for the view is trying to keep multiple recursive queries straight is a royal PITA. The reason for the max index is to get the most recent sale to match the return. That is not a guarentee match to the sale, but it will at least get it out of the totals.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  10. #10
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62
    This just came to me, and I don't know why I didn't think of it before, but I would think that I could just get rid of the filter that takes out the voided transactions all together.

    Since the voided transactions have a negative dollar amount, when I do a SUM() on the Dollars the negative amounts will cancel out, and The dollar figure will be correct. (I think anyway)

    As for the number of sales, all I have to do is count the number of voided transactions, and subtract that amount from the total number of sales.

    I'll let you know if this works....

  11. #11
    Join Date
    Jul 2003
    Posts
    21

    Wink Re: Query Filtering Entries

    you can use self join for that, join the table with itself using the product id, price (with different signs) to get the records you want. thats the most simple - elegant way i can think of though im not sure how hot it is performance wise.

    consider flaggnig voided records when inserting the negative valued record using either a trigger or (much better) in the sproc performing the insertion.

    Good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •