This is a ledger kind of table, initial amount is when the movement is null and all transactions on it is based on the Waste_Seq
the query take the last entry (id) based on the movement and change the initial amount
this query seem to work for my scenario and seem fast, is there a better or alternative way of doing what the query does?
long term this table will have more than 5-10 millions rows easily, this is a new empty table for now
Schema and data:
CREATE TABLE [dbo].[fredou](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Movement] [int] NULL,
[Waste_Seq] [int] NOT NULL,
[Amount] [int] NOT NULL,
[operation] [int] NOT NULL,
CONSTRAINT [PK_fun_fred_test] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[fredou]
(
[Waste_Seq] ASC,
[Movement] ASC,
[Id] DESC
)
INCLUDE ( [Amount],
[operation]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
insert into fredou values ( null, 1, 1000, 1)
insert into fredou values ( 1, 1, 500, -1)
insert into fredou values ( 1, 1, 450, -1)
insert into fredou values ( 1, 1, 475, -1)
insert into fredou values ( 1, 1, 475, -1)
insert into fredou values ( 2, 1, 300, 1)
insert into fredou values ( 2, 1, 200, 1)
insert into fredou values ( 2, 1, 100, 1)
insert into fredou values ( 3, 1, 300, 0)
insert into fredou values ( 3, 1, 350, 0)
insert into fredou values ( 4, 1, 50, -1)
insert into fredou values ( 4, 1, 50, 0)
insert into fredou values ( null, 2, 5000, 1)
insert into fredou values ( 50, 2, 1000, -1)
insert into fredou values ( 50, 2, 1000, 0)
insert into fredou values ( 51, 2, 500, -1)
insert into fredou values ( 53, 2, 400, -1)
insert into fredou values ( 51, 2, 300, -1);
Query:
DECLARE @SEQ int = 1
SELECT SUM(f.Amount * f.Operation) as Amount
FROM
(
SELECT FIRST_VALUE(a.Amount) OVER (partition BY a.Movement order by a.id desc) as Amount,
FIRST_VALUE(a.Operation) OVER (partition BY a.Movement order by a.id desc) as Operation,
ROW_NUMBER() OVER (partition BY a.Movement order by a.id desc) as rownum
FROM [dbo].[fredou] a
WHERE a.Waste_Seq = @SEQ
) f
where f.rownum = 1;
Link to sql fiddle
