prueba cliente

declare @date datetime= '20230226', @clientid int = 1;

DECLARE @accountStatus as table
(
	Id int identity(1,1),
	Description varchar(500),
	Credit  money,
	Debit money,
	Saldo money,
	Factor int
)

insert into @accountStatus
(Description, Credit, Debit, Saldo, Factor)
SELECT top 1  'Saldo inicial', 0 , 0, t.prepaid_balance, 1
FROM historicalClientNetPosition t
where CAST(t.net_position_date as date) = cast(@date as date)

insert into @accountStatus
(Description, Credit, Debit, Saldo, Factor)
select
                            tt.description AS Description,
                            CASE transaction_Type WHEN 100 THEN SUM(AMOUNT) ELSE 0 END Credit,
                            CASE transaction_Type WHEN 100 THEN 0 ELSE SUM(AMOUNT) END Debit,
							0 as saldo,
                            CASE transaction_Type WHEN 100 THEN 1 ELSE -1 END Factor
                            from (
	                            SELECT 1 as type, client_id, float_deposit_date transaction_Date, 100 transaction_Type, amount_deposited amount, reference
	                            FROM floatDeposits
	                            where cast(float_deposit_date  as date) > cast(@date as date) and client_id = @clientId
	                            UNION
	                            select 2 as type,client_id, deposit_date transaction_Date, 100 transaction_Type, amount_deposited amount, reference
	                            from deposits
	                            where cast(deposit_date  as date) > cast(@date as date)  and client_id = @clientId
	                            UNION
	                            SELECT 3 as type,client_id, transaction_date, transaction_type, amount, reference
	                            FROM registerPaidIncome
	                            where cast(transaction_date  as date) > cast(@date as date) and client_id = @clientId
	                            UNION
	                            select 4 as type,client_id, transaction_date, transaction_type, amount, reference
	                            from registerUnPaidIncome
	                            where cast(transaction_date  as date) > cast(@date as date) and client_id = @clientId
                            )A
                            INNER JOIN transactionsType tt on tt.transaction_type_id = A.transaction_Type
                            GROUP BY tt.description,tt.priority, A.transaction_Type
                            ORDER BY tt.priority DESC

declare @i int = 1, @rows int = (select count(*) from @accountStatus);

while @i <= @rows
begin

	if @i > 1
	begin
	declare @saldoAnterior as money = 0;

	select top 1 @saldoAnterior =  t.Saldo from @accountStatus t where t.Id < @I order by Id desc;

	update @accountStatus
	set Saldo = @saldoAnterior + case when Factor = 1 then Credit else (Debit * -1) end
	where Id = @i
	end
	set @i = @i + 1;
end

insert into @accountStatus
(Description, Credit, Debit, Saldo, Factor)
SELECT top 1  'Saldo Final', 0 , 0, t.Saldo, 1
FROM @accountStatus t
order by t.Id desc

select Id, Description, Credit, Debit, Saldo, Factor
from @accountStatus
order by Id



SELECT 1 as type, client_id, float_deposit_date transaction_Date, 100 transaction_Type, amount_deposited amount, reference
	                            FROM floatDeposits
	                            --where cast(float_deposit_date  as date) > cast(@date as date) and client_id = @clientId
	                            UNION
	                            select 2 as type,client_id, deposit_date transaction_Date, 100 transaction_Type, amount_deposited amount, reference
	                            from deposits
	                            --where cast(deposit_date  as date) > cast(@date as date)  and client_id = @clientId
	                            UNION
	                            SELECT 3 as type,client_id, transaction_date, transaction_type, amount, reference
	                            FROM registerPaidIncome
	                            --where cast(transaction_date  as date) > cast(@date as date) and client_id = @clientId
	                            UNION
	                            select 4 as type,client_id, transaction_date, transaction_type, amount, reference
	                            from registerUnPaidIncome
	                            --where cast(transaction_date  as date) > cast(@date as date) and client_id = @clientId