Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2014
    Posts
    3

    Question Unanswered: Split one row in multiple row based upon a integer value

    Hi everyone,

    i got an issue and i don't know how to solve it.

    My code looks like this for now:

    Code:
    SELECT CDELIG.CDELIG_QTE AS [Quantity],
    		(ARTICLE.ART_PRIXPUBLIC * CDELIG.CDELIG_QTE) * 100  AS [Total line price],
    		(CDELIG.CDELIG_PU_SUBACC_MNT * CDELIG.CDELIG_QTE) * 100 AS [Grant]
    		FROM CDELIG
    		INNER JOIN ARTICLE ON ARTICLE.ART_ID = CDELIG.ART_ID
    And the result like this:

    Quantity Total line price Grant
    120 84000 0.0000
    255 178500 0.0000
    1 700 0.0000

    The problem is that if a quantity is higher than 99 i got to split the row. And the others has to change also because the quantity would have change.

    The expected result will be like this:

    Quantity Total line price Grant
    99 69300 0
    21 14700 0
    99 69300 0
    99 69300 0
    57 39900 0
    1 700 0

    Sorry for my english and thanks in advance for your responses!

  2. #2
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    20
    Hello

    You are having 3 columns (Quantity, Total line price, Grant) in your select query. Do you want to split it based on the quanity that an ART_ID has as I see;
    1. 120 split into two 99 & 21; 84000 split into two 69300 & 14700
    2. 255 split into three 99, 99, 57; 178500 split into three 69300, 69300 & 39900

    I assume the quantity 120 belongs to one art_id and 255 to another art_id; and you want that the first split record to have 99, remaining quantity as another record and so on. So ideally you would be subtracting 99 from the total quantity & if still the remainder is greater than 99 then again subtracting 99 from the remainder quantity...right??? But then how will your manage subtracting the price in this process? Is there any other key that can separate the quantities for an art_id or do you want to add the first 99 quantity's price placing it against it in the same row and then next 99 quantity's price in the second row and so on...

    Thanks & Regards
    Satyajit

  3. #3
    Join Date
    Jun 2014
    Posts
    3
    You're right 120 QTE belongs to one art_id and 255 belongs to an other.

    you want to add the first 99 quantity's price placing it against it in the same row and then next 99 quantity's price in the second row and so on...
    This is exactly what i want to do !

    Thanks & regards!

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    with
        CTE_Qty as
        (
            SELECT
                CDELIG.ART_ID, 
                CDELIG.CDELIG_QTE AS [Quantity],
                ARTICLE.ART_PRIXPUBLIC * 100 AS [Price],
                CDELIG.CDELIG_PU_SUBACC_MNT * CDELIG.CDELIG_QTE * 100 AS [Grant]
            FROM CDELIG
            INNER JOIN ARTICLE 
                ON ARTICLE.ART_ID = CDELIG.ART_ID
        ),
        
        CTE_Rec as
        (
            select 
                Art_Id,
                case when Quantity > 99 then 99 else Quantity end as Quantity,
                Quantity - 99 as Remainder,
                Price,
                [Grant] 
            from CTE_Qty
            
            union all
            
            select
                Art_Id,
                case when Remainder > 99 then 99 else Remainder end,
                Remainder - 99,
                Price,
                [Grant]
            from CTE_Rec
            where
                Remainder > 0
        )
        
    select
        Art_Id,
        Quantity,
        Quantity * Price as TotalLinePrice,
        [Grant]
    from CTE_Rec    
    order by 
        Art_Id
    Hope this helps.

  5. #5
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    20
    That should cater your needs with little modification to manage your calculations

    gud one imex...


    Cheers

  6. #6
    Join Date
    Jun 2014
    Posts
    3
    This is working like a charm!

    Thank you very much Imex and Satyajit!

    Cheers

    Have a good day!

    P.S: Is there a way to make the thread as Resolved or something?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Patly View Post
    Is there a way to make the thread as Resolved or something?
    Sorry, but VBulletin is more forum software than a knowledgebase or help desk so there isn't any formal "resolved" status.

    I'm glad to know that your problem has been resolved though!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •