April 15, 2014 at 10:40 am
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
April 15, 2014 at 11:03 am
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)
;
April 15, 2014 at 12:37 pm
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/
April 16, 2014 at 2:41 am
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
April 16, 2014 at 7:13 am
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/
April 16, 2014 at 8:04 am
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
April 16, 2014 at 8:07 am
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/
April 16, 2014 at 8:13 am
Volatile yes...
Offset?! What do you mean by offset row?!
April 16, 2014 at 8:19 am
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/
April 16, 2014 at 9:10 am
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
April 16, 2014 at 9:31 am
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/
April 16, 2014 at 10:16 am
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
April 16, 2014 at 11:02 am
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/
April 17, 2014 at 3:13 am
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
April 17, 2014 at 8:07 am
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