Post reply

Selecting value(s) from a field that will equal a selected value

  • December 10, 2010 at 9:48 am

    #244084

    Hello.

    I apologize if the question is not in a proper format.

    I am trying to find a way (Via SQL of course) to accomplish the following.

    I have a transaction table (InventTrans) that contains a field named Qty. This (QTY) field contains values from (example -100.00 to 100.00). Our production folks want to know if they can find the value of (Example 50.00) from the QTY field. Any combination of values from the QTY field that will add up to 50.

    So if there is a transaction QTY value of 50 there is a match, however if there are multiple transactions totalling 50 (example 1.5 and 48.5) then the sum of these two transactions also add up to 50.

    We can breakdown the transaction values by date to minimize the number of records

    Any help would be greatly appreciated.

    Marc

  • December 10, 2010 at 1:09 pm

    #1261195

    This should handle it for you. It shows every combination of the Qty field that when added to every other combination, comes up to 50.

    -- See how you start off by actually creating a table and then

    -- inserting the data into it? Your doing this makes it a lot easier

    -- for all of us volunteers to help you. So, help us help you.

    -- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

    -- for more details on how to do all of this.

    DECLARE @test-2 TABLE (RowID INT IDENTITY, Qty numeric(5,2));

    -- add numbers from -100 to +100 (including 0)

    INSERT INTO @test-2 (Qty)

    SELECT N-101 FROM (SELECT TOP 201 N = ROW_NUMBER() OVER (ORDER BY object_id) FROM master.sys.columns) tally WHERE N <= 201;

    -- add numbers from -99.5 to 100.5

    INSERT INTO @test-2 (Qty)

    SELECT N-100.5 FROM (SELECT TOP 201 N = ROW_NUMBER() OVER (ORDER BY object_id) FROM master.sys.columns) tally WHERE N <= 201;

    -- we now have numbers from -100 to 100.5, in .5 increments

    -- show the rows that add up to any other row to equal 50

    SELECT t1.*, t2.Qty --, t3.Qty

    FROM @test-2 t1

    CROSS APPLY (SELECT Qty from @test-2) t2

    --CROSS APPLY (SELECT Qty from @test-2) t3

    WHERE t1.Qty + t2.Qty /*+ t3.Qty*/ = 50

    ORDER BY t1.Qty;

    Note the remarked out code... if you want to do any three numbers that add up to 50, unremark those lines (but it will take longer to run!)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 2 posts - 1 through 1 (of 1 total)

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