sql2016 - if column cell changes, insert value into another column cell
-
How can I calculate or insert TrimWgt (17.15 from MasterRolls value =3) and (17.15 from MasterRolls value = 4) into another column if the MasterRolls column increments or different from the the previous. If I had another column in the table or I can create another table. Please assist. My table is updating every 30-60 seconds. I cannot do a change or sample because the values could be the same on consecutive MasterRolls just like in the example below. I need to record 17, 17.15, 17.15, 18.4.
- This topic was modified 3 years, 8 months ago by jayM.
-
Can you please provide the DDL (create table) script, the data as an insert statement, what you have tried so far and the expected results.
😎
-
As Eirikur Eiriksson said, without data, it is hard for us to help you.
But lets assume that all of your columns are in a format that meets your sample data. MasterRools and Slices are both smallint, t_stamp is ... umm... lets call that varchar as I don't see anything else it could be (but I would strongly encourage you to use a different format for your t_stamp such as a datetime or even a time data type), and MRWgt and TrimWgt are both numeric(5,2).
What you are asking, to me, sounds like you are wanting a trigger; specifically an AFTER UPDATE trigger. Basically, your after update trigger would watch for MasterRolls to become either 3 or 4 and if so, update the TrimWgt to 17.15. That is assuming you need the value to change immediately. Triggers will cause performance slowdowns as it need to run after each and every update, even if MasterRolls is not changed to a 3 or 5, but is a 2 or 5.
If you want less of a performance hit and can live with the data not being updated immediately, you could have a scheduled job that updates TrimWgt to 17.15 where MasterRools = 3 or 4. If you built an index to include MasterRolls and TrimWgt, you could also filter that down to where TrimWgt <> 17.15. This way your update should be pretty quick and you could run it hourly or daily (depending on your requirements).
Alternately, how does the tool update the data? If it is from a stored procedure (which is how I'd recommend doing it), change your stored procedure to insert TrimWgt of 17.15 when MasterRolls = 3 or 4.
To summarize - Trigger or stored procedure change if you need real-time data changes, scheduled job if you can wait.
At least, that is what my approach would be. Stored procedure change is likely the least impact to your system and thus the "best" option.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Our process:
- We have masterRolls#
- We cut into 5 slices per that roll, but it could be 2 slices, could be up to 6 based on different jobs
- I need to take only one weight from the duplicate trim weights because each transaction for trim weight is based on the MasterRoll, not the Slice
- .....and record and insert that TrimWgt cell value into another column.
-
Something like this maybe?
SELECT *
FROM
(SELECT MasterRolls
, MrWgt
, TrimWgt
, rn = ROW_NUMBER() OVER (PARTITION BY MrWgt ORDER BY TrimWgt)
FROM RollSlice) orderedSlice
WHERE orderedSlice.rn = 1; - jayM wrote:
Our process:
- <li style="list-style-type: none;">
- We have masterRolls#
- <li style="list-style-type: none;">
- We cut into 5 slices per that roll, but it could be 2 slices, could be up to 6 based on different jobs
- <li style="list-style-type: none;">
- I need to take only one weight from the duplicate trim weights because each transaction for trim weight is based on the MasterRoll, not the Slice
- <li style="list-style-type: none;">
- .....and record and insert that TrimWgt cell value into another column.
- <li style="list-style-type: none;">
Jay,
See the first link in my signature line below... it'll help you help us and will usually result in faster and better answers that have code that has been tested. Pictures of data isn't "readily consumable" data.
--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) -
--create masterscale2 table--
Create table masterscale2
( ID varchar(50)
,MR# int
,R# int
,MRWgt numeric(4,2)
,TrimWgt numeric(4,2))
go
--insert data--
INSERT INTO masterscale2
(ID, mr#, R#, MRWgt, TrimWgt)
select'0101011234654991','9','4','48.05','14.35' UNION ALL
select'0101011234654990','9','3','48.05','14.35' UNION ALL
select'0101011234654989','9','2','48.05','14.35' UNION ALL
select'0101011234654988','9','1','48.05','14.35' UNION ALL
select'0101011234654987','8','4','35.25','16.10' UNION ALL
select'0101011234654986','8','3','35.25','16.10' UNION ALL
select'0101011234654985','8','2','35.25','16.10' UNION ALL
select'0101011234654984','8','1','35.25','16.10' UNION ALL
select'0101011234654983','7','4','45.95','16.85' UNION ALL
select'0101011234654982','7','3','45.95','16.85' UNION ALL
select'0101011234654981','7','2','45.95','16.85' UNION ALL
select'0101011234654980','7','1','45.95','16.85' UNION ALL
select'0101011234654979','6','4','45.65','16.30' UNION ALL
select'0101011234654978','6','3','45.65','16.30' UNION ALL
select'0101011234654977','6','2','45.65','16.30' UNION ALL
select'0101011234654976','6','1','45.65','16.30' UNION ALL
select'0101011234654975','5','4','46.95','15.05' UNION ALL
select'0101011234654974','5','3','46.95','15.05' UNION ALL
select'0101011234654973','5','2','46.95','15.05' UNION ALL
select'0101011234654972','5','1','46.95','15.05' -
I am trying to retrieve the blue-highlighted based on MR#.
-if MR# is different from last (5 to 6), retrieve 16.30, if MR# is different from last, retrieve 16.85, and repeat
-
i posted create and insert data, please review my problem, i have no idea where to start besides using excel
-
Thank you very much. I created a script to create and insert, now we can finally get somewhere!
-
--retrieve one TrimWgt from MR#s--
SELECT *
FROM
(SELECT ID, MR#, R#, MrWgt, TrimWgt,
rn = ROW_NUMBER() OVER (PARTITION BY MrWgt ORDER BY TrimWgt)
FROM masterscale3) orderedSlice
WHERE orderedSlice.rn = 1 - jayM wrote:
I am trying to retrieve the blue-highlighted based on MR#.
-if MR# is different from last (5 to 6), retrieve 16.30, if MR# is different from last, retrieve 16.85, and repeat
jayM wrote:--retrieve one TrimWgt from MR#s--
SELECT *
FROM
(SELECT ID, MR#, R#, MrWgt, TrimWgt,
rn = ROW_NUMBER() OVER (PARTITION BY MrWgt ORDER BY TrimWgt)
FROM masterscale3) orderedSlice
WHERE orderedSlice.rn = 1I have to admit, your last several post have confused me. The title talks about inserting new values and you talk about selecting values when the value in a column changes, and then you talk about selecting on the data in the blue cells, and now you post code with no amplification.
So, let's start at that latest post above. What is that code. To me, it looks like the answer to how you find the rows with the blue cells on them but I haven't a clue as to what you want to do with it because the data you posted looks like it has everything it needs rather than what the data starts out as.
I also think we're also fighting a bit of a language barrier here.
--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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply