December 15, 2022 at 2:58 pm
For each part there must be a history record of price from to price to. Is there a better way to do this than a cursor?
Use M2MDATA01
declare @part char(30)
declare @rev char(3)
Declare @price numeric(15,5)
declare @newprice numeric(15,5)
Declare @now datetime = current_timestamp
declare @new varchar(max)
declare @old varchar(max)
ALTER TABLE [dbo].[inmastx] Disable TRIGGER [inmastx_custom_after_update]
declare price cursor for select fpartno, frev, fprice, round(fprice*1.10,0) as newprice
from M2MDATA01..inmastx Where (fprice > 5)
open price
Fetch next from price into @part, @rev, @price, @newprice
While @@FETCH_STATUS = 0
Begin
set @new = rtrim(ltrim(Cast(@newprice as varchar(20))))
set @old = rtrim(ltrim(Cast(@price as varchar(20))))
exec M2Maux01..SP_InsertM2MHistory 'E','FPRICE','INV','INMAST','BWHIPP',@now, @part, @part, @rev, @new, @old,
'03','Default'
Fetch next from price into @part, @rev, @price, @newprice
End
Close price
Deallocate price
update M2MDATA01..inmastx set fprice = round(fprice*1.10,0) where fpartno+frev in (
Select fpartno+frev
from M2MDATA01..inmast
Where (fprice > 5))
declare price cursor for select fpartno, frev, fprice, round(fprice+.50,1) as newprice
from M2MDATA01..inmastx Where (fprice > 0 and fprice <= 5)
open price
Fetch next from price into @part, @rev, @price, @newprice
While @@FETCH_STATUS = 0
Begin
set @new = rtrim(ltrim(Cast(@newprice as varchar(20))))
set @old = rtrim(ltrim(Cast(@price as varchar(20))))
exec M2Maux01..SP_InsertM2MHistory 'E','FPRICE','INV','INMAST','BWHIPP',@now, @part, @part, @rev, @new, @old,
'03','Default'
Fetch next from price into @part, @rev, @price, @newprice
End
Close price
Deallocate price
update M2MDATA01..inmastx set fprice = round(fprice+.50,1) where fpartno+frev in (
Select fpartno+frev
from M2MDATA01..inmast
Where (fprice > 0 and fprice <= 5))
ALTER TABLE [dbo].[inmastx] ENABLE TRIGGER [inmastx_custom_after_update]
December 15, 2022 at 3:29 pm
If you used a stored procedure to do the update, you could also put history logic in the same procedure that does the update.
Triggers are a common way to maintain a history/audit table. Temporal tables are another way.
Cursor seems like an inefficient approach particularly if doing a mass update. It also seems odd that you are converting the numeric prices to varchar(20) for history. Is it numeric in the history table, or is it actually a varchar?
December 15, 2022 at 3:32 pm
It is actually varchar so I have to convert it. I am using stored procedure. How would I do it without the cursor?
December 15, 2022 at 3:52 pm
UPDATE using a case statement -- it looks like you only have two variations.
Is the IN clause even necessary? -- i.e., does part have to be in inmast, or can you just update/log price changes to inmastx based on fprice? If needed, I show EXISTS in example below
INSERT INTO history_table
(
...,
price,
newprice
)
SELECT
...,
price,
CASE WHEN fprice > 5 THEN CAST(round(fprice*1.10,0) AS varchar(20))
ELSE CAST(round(fprice+.50,1) AS varchar(20))
END
from M2MDATA01..inmastx
/* and if needed...
WHERE EXISTS (SELECT * FROM M2MDATA01..inmast
where inmast.fpartno = inmastx.fpartno
AND inmast.frev = inmastx.frev)
*/
December 15, 2022 at 6:21 pm
January 2, 2023 at 9:26 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply