February 5, 2008 at 9:57 am
i have a database that is a list of items sold. it includes the date it was sold, the item description, quantity and total charge.
there are 2 things i need to get:
1) the top 10 highest charges
2) the top 10 most frequently sold products (based on product description field)
the complication is that when items are returned, they are entered into this same table but with a negative number for the charge amount.
so for instance if i sold widget A on January 1st for 4.99 and it was returned on january 3rd. then i would have the following 2 lines in the table:
1/1/2008, Widget A, 1, 4.99
1/3/2008, Widget A, 1, -4.99
so if i want to find the top 10 highest charges from my table but not count the returns, then i need to not only exclude the line with the negative charge amount, i need to exclude its matching line for the original sale.
i am not sure exactly how to build a query that would accomplish this.
the kind of pseudo code for it that i am envisioning is as follows:
Find the top 10 highest Charge Amounts
WHERE Charge Amount is not negative
IF the charge amount has a record in the table with a matching amount (only negative), and a matching description, then exclude this record also.
sorry i am just really confused on this one and would really appreciate some guidance.
February 5, 2008 at 10:12 am
Something similar to
SELECT TOP 10Col1,
SUM(Col2)
FROMTable1
GROUP BYCol1
HAVINGSUM(Col2) > 0
ORDER BYSUM(Col2) DESC
N 56°04'39.16"
E 12°55'05.25"
February 5, 2008 at 10:20 am
ooh that is slick - lemme give it a try!
February 5, 2008 at 10:43 am
This looks suspiciously like homework to me! However...here's half of what you need...
DROP TABLE #Widgets
CREATE TABLE #Widgets (TranDate DATETIME, Product VARCHAR(10), quantity INT, value MONEY)
INSERT INTO #Widgets (TranDate, Product, quantity, value)
SELECT '1/1/2008', 'Widget A', 2, 4.99 UNION ALL
SELECT '1/3/2008', 'Widget A', 2, -4.99 UNION ALL
SELECT '2/1/2008', 'Widget A', 1, 4.99 UNION ALL
SELECT '3/1/2008', 'Widget A', 1, 4.99 UNION ALL
SELECT '4/1/2008', 'Widget A', 1, 4.99 UNION ALL
SELECT '5/1/2008', 'Widget A', 1, 4.99 UNION ALL
SELECT '6/1/2008', 'Widget A', 1, 4.99 UNION ALL
SELECT '7/1/2008', 'Widget A', 1, 4.99 UNION ALL
SELECT '8/1/2008', 'Widget A', 1, 4.99 UNION ALL
SELECT '1/1/2008', 'Widget B', 1, 2.99 UNION ALL
SELECT '2/1/2008', 'Widget B', 1, 2.99 UNION ALL
SELECT '3/1/2008', 'Widget B', 1, 2.99 UNION ALL
SELECT '4/1/2008', 'Widget B', 1, 2.99 UNION ALL
SELECT '5/1/2008', 'Widget B', 1, -2.99 UNION ALL
SELECT '1/1/2008', 'Widget C', 10, 1.99 UNION ALL
SELECT '2/1/2008', 'Widget C', 10, 1.99
SELECT w1.Product, w1.quantity - ISNULL(w2.quantity, 0) AS netquantity, w1.value + ISNULL(w2.value, 0) AS netvalue
FROM
(SELECT Product, SUM(quantity) AS quantity, SUM(value) AS value
FROM #Widgets w1 WHERE value > 0
GROUP BY Product) w1
LEFT JOIN (SELECT Product, SUM(quantity) AS quantity, SUM(value) AS value
FROM #Widgets w1 WHERE value < 0
GROUP BY Product) w2
ON w2.Product = w1.Product
ORDER BY w1.quantity - ISNULL(w2.quantity, 0) DESC
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 5, 2008 at 10:47 am
ok now tha ti am looking at this i was wondering if you could just clarify something.
so it looks like what you were gettign at with the proposed query is grouping together all the transactions for each Product Description and then taking the sum of their amount, right?
lets say i have the following data:
1, 1/1/2008, Widget A, 15, 74.85
2, 1/3/2008, Widget A, -15, -74.85
3, 1/1/2008, Widget A, 3, 14.97
4, 1/3/2008, Widget A, 1, 4.99
5, 1/4/2008, Widget A, 10, 49.90
6, 1/6/2008, Widget A, 1, 4.99
7, 1/4/2008, Widget B, 1, 18.99
8, 1/6/2008, Widget C, 2, 37.98
9, 1/4/2008, Widget D, 1, 1.99
10, 1/6/2008, Widget E, 1, -1.99
11, 1/6/2008, Widget E, 1, 291.99
the results I would be looking for would be
1/6/2008, Widget E, 1, 291.99
1/4/2008, Widget A, 10, 49.90
1/6/2008, Widget C, 2, 37.98
1/4/2008, Widget B, 1, 18.99
1/1/2008, Widget A, 3, 14.97
1/6/2008, Widget A, 1, 4.99
but it sounds like with your proposed query, it would group together all the Widget A's and get a sum of their charge amount - so in the example it would be 74.85 and woudl give the result of $74.85(the total of all widget A charges)
but i am looking for the highest individual charge, not the total of the charges from each item.
maybe i am misunderstanding - please correct me if i am wrong.
February 5, 2008 at 10:50 am
no, trust me, its not homework - i wish it was. it is for my job - a healthcare facility that uses another area hospital's pharmacy services and we get the data in fixed width text files which we DTS into a SQL server 200 table. and my boss is asking me to find this out for him but i am really not a DB guy - i am more of a web/interface designer that is beign forced to be a jack of all trades.
anyway - i really appreciate the help.
February 5, 2008 at 10:52 am
So you're looking to match, one for one, your sales & refunds, and examine what's left?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 5, 2008 at 10:56 am
Chris Morris (2/5/2008)
So you're looking to match, one for one, your sales & refunds, and examine what's left?
i am looking to find the top 10 highest individual charges (individual records) that were not returned.
and really i'm not picky - it doesn't matter too much if it is in SQL, crystal reports, access or whatever but i figured SQL would be the best starting point.
February 5, 2008 at 10:56 am
Blair Dee (2/5/2008)
no, trust me, its not homework - i wish it was. it is for my job - a healthcare facility that uses another area hospital's pharmacy services and we get the data in fixed width text files which we DTS into a SQL server 200 table. and my boss is asking me to find this out for him but i am really not a DB guy - i am more of a web/interface designer that is beign forced to be a jack of all trades.anyway - i really appreciate the help.
No worries mate I work for Nuffield and "get the data in fixed width text files which we DTS into a SQL server 200 table" quite frequently. Let's sort this out.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 5, 2008 at 11:06 am
Is there any additional data in the file you get, such as PO or Order numbers that can be used to tie sales and returns together?
😎
February 5, 2008 at 11:30 am
If not, this will help:
http://www.sqlservercentral.com/Forums/Topic424213-338-1.aspx
😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 5, 2008 at 11:43 am
Alter table dbo.Sales
add TransactionID int identity, RefundID int null references dbo.Sales(transactionid)
create index IDX_SalesRefunds on dbo.sales (refundid)
create index IDX_SalesTransactions on dbo.sales(transactionid)
create index IDX_SalesSale on dbo.sales(description, amount, date, transactionid)
update dbo.sales
set refundid = transactionid
from dbo.sales s2
where sales.description = s2.description
and sales.amount = -1 * s2.amount
and sales.date < s2.date
declare @Rows int
select @rows = 1
while @rows > 0
begin
update dbo.sales
set refundid = null
where transactionid in
(select max(transactionid)
from dbo.sales
group by refundid
having count(*) > 1)
select @rows = @@rowcount
end
update dbo.sales
set refundid = transactionid
from dbo.sales s3
where sales.description = s2.description
and sales.amount = -1 * s2.amount
and sales.date < s2.date
and transactionid not in
(select refundid
from dbo.sales
where refundid is not null)
-- Use to find if any refunds have not been matched to a prior sale
select *
from dbo.sales
where amount < 0
and transactionid not in
(select refundid
from dbo.sales
where refundid is not null)
select *
from dbo.sales
where transactionid in
(select refundid
from dbo.sales
group by refundid
having count(*) > 1)
-- Once the above checks are clean
select top 10 *
from dbo.sales
where refundid is null
order by amount desc
It's not the cleanest code ever written. Uses a loop, etc. But without ID numbers to tie refunds to sales in the raw data, I think it'll be the best method for matching them up.
You'll have to modify it to use the actual table name and column names. It won't work very well as a proc, but if you use it to clean up the data when you import it, then you can run reports on the data after you've tagged all the refunds correctly.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 12:11 pm
This?
DECLARE @Sample TABLE (Gadget CHAR(8), Price MONEY)
INSERT@Sample
SELECT'Widget A', 74.85 UNION ALL
SELECT'Widget A', -74.85 UNION ALL
SELECT'Widget A', 14.97 UNION ALL
SELECT'Widget A', 4.99 UNION ALL
SELECT'Widget A', 49.90 UNION ALL
SELECT'Widget A', 4.99 UNION ALL
SELECT'Widget B', 18.99 UNION ALL
SELECT'Widget C', 37.98 UNION ALL
SELECT'Widget D', 1.99 UNION ALL
SELECT'Widget E', -1.99 UNION ALL
SELECT'Widget E', 291.99
SELECT TOP 6Gadget,
MAX(Price) AS Price
FROM@Sample
GROUP BYGadget,
ABS(Price)
--AND MAX(Price) >= 0
HAVINGSUM(Price) > 0
ORDER BYMAX(Price) DESC
Output is
GadgetPrice
Widget E291,99
Widget A49,90
Widget C37,98
Widget B18,99
Widget A14,97
Widget A4,99
Which is what OP wanted
Blair Dee (2/5/2008)
the results I would be looking for would be1/6/2008, Widget E, 1, 291.99
1/4/2008, Widget A, 10, 49.90
1/6/2008, Widget C, 2, 37.98
1/4/2008, Widget B, 1, 18.99
1/1/2008, Widget A, 3, 14.97
1/6/2008, Widget A, 1, 4.99
N 56°04'39.16"
E 12°55'05.25"
February 6, 2008 at 8:45 am
Lynn Pettis (2/5/2008)
Is there any additional data in the file you get, such as PO or Order numbers that can be used to tie sales and returns together?😎
unfortunately no. the joys of dealing with an old crappy system 🙂
February 6, 2008 at 8:55 am
looks like this could work but i am confused by the following line of the query:
--AND MAX(Price) >= 0
what is with the two minuses. if i leave them in, query analyzer removes the line. if i take them out i get a syntax error.
BTW - I really appreciate everyones replies - sorry i didn't get back to this sooner - i ended gettign sidetracked into yet another project yesterday afternoon.
Peso (2/5/2008)
This?
DECLARE @Sample TABLE (Gadget CHAR(8), Price MONEY)
INSERT@Sample
SELECT'Widget A', 74.85 UNION ALL
SELECT'Widget A', -74.85 UNION ALL
SELECT'Widget A', 14.97 UNION ALL
SELECT'Widget A', 4.99 UNION ALL
SELECT'Widget A', 49.90 UNION ALL
SELECT'Widget A', 4.99 UNION ALL
SELECT'Widget B', 18.99 UNION ALL
SELECT'Widget C', 37.98 UNION ALL
SELECT'Widget D', 1.99 UNION ALL
SELECT'Widget E', -1.99 UNION ALL
SELECT'Widget E', 291.99
SELECT TOP 6Gadget,
MAX(Price) AS Price
FROM@Sample
GROUP BYGadget,
ABS(Price)
--AND MAX(Price) >= 0
HAVINGSUM(Price) > 0
ORDER BYMAX(Price) DESC
Output is
GadgetPrice
Widget E291,99
Widget A49,90
Widget C37,98
Widget B18,99
Widget A14,97
Widget A4,99
Which is what OP wanted
Blair Dee (2/5/2008)
the results I would be looking for would be1/6/2008, Widget E, 1, 291.99
1/4/2008, Widget A, 10, 49.90
1/6/2008, Widget C, 2, 37.98
1/4/2008, Widget B, 1, 18.99
1/1/2008, Widget A, 3, 14.97
1/6/2008, Widget A, 1, 4.99
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply