Duplicate Rows Withe Percentage

  • Hello

    I need to do this task, i have a table with several numeric values, and for each row i need to calculate a percentage of the value and update the row, and create an new one with the remain percentage... Example, the user passes 25% to the sp, the original row will be update by 25% and the new row will be a copy of this one but instead of 25% will be 75%... This is done in a temp table so I can use the same table or create a new table to save all the rows...

    I know how to do this with a cursor, but i'm looking for a quick way, if exists one.

    Some sample data:GO

    IF OBJECT_ID('TempDB..#TesteTable','U') IS NOT NULL

    DROP TABLE #TesteTable

    CREATE TABLE #TesteTable(

    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    Duration INT,

    A NUMERIC(12,2),

    B NUMERIC(12,2)

    )

    INSERT INTO #TesteTable

    SELECT 10,10,10 UNION ALL

    SELECT 5,3,7 UNION ALL

    SELECT 23,1,2 UNION ALL

    SELECT 44,34,12 UNION ALL

    SELECT 12,2,6 UNION ALL

    SELECT 21,5,5

    Thanks

  • Here's one way that might do it:

    DECLARE @Pct float = .25

    DECLARE @maxID int = (select max(ID) from #TesteTable)

    MERGE INTO #TesteTable a

    USING (

    SELECT ID, Duration, A+A*@pct as A, B+B*@pct as B

    FROM #TesteTable

    UNION

    SELECT ID+@MaxID, Duration, A+A*(1-@pct), B+B*(1-@pct)

    FROM #TesteTable

    ) b

    ON a.ID = b.ID

    WHEN MATCHED THEN

    UPDATE SET a.A = b.A, a.B = b.B

    WHEN NOT MATCHED THEN

    INSERT (Duration, A,B) VALUES (b.Duration, b.A, b.B)

    ;

  • Are you planning on storing this data in a persistent table? Reason I ask, is the fine solution posted by gbritton1 will not work in all cases. What happens when the row has already been created, meaning the "opposite" row already exists? There is nothing to indicate if the row is original or a copy. This also has some concurrency issues. I think it would be extremely helpful if you could explain better what you are trying to do.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks for the replies...

    This chunk of code it's a requirement inside a bigger sp, the table it's a table that contains some fields a start date, duration (days), and several locations (time in that location in hours)....

    I need to plot a graph, that makes the distribution of the hours by the days, counting from the start date... Until now i was doing a direct division, hours/days, now the requirement it's to add an extra parameter that changes the distribution, that in the first 50% of the time i'll use for example 30% of the hours and in the remaining 50% i'll use the 70% of the hours. For example if i have an interval of 10 days and 100 hours to use, in the first 5 days i'll have 30 hours to divide and in the remaining 5 days will have 70 hours...

    Like i said, this is only to plot a graph, so it´s a temp table.

    THanks

  • rootfixxxer (4/16/2014)


    Thanks for the replies...

    This chunk of code it's a requirement inside a bigger sp, the table it's a table that contains some fields a start date, duration (days), and several locations (time in that location in hours)....

    I need to plot a graph, that makes the distribution of the hours by the days, counting from the start date... Until now i was doing a direct division, hours/days, now the requirement it's to add an extra parameter that changes the distribution, that in the first 50% of the time i'll use for example 30% of the hours and in the remaining 50% i'll use the 70% of the hours. For example if i have an interval of 10 days and 100 hours to use, in the first 5 days i'll have 30 hours to divide and in the remaining 5 days will have 70 hours...

    Like i said, this is only to plot a graph, so it´s a temp table.

    THanks

    So does the code posted earlier work or do you still need help?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Yes i need.

    I'm currently using/testing in the 2008 version, but i need that the code works in the 2005 version too...

    Just posted in this section because i thought that the solution could be more generic, without the merge command, that i don't know very well.

    THanks

  • rootfixxxer (4/16/2014)


    Yes i need.

    I'm currently using/testing in the 2008 version, but i need that the code works in the 2005 version too...

    Just posted in this section because i thought that the solution could be more generic, without the merge command, that i don't know very well.

    THanks

    OK. So since this data is volatile we can assume that every row in the temp table needs an "offset" row correct?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Volatile yes...

    Offset?! What do you mean by offset row?!

  • rootfixxxer (4/16/2014)


    Volatile yes...

    Offset?! What do you mean by offset row?!

    Inverse, whatever you want to call it. gbritton basically gave you the answer already. A very minor tweak to his/her fine code and you could end up with something like this.

    declare @Pct numeric(9,2) = .35

    SELECT ID,

    Duration,

    A + A * @pct as A,

    B + B * @pct as B,

    1 as SortOrder

    FROM #TesteTable

    UNION ALL

    SELECT ID,

    Duration,

    A + A *(1 - @pct),

    B + B *(1 - @pct),

    2

    FROM #TesteTable

    order by Duration, SortOrder

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Looking to the things like that, it looks very easy...

    But it's not handling the 50% interval duration, neither it's capable of it, for example if i have 1 day and 10 hours to split, the first split i'll get 0,5 that i must round (i need complete days), and i'll get 1, and in the second split the same thing...

    So i'll get 1+1 = 2, instead of the 1. In this situation i need to set the first split to 0 and the second to 1, and only make the calculation for the second split ignoring the percentage of the hours and using the total amount...

    To solve the round problem I can set the first split to always round down and the second split to always round up. But i need to somehow look ahead to see if the first split returns 0 and handle the situation.

    I should mention this in the beginning but i thought that after getting the help for the duplication, i could get the solution by myself, i was wrong. 🙁

    The only solutions that came to my head, it's using a cursor, maybe it's the simplest way..

    Sorry and Thanks for the patience

  • rootfixxxer (4/16/2014)


    Looking to the things like that, it looks very easy...

    But it's not handling the 50% interval duration, neither it's capable of it, for example if i have 1 day and 10 hours to split, the first split i'll get 0,5 that i must round (i need complete days), and i'll get 1, and in the second split the same thing...

    So i'll get 1+1 = 2, instead of the 1. In this situation i need to set the first split to 0 and the second to 1, and only make the calculation for the second split ignoring the percentage of the hours and using the total amount...

    To solve the round problem I can set the first split to always round down and the second split to always round up. But i need to somehow look ahead to see if the first split returns 0 and handle the situation.

    I should mention this in the beginning but i thought that after getting the help for the duplication, i could get the solution by myself, i was wrong. 🙁

    The only solutions that came to my head, it's using a cursor, maybe it's the simplest way..

    Sorry and Thanks for the patience

    You don't need a cursor for this. If you can explain all the rules in a way that I can understand we can do this very easily.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • OK

    I'll try, i have stored in a temp table information about items that must be sent to the factory to production, several joins and calculations to get to this point.

    For each one of them i have a start date, a end date, the difference between this two in working days returns me the duration/ time available to produce the item in complete days. And i have several times for several points/sectors that exist in the factory where the items will pass...

    So i need to put this in a graph to show the load of the factory, like i wrote before, actually i don't have any variables, i just do simple math, i have 10 days, sector a 5 hours, sector b 10 hours, and so on, just put 0,5 hours for each day in sector a, 1 hour for each day in sector b... It works, but doesn't represent the reality, because the beginning of the process it's slower than the end of the process, so the solution was to create two vars, the first one it's to say what's the percentage of process that's considerable as slow, and the second var it's to say the percentage of work that normally will be done in the slow part...

    So i need to pick each one of the rows for the temp table and create two rows, the first row, will be the "slow row" split, that have a start date, a duration and a percentage valor for each sector (some sectors aren't used, 0 time), and the second row, that will have a start date (slow row date + duration of the slow row), a duration and the remaining percentage for each sector...

    I cant explain the process better than this. Hope i made myself clear.

    THanks

  • OK so let's pretend you are going to run this with the sample data you provided. You said the value for the parameter would be 25%. Given that value, what should the output be from your sample data? Not a verbal explanation, I want to know what the contents of that table should look like. Use another temp table with some inserts or something.

    There are a lot of rules that you have not provided which are clear to you but not to anybody else. Without knowledge of what you are trying to do this is incredibly difficult to figure out.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hehehe

    The sample code with a working cursor version of the situation:

    GO

    IF OBJECT_ID('TempDB..#TesteTable','U') IS NOT NULL

    DROP TABLE #TesteTable

    IF OBJECT_ID('TempDB..#TesteTableFinal','U') IS NOT NULL

    DROP TABLE #TesteTableFinal

    CREATE TABLE #TesteTable(

    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    StartDate SMALLDATETIME,

    Duration INT,

    A NUMERIC(12,2),

    B NUMERIC(12,2)

    )

    CREATE TABLE #TesteTableFinal(

    ID INT,

    StartDate SMALLDATETIME,

    Duration INT,

    A NUMERIC(12,2),

    B NUMERIC(12,2)

    )

    INSERT INTO #TesteTable

    SELECT '01/01/2013',10,10,10 UNION ALL

    SELECT '02/01/2013',5,3,7 UNION ALL

    SELECT '03/01/2013',23,1,2 UNION ALL

    SELECT '04/01/2013',44,34,12 UNION ALL

    SELECT '05/01/2013',12,2,6 UNION ALL

    SELECT '06/01/2013',1,5,15 UNION ALL

    SELECT '07/01/2013',21,5,5

    SELECT * FROM #TesteTable

    -- New part

    -- Input sp values

    DECLARE @Interval AS NUMERIC(8,2)

    DECLARE @Percentage AS NUMERIC(8,2)

    -- In 50% of the duration i'll use 30% of the available hours, TESTE VALUES

    SET @Interval = 0.5

    SET @Percentage = 0.3

    -- Cursor version

    DECLARE @ID INT,@Dat SMALLDATETIME,@Dur INT, @a NUMERIC(8,2),@B NUMERIC(8,2) -- Cursor vars

    DECLARE @I1 NUMERIC(8,2),@I2 NUMERIC(8,2) --Internal vars

    DECLARE cur CURSOR FOR

    SELECT * FROM #TesteTable

    OPEN cur

    FETCH NEXT FROM cur INTO @ID,@Dat,@Dur,@A,@B

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @I1 = FLOOR(@Dur * @Interval)

    SET @I2 = CEILING(@Dur * (1-@Interval))

    --- Only insert the first row if i have at least one day interval

    IF @I1 <> 0

    -- First interval

    INSERT INTO #TesteTableFinal

    ( ID, StartDate, Duration, A, B )

    VALUES ( @ID, -- ID - int

    @Dat, -- StartDate - smalldatetime

    @I1, -- Duration - int

    @a * @Percentage, -- A - numeric

    @b-2 * @Percentage -- B - numeric

    )

    ELSE

    -- Set the percentage to 0% because there isn't anything to insert in the first one

    SET @Percentage = 0

    --- Second interval

    INSERT INTO #TesteTableFinal

    ( ID, StartDate, Duration, A, B )

    VALUES ( @ID, -- ID - int

    DATEADD(DAY,@I1,@Dat), -- StartDate - smalldatetime

    @I2, -- Duration - int

    @a * (1-@Percentage), -- A - numeric

    @b-2 * (1-@Percentage)-- B - numeric

    )

    FETCH NEXT FROM cur INTO @ID,@Dat,@Dur,@A,@B

    END

    CLOSE cur

    DEALLOCATE cur

    SELECT * FROM #TesteTableFinal

  • I am not sure that your cursor actually works in all cases. When running it as is the values for ID 7 seems to be accurate. If you add a where clause to your cursor select statement "Where ID = 7" it gets different values. 😉

    Regardless I am pretty sure this can be turned into 2 relatively simple insert statements. At least this matches your sample data.

    IF OBJECT_ID('TempDB..#SeanTesteTableFinal','U') IS NOT NULL

    DROP TABLE #SeanTesteTableFinal

    CREATE TABLE #SeanTesteTableFinal(

    ID INT,

    StartDate SMALLDATETIME,

    Duration INT,

    A NUMERIC(12,2),

    B NUMERIC(12,2)

    )

    INSERT INTO #SeanTesteTableFinal

    (ID, StartDate, Duration, A, B)

    select ID,

    StartDate,

    FLOOR(Duration * @Interval),

    A * @Percentage,

    B * @Percentage

    from #TesteTable

    where FLOOR(Duration * @Interval) <> 0

    INSERT INTO #SeanTesteTableFinal

    (ID, StartDate, Duration, A, B)

    select ID,

    DATEADD(DAY, FLOOR(Duration * @Interval), StartDate),

    CEILING(Duration * (1 - @Interval)),

    Case when FLOOR(Duration * @Interval) <> 0

    then A * (1 - @Percentage)

    else A

    end,

    Case when FLOOR(Duration * @Interval) <> 0

    then B * (1 - @Percentage)

    else B

    end

    from #TesteTable

    select * from #SeanTesteTableFinal order by ID

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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