Aging Query

  • I have to create a report for aging and i am getting data as

    Qty Age

    -5 0-30Days

    7 31-60Days

    3 61-90 Days

    but i want data as

    Qty Age

    0 0-30Days

    5 31-60Days

    0 61-90 Days

    that means if i get negative value than that should be substracted from the oldest one and if oldest one becomes 0 then it should look out for the second last and so on.

    if anyone can provide the SQL query that will be really helpful

    Please help

    Thanks in advance

    Ravi

  • Ravi (8/17/2009)


    I have to create a report for aging and i am getting data as

    Qty Age

    -5 0-30Days

    7 31-60Days

    3 61-90 Days

    but i want data as

    Qty Age

    0 0-30Days

    5 31-60Days

    0 61-90 Days

    that means if i get negative value than that should be substracted from the oldest one and if oldest one becomes 0 then it should look out for the second last and so on.

    if anyone can provide the SQL query that will be really helpful

    Please help

    Thanks in advance

    Ravi

    it's not entirely clear what you are doing, but it sounds like you're running a recursive algorithm. if that's the case, then your best bet is to either write a recursive stored proc, or just have a statement that returns true until your condition is satisfied, and then within that statement, loop through each iteration of the procedure.

  • Please take the time to help us help you.

    In order to get a solution to the problem you described some of us like to have ready to use data (including table definition, sample data and expected results based on the sample).

    Please follow the link in my signature on how to post sample data.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • I have attached the sample data and expected output

    let me try to explain what i need

    say for a particular city ,street i get negative quantity for particular age then that should become 0 and that negative value should be substracted from the oldest value

    for example if 0-30 days qty = -4 , 31-60 days qty = 4 and 61-90 days qty =2 then the expected output is 0-30days =0 , 31-60days =2 and 61-90 days =0

    sum should remain same

    see if this is helpful for you

    Regards,

    Ravi

  • Ravi (8/17/2009)


    I have attached the sample data and expected output

    let me try to explain what i need

    It's only slightly better. You still haven't provided it in the requested format. You're missing the DDL for the tables and the insert statements for the data.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I have attached the create statment and statment to insert data.

    Please let me know if anything else is required

    Regards,

    Ravi

  • Not sure, but this is looking more like FIFO Inventory problem than an Aging problem.

    From looking at the sample data in the spreadsheet, you are deduction the (-n) from the previous (n) values for each product. Is this a fair assessment of what I saw in a cursory exam of the data?

  • Yes this has to be FIFO , and its similar to inventory

  • Ravi (8/17/2009)


    for example if 0-30 days qty = -4 , 31-60 days qty = 4 and 61-90 days qty =2 then the expected output is 0-30days =0 , 31-60days =2 and 61-90 days =0

    so, if i understand your request correctly, if you have this situation:

    0-30 days = -4

    31-60 days = 6

    61-90 days = 2

    then your output should be

    0-30 days = 0

    31-60 days = 4

    61-90 days = 2

    and, if it was

    0-30 days = -4

    31-60 days = 2

    61-90 days = 6

    then the output would be

    0-30 days = 0

    31-60 days = 2

    61-90 days = 2

    what about if the input was

    0-30 days = -2

    31-60 days = -2

    61-90 days = 6

    would the output be

    0-30 days = 0

    31-60 days = 0

    61-90 days = 2

    ?

  • The 2nd and 3rd case are correct but some minor mistake in 1st ..let me correct it

    if the input is

    0-30 days = -4

    31-60 days = 6

    61-90 days = 2

    then your output should be

    0-30 days = 0

    31-60 days = 4

    61-90 days = 0

    i really need the Sql query for this. please help

    Regards,

    Ravi

  • I'm sure someone will come up with a more elegant solution, but this should work for you. if the total negative numbers add up to more than the total positive numbers though, what should happen? for now, the result set is going to look a bit odd, but an easy solution is just to set them all to 0 in this case, which is just adding an extra update at the end.

    Input Data:

    DECLARE @TestTable TABLE

    (

    ID INT IDENTITY PRIMARY KEY,

    DateRange VARCHAR(100),

    Total INT

    )

    INSERT INTO @TestTable (DateRange, Total)

    SELECT '0-30 days', -4

    UNION

    SELECT '31-60 days', -2

    UNION

    SELECT '61-90 days', 8

    UNION

    SELECT '91-120 days', 3

    IDDateRangeTotal

    10-30 days-4

    231-60 days-2

    361-90 days8

    491-120 days3

    Code:

    DECLARE @MinTotal INT

    DECLARE @MaxTotal INT

    DECLARE @ID INT

    SET @MinTotal = 0

    SET @MaxTotal = 0

    SET @ID = 0

    WHILE 1 = 1

    BEGIN

    SELECT @ID = ID, @MinTotal = Total

    FROM @TestTable

    ORDER BY Total DESC

    SELECT @MaxTotal = MAX(Total)

    FROM @TestTable

    -- Finished with all the negative numbers

    IF ISNULL(@MinTotal, 0) >= 0 OR ISNULL(@MaxTotal, 0) 0

    ORDER BY ID DESC

    IF @@rowcount = 0 BREAK

    UPDATE @TestTable

    SET Total = (CASE WHEN Total + @MinTotal > 0 THEN Total + @MinTotal ELSE 0 END)

    WHERE ID = @ID

    SET @MinTotal = @MinTotal + @MaxTotal

    IF @MinTotal > 0 BREAK

    END

    END

    Output data:

    IDDateRangeTotal

    10-30 days0

    231-60 days0

    361-90 days5

    491-120 days0

  • Thanks for the Query , ill surely try this

    meanwhile if any one has any other solution ,please provide

    Regards,

    Ravi

  • it works when we have 1 item ,but i need for multiple items and city

    Item likes A,B,C

    and thr is also City

    Can u please help

    Regards,

    Ravi

  • i don't understand what you mean. you're going to have to provide some sample data in a more clear fashion for me adapt the query for you.

    on that note - you really should do this all in your first post, so you don't waste people's time re-working their solutions to fit your changing needs.

  • Ravi (8/18/2009)


    Thanks for the Query , ill surely try this

    meanwhile if any one has any other solution ,please provide

    Regards,

    Ravi

    How about you give it a try first. Show us what you have done to make it work for multiple items, etc. By the way, perhaps you should have provided more realistic data to begin with and you may have gotten a better answer as well.

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

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