Procedure edit for accumulated points
-
Hello,
I am trying to edit a procedure to add new points to existing points without resetting it.
I have 2 tables. First table is saving the points dynamically. Then the points in first table are saving to second table. But I want the points to get increased in the second tables.
Example: first table has 5 points. Second tables showing 5 points. (fine)
Now first table is truncated, and then it has 1 point. I want the second table to show 6 points.
In my case it will reset the 5 points and show 1 point which I don't want like that.
I am TSQL beginner and I couldn't find a solution. Hope you guys help me.
USE [Maxi_User]
GO
/****** Object: StoredProcedure [dbo].[_BARank] Script Date: 4/13/2020 3:43:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**
version : 1
author : xxx
created date : 2020-13-04
description : battle arena rank manager
**/
ALTER PROCEDURE [dbo].[_BARank]
AS
DECLARE @Counter INT = 1
DECLARE @RankTable TABLE (ID INT IDENTITY(1,1), CharName16 VARCHAR(64), Points INT)
INSERT @RankTable
SELECT Data2, COUNT(*)
FROM Maxi_User.dbo._BridgeCommands
WHERE Executor = '_IncreaseKillCountBA' and Data3 = 'BattleArena'
GROUP BY Data2
ORDER BY COUNT(*) DESC
WHILE @Counter <= (SELECT COUNT(*) FROM @RankTable)
BEGIN
DECLARE @CharName16 VARCHAR(64),
@Points INT,
@CharID INT,
@Guild VARCHAR(64)
/**_# INITIALIZE */
SET @CharName16 = NULL
SET @CharID = NULL
SET @Guild = NULL
SELECT @CharName16 = CharName16,
@Points = Points
FROM @RankTable
WHERE ID = @Counter
SELECT @CharID = CharID
FROM KSY_VT_SHARD.dbo._Char
WHERE CharName16 = @CharName16
SELECT @Guild = G.Name
FROM KSY_VT_SHARD.dbo._GuildMember GM
JOIN KSY_VT_SHARD.dbo._Guild G
ON GM.GuildID = G.ID
WHERE GM.CharName = @CharName16
IF @Guild IS NULL
BEGIN
SET @Guild = ''
END
UPDATE Maxi_User.dbo._DynamicRank_Custom1
SET CharName16 = @CharName16, --POSIBLE CHAR RENAME
GuildName = @Guild, --POSIBLE GUILD LEAVE
Point = @Points,
LastUpdated = GETDATE()
WHERE CharID = @CharID
IF @@ROWCOUNT = 0
BEGIN
INSERT Maxi_User.dbo._DynamicRank_Custom1 (CharID, CharName16, GuildName, Point, LastUpdated)
VALUES (@CharID, @CharName16, @Guild, @Points, GETDATE())
END
/**_# FOR LOOP COUNTER*/
SET @Counter += 1
END- This topic was modified 4 years, 8 months ago by bissagars.
-
Can you also show us what the data looks like?
If you want a working solution, you'll need to provide a script with sample DDL, INSERT (for data) and desired results.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
First table you see 5x counts for "Name" he has 5 points. This data is saved dynamically from a tool.
Then it is saved for the Log table
Now 5 counts = 5 points.
Now when the first table is truncated, and the tool will save new points to the first table. I want the new points to get saved on the (already) 5 points. So it gets increased over it but in my case it will change to 1 point and lose the 5 points.
-
Why is the 'first table' being truncated? Best solution is to keep the data, or move it to an archive table, rather than deleting it.
If you delete it and you ever find that the Log table needs to be rebuilt (because it's become corrupted somehow), you will be up shit creek.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
The table is being truncated because of the tool, it resets automatically at 12:00AM, I don't have control over it.
There is nothing we can do with current procedure?
-
alter procedure [dbo].[_BARank]
as
set nocount on;
set xact_abort on;
begin transaction
begin try
Declare
@executor varchar(64)='_IncreaseKillCountBA',
@Data3 varchar(64)='BattleArena';
declare
@RankTable table(CharName16 varchar(64) unique not null,
Points int not null);
insert @RankTable(CharName16, Points)
select
Data2, count(*)
from
Maxi_User.dbo._BridgeCommands
where
Executor = @executor
and Data3 = @Data3
group by
Data2;
/* try to update */
update dc1
set
CharName16=c.CharName,
GuildName = g.[Name],
point=rt.points,
LastUpdated=getdate()
from
Maxi_User.dbo._DynamicRank_Custom1 dc1
join
KSY_VT_SHARD.dbo._Char c on dc1.CharId=c.CharId
join
@RankTable rt on c.CharName=rt.CharName16
join
KSY_VT_SHARD.dbo._GuildMember gm on c.CharName16=gm.CharName
join
KSY_VT_SHARD.dbo._Guild g on gm.GuildID = g.ID;
/* insert where not exists */
insert Maxi_User.dbo._DynamicRank_Custom1(CharID, CharName16, GuildName, Point, LastUpdated)
select
c.CharID, c.CharName16, g.[Name], rt.Points, getdate()
from
KSY_VT_SHARD.dbo._Char c
join
@RankTable rt on c.CharName=rt.CharName16
join
KSY_VT_SHARD.dbo._GuildMember gm on c.CharName16=gm.CharName
join
KSY_VT_SHARD.dbo._Guild g on gm.GuildID = g.ID
where
c.CharId not in(select 1 from Maxi_User.dbo._DynamicRank_Custom1 dc1 where dc1.CharID=c.CharId);
commit transaction;
end try
begin catch
print ('No bueno');
rollback transaction;
end catch
goAus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Needs sample data or it's unlikely to work as intended the first time.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply