Post reply

Query

  • February 19, 2023 at 9:27 am

    #4151566

    Dear All

    My table contains the following records

    create table #ProductDetails(ProductID int , PurchaceDate datetime ,LicenceQty Int)
    insert into #ProductDetails values (1,getdate()-5,10)
    insert into #ProductDetails values (1,getdate()-4,25)
    insert into #ProductDetails values (2,getdate()-4,25)
    insert into #ProductDetails values (6,getdate()-3,100)

    insert into #ProductDetails values (6,getdate()-4,20)

    insert into #ProductDetails values (6,getdate()-5,20)
    select * from #ProductDetails

    In the above table, I want the following result based on the condition

    I have passed the parameters  Product id and Qty

    For Example, I am passed the parameter productid and total license count, and based on this reduce the license count

    exec spLiceceQty @Productid=1,@LicenceQty= 10

    I want the following result set

    1, 2023-02-15 14:47:16.927, 25

    2 ,2023-02-15 14:47:16.927, 25

    6, 2023-02-16 14:47:16.930, 100

    6 ,2023-02-15 14:47:16.930, 20

    6, 2023-02-14 14:47:16.930, 20

    for example 

    exec spLiceceQty @Productid=1,@LicenceQty= 15

    1, 2023-02-15 14:47:16.927 ,20

    2 ,2023-02-15 14:47:16.927 ,25

    6 ,2023-02-16 14:47:16.930 ,100

    6 ,2023-02-15 14:47:16.930 ,20

    6 ,2023-02-14 14:47:16.930, 20

    for example

    exec spLiceceQty @Productid=6,@LicenceQty= 50

     

    1 ,2023-02-14 14:47:16.927, 10

    1, 2023-02-15 14:47:16.927 ,25

    2, 2023-02-15 14:47:16.927, 25

    6, 2023-02-16 14:47:16.930, 50

    6, 2023-02-15 14:47:16.930, 20

    6, 2023-02-14 14:47:16.930, 20

     

     

    tHANK YOU

     

     

     

     

     

  • February 19, 2023 at 12:49 pm

    #4151588

    Well, what does the chatbox have to say about this one?  It doesn't look too tricky imo

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • February 19, 2023 at 1:21 pm

    #4151589

    What determines the hierarchy of 'LicenceQty' row values from which to begin the subtraction?  In the case of ProductID=1 the example appears to apply lifo, "last in, first out", and in the case of ProductID=6 it's fifo, "first in, first out".  The hierarchy also doesn't appear to be small to large or large to small...  Is it random?  Also, you're looking for a DELETE statement or SELECT?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • February 19, 2023 at 3:42 pm

    #4151641

    Thank you.I am looking select statement

  • February 19, 2023 at 4:23 pm

    #4151644

    vs.satheesh wrote:

    Thank you.I am looking select statement

      Ok thank you.  Still have some questions tho

    Example 1:

    exec spLiceceQty @Productid=1,@LicenceQty= 10

    I want the following result set

    1, 2023-02-15 14:47:16.927, 25

    Why were the 10 LicenceQty subtracted from the row with the earliest PurchaceDate?  If not the PurchaceDate then what determines from which row to subtract from?

    Example 2: Seems ok

    Example 3:

    exec spLiceceQty @Productid=6,@LicenceQty= 50

    6, 2023-02-16 14:47:16.930, 50

    Why were the 50 LicenceQty subtracted from the row with the most recent PurchaceDate?  Same question as above

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • February 19, 2023 at 4:25 pm

    #4151646

    Could just be a lack of coffee but I have no idea how your combination of Product_ID and Qty would produce the results you posted.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 19, 2023 at 4:27 pm

    #4151647

    Steve Collins wrote:

    Well, what does the chatbox have to say about this one?

    Good lord. 🙁

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 19, 2023 at 5:19 pm

    #4151660

    Jeff Moden wrote:

    Good lord. 🙁

    It's got to have you wondering, no?  Where the line is to be drawn?  How many doublings do we have remaining?  What really changes because I'm still having a big demand for SQL development regardless of who or what performs the act?  Who wants to be the last man cracking rocks with a toothpick when everyone else is drinking martinis at the bar?  Not me

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • February 20, 2023 at 12:39 am

    #4151724

    Steve Collins wrote:

    Jeff Moden wrote:

    Good lord. 🙁

    It's got to have you wondering, no?  Where the line is to be drawn?  How many doublings do we have remaining?  What really changes because I'm still having a big demand for SQL development regardless of who or what performs the act?  Who wants to be the last man cracking rocks with a toothpick when everyone else is drinking martinis at the bar?  Not me

    Heh... and who wants to get a panic call while they're at the bar? 😉  Of the few relatively simple questions I've asked of it, I've gotten mostly incorrect answers and answers that don't actually work.  The ones that do actually work are extremely performance challenged.

    It also says in the use-agreement that that can be the case and there won't be any warning.

    Good luck to all that are using it for code solutions.  If you use it to study for an interview, it'll certainly help you keep the interview really short. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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