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?Any chance you could add a column to use as a void flag? This would
make filtering this item much less cumbersome...|||nope. I can't make structural changes like that.|||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.|||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...|||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.|||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.|||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 ...|||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.|||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...|||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

query based on the top MTD sales

hello,
I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group

Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales

Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...

Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...

Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: 1,2,3,4,5,6,7,8,...

Thank youhello,
I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group

Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales
Each Fabric group has to be orderd by the Top MTD.

Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f,g...
StyleNum: 1,2,3,4,5,6...

Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f,g...
StyleNum: 1,2,3,4,5,6...

Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: a,b,c,d,e,f,g...
StyleNum: 1,2,3,4,5,6...|||you need to post some transaction data and the expected output of the query for us to understand where exactly the problem is.|||Hi

I'm afraid your description is a little breathless and difficult to follow. I suspect this is why you have not yet had a response. Please could you write in natural English what purpose the query is to serve?

Ta|||Hello,
Attached is a data file in which I need a sort as I said below.

Thank you|||Query has to show data in alphabetical order for each group (Sales Group, Product Group). Then the Fabric Group has to be at the highest order first based on the MDT Sales and then the Fabric Group has to be grouped by description itself.
Then style number and a color code.

Thank you

Sales Group Product Group FabricGroup STYLE NUM Retail Price WTD_Unit WTD_Sale WTD Avg Sales MTD_Unit MTD_Sale MTD_Avg_Sales YTD_Units YTD_Sales YTD_Avg_Sales
ADULT ACTIVE SUPPLEX 1561 $30.00 25 $716.40 $28.66 101 $2,975.40 $29.46 626 $17,896.50 $28.59
ADULT ACTIVE TRIGROUP 1677 $75.00 6 $344.34 $57.39 39 $2,282.02 $58.51 24 $1,287.60 $53.65
ADULT ACTIVE TRIGROUP 1677 $75.00 6 $344.34 $57.39 39 $2,282.02 $58.51 100 $5,755.44 $57.55
ADULT ACTIVE TRIGROUP 1677 $75.00 2 $117.58 $58.79 34 $2,009.67 $59.11 38 $2,125.20 $55.93
ADULT ACTIVE TRIGROUP 1677 $75.00 2 $117.58 $58.79 34 $2,009.67 $59.11 126 $7,229.39 $57.38
ADULT ACTIVE SUPPLEX 1562 $31.50 11 $343.35 $31.21 65 $1,997.73 $30.73 585 $17,091.59 $29.22
ADULT ACTIVE SUPPLEX 1561 $30.00 8 $223.20 $27.90 35 $1,024.80 $29.28 154 $4,335.60 $28.15
ADULT ACTIVE COTN LYCRA 8150 $21.00 8 $168.00 $21.00 49 $1,019.34 $20.80 404 $8,098.44 $20.05
ADULT ACTIVE YOGA CVC 5720 $33.00 11 $327.69 $29.79 31 $935.55 $30.18 220 $6,721.77 $30.55
ADULT ACTIVE SUPPLEX 5145 $39.00 6 $221.91 $36.99 23 $873.99 $38.00 212 $7,781.67 $36.71
ADULT ACTIVE SUPPLEX 7360 $23.00 14 $318.78 $22.77 39 $868.48 $22.27 178 $3,822.37 $21.47
ADULT ACTIVE SUPPLEX 1561 $30.00 6 $179.40 $29.90 29 $859.20 $29.63 35 $1,037.40 $29.64
ADULT ACTIVE TRIGROUP 1847 $48.00 2 $95.04 $47.52 17 $797.76 $46.93 25 $1,168.32 $46.73
ADULT ACTIVE YOGA CVC 8298 $42.00 3 $115.50 $38.50 19 $761.46 $40.08 177 $6,994.68 $39.52
ADULT ACTIVE SUPPLEX 7111 $36.00 7 $232.92 $33.27 22 $752.76 $34.22 98 $3,303.36 $33.71
ADULT ACTIVE TRIGROUP 1672 $48.00 0 $0.00 $0.00 18 $715.82 $39.77 74 $2,806.50 $37.93
ADULT ACTIVE TRIGROUP 1671 $44.00 2 $69.98 $34.99 19 $660.61 $34.77 82 $2,680.23 $32.69
ADULT ACTIVE STRETCH COTN 8202 $17.00 13 $209.95 $16.15 39 $635.97 $16.31 103 $1,633.70 $15.86
ADULT ACTIVE COTN LYCRA 8379 $36.00 2 $71.28 $35.64 19 $635.76 $33.46 175 $5,721.48 $32.69
ADULT ACTIVE YOGA CVC 5369 $36.00 5 $177.12 $35.42 18 $626.40 $34.80 310 $10,296.72 $33.22
ADULT ACTIVE COTN LYCRA 8166 $30.00 0 $0.00 $0.00 22 $625.20 $28.42 99 $2,781.60 $28.10
ADULT ACTIVE TRIGROUP 1845 $75.00 0 $0.00 $0.00 8 $597.00 $74.63 19 $1,401.00 $73.74
ADULT ACTIVE SUPPLEX 1561 $30.00 3 $90.00 $30.00 20 $596.40 $29.82 28 $817.20 $29.19
ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 3 $35.49 $11.83
ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 10 $193.60 $19.36
ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 21 $293.65 $13.98
ADULT DANCE NYLON 9090 $15.50 1 $8.81 $8.81 4 $35.42 $8.86 4 $35.42 $8.86
ADULT DANCE NYLON 9090 $15.50 1 $8.81 $8.81 4 $35.42 $8.86 13 $188.79 $14.52
ADULT DANCE SATIN TOUCH 2809 $36.00 0 $0.00 $0.00 1 $35.28 $35.28 6 $207.36 $34.56
ADULT DANCE SATIN TOUCH 2812 $36.00 0 $0.00 $0.00 1 $35.28 $35.28 14 $483.48 $34.53
ADULT DANCE NYLON 9090 $15.50 0 $0.00 $0.00 4 $35.24 $8.81 17 $244.90 $14.41
ADULT DANCE NYLON 9090 $15.50 0 $0.00 $0.00 4 $35.24 $8.81 18 $152.02 $8.45
ADULT DANCE BODY SCULPT 2813 $35.00 0 $0.00 $0.00 1 $35.00 $35.00 9 $300.30 $33.37
ADULT DANCE BODY SCULPT COLLECTION (2006) 2813 $35.00 0 $0.00 $0.00 1 $35.00 $35.00 11 $359.45 $32.68|||Dupe post
http://www.dbforums.com/showthread.php?t=1217893

I still don't get it. There is a sticky at the top of this forum. It asks you to:
Post table DDL
Sample data in DML format
Expected results

It also explains how to do any of these if you don't know how. Please could you do this?|||FYI (not the OP) - this is also live in SQL Server:
http://www.dbforums.com/showthread.php?t=1217908

:D|||SalesGroup Text 255
ProductGroup Text 255
FabricGroup Text 255
TYPEYR Text 5
STYLE_NUM Text 50
STYLE_DESC Text 255
COLORCODE Text 3
COLORD Text 255
Retail Price Currency 8
WTD_Units_05_25_2006 Double 8
WTD_Sales_05_25_2006 Currency 8
WTD_Avg_Sales_05_25_2006 Currency 8
MTD_Units_May 2006 Double 8
MTD_Sales_May 2006 Currency 8
MTD_Avg_Sales_May 2006 Currency 8
YTD_Units_2006 Double 8
YTD_Sales_2006 Currency 8
YTD_Avg_Sales_2006 Currency 8

SalesGroup (In Alphabetical Order - Adult, Girls, Plus, Mens, Irrs),
for each Sales gr ther are the
ProductGroup (Alphabetical Order - Active, Dace, Legwr, Prvt),
For each Sales Gr and a Prod Group there are the
FabricGroups (has to apear as the highest total first for each FabricGroup based on the MTD Sales (Suplex, Cotton, Cotton LCRY)
and then it has to group the FabricGroup together. (If I have 10 entries for the Supplex and it is at the highest total, then the highest total has to be first and the rest apper after)
StyleID (1,2,3,4,5,6,7..),
Color (01,02,03,04,05),
RetailAmount,
WeeklySales,
MonthlySales
YTDSales

Thank you so much.|||the thing to do when there's a cross post is not to put pointers from one to the other, but to merge the threads, as i have done here

that the merged threads may be more confusing than separate threads is not our problem but the original poster's|||the thing to do when there's a cross post is not to put pointers from one to the other, but to merge the threads, as i have done hereLol - ya :D The pointers were more to flag up the posts to the Orange users than to create some complex web of a thread.

Although I do like to alternate my responses from one thread to another when I do get involved. That's just mischief though.