Post reply

Complex Query

  • October 15, 2007 at 3:39 am

    #180452

    Hi Experts

    I have appno,Qty,Price in my table and I want to update CleanQty

    for that application no as per rules.

    Here is the o/p I want in CleanQty Column with explanation about How CleanQty calculated :

    AppNo Qty Price CleanQty

    1 200 40 100 (Step 2)

    1 100 50 100 (Step 1)

    1 300 30 100 (Step 3)

    2 300 50 300 (Step 4)

    2 200 40 0 (Step 5)

    2 100 30 0 (Step 6)

    3 100 50 0 (Step 8)

    3 200 50 200 (Step 7)

    3 300 40 100 (Step 9)

    Explanation about how CleanQty is calculated :

    (Step 1 : Max price for app1 is 50 , so put its corresponding qty here which is 100)

    (Step 2 : 2nd highest price for App1 is 40.So cleanQty 100=200(current Qty) - 100(prev. Clean qty)

    (Step 3 : 3rd highest price for App1 is 30.

    So cleanQty 100=300(current Qty)-200(sum of prev. clean qty is 100+100)

    (Step 4 : Max Price for app2 is 50 , so put its corresponding qty here which is 300)

    (Step 5 : 2nd highest price for app2 is 40.So cleanQty -100=200(current Qty) - 300(prev. Clean qty).

    (Step 6 : 3rd highest price for app2 is 30.

    So cleanQty -200=100(current Qty)-300(sum of prev. clean qty is 0+300)

    Clean Quantity is -ve so put 0)

    (Step 7 : Max Price for app3 is 50 and another record for app3 is having price 50

    So which one is having max Qty choose that, so put its corresponding qty here which

    (Step 8 : 100-200=-100 So 0)

    (Step 9 : 300-(0+200) =100)

    Plz help me to write optimized query to calculate CleanQty

    Thanks

  • October 15, 2007 at 4:16 am

    #741918

    personally I don't think this is the place to post this - you're really asking for a freebie from an experienced developer/dba.

    I will go out of my way to help a poster with a problem if I can help them on their way - but I draw the line at writing code - I suggest you either find an appropriate training course/tutorial/book or employ a developer.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • October 15, 2007 at 4:23 am

    #741921

    First of all sorry for such a query.

    I know that this is not a right way .

    But due to urgency I had to ask.

    Also I dont have much knowledge about SQL Server.

    Any help will be appreciated.

  • October 15, 2007 at 5:18 am

    #741927

    Strange that when a problem is URGENT, the original poster always finds time to post the problem statement on several different sites...

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

Viewing 4 posts - 1 through 3 (of 3 total)

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