need help with this query please?

  • 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.

  • 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"

  • ooh that is slick - lemme give it a try!

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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.

  • 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.

  • So you're looking to match, one for one, your sales & refunds, and examine what's left?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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.

  • 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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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?

    😎

  • If not, this will help:

    http://www.sqlservercentral.com/Forums/Topic424213-338-1.aspx

    😀

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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

  • 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 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


    N 56°04'39.16"
    E 12°55'05.25"

  • 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 🙂

  • 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 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

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply