Don’t duplicate questions, doesnt get you more answers.
1 Like
How to sort transactions by date and start from only remaining balances ? not all and random dates confuseing customers my template as below
{REPORT SQL DETAILS:
select Date,
CASE
When Credit > 0 Then 'Payment'
When Debit > 0 Then 'Invoice'
END as Name,
Debit-Credit as Amount from AccountTransactionValues
Where AccountId in (select top 1 AccountId from Entities where Name = '{ENTITY NAME}')
:F.Date,F.Name,F.Amount::<J00>{0} {1}|${2}
}
screen shot
This should order it by date in descending order
{REPORT SQL DETAILS:
select Date,
CASE
When Credit > 0 Then 'Payment'
When Debit > 0 Then 'Invoice'
END as Name,
Debit-Credit as Amount from AccountTransactionValues
Where AccountId in (select top 1 AccountId from Entities where Name = '{ENTITY NAME}') ORDER BY Date DESC
:F.Date,F.Name,F.Amount::<J00>{0} {1}|${2}
}
2 Likes
I am not that great at SQL but I reckon you would need to sum it and then select if the balance is not 0. Maybe one of the other members can help with this
The balances we see on the account screens are calculated in-app and are not found in the db.
The following will either show all transactions since last zero balance (including the transaction that zeroed the balance) or all transactions will be returned if there has never been a zero balance.
I, like in most instances, opted for a stored procedure. You can pull the actual processing DML from between the --start and --end comments and use in the report tag or a script. I’ve added a comment to line #35 where you’d need to change for passing {ENTITY NAME} if you go a different route.
/* Get entity account transactions after last zero balance */
IF OBJECT_ID('dbo.p_GetEntityAccountTransactionsSinceLastZeroBalance') IS NOT NULL
DROP PROCEDURE dbo.p_GetEntityAccountTransactionsSinceLastZeroBalance;
GO
CREATE PROCEDURE dbo.p_GetEntityAccountTransactionsSinceLastZeroBalance @EntityName NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
--start processing DML
BEGIN
DECLARE @Date DATETIME;
DECLARE @Name NVARCHAR(255);
DECLARE @Amount DECIMAL(16, 2);
DECLARE @LastZeroBalanceDate DATETIME;
DECLARE @AccountDetails TABLE
(
Date DATETIME NOT NULL,
Name NVARCHAR(255) NOT NULL,
Amount DECIMAL(16, 2) NOT NULL,
Balance DECIMAL(16, 2) NOT NULL
);
DECLARE cur_payments CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT Date,
CASE
WHEN Credit > 0 THEN
'Payment'
WHEN Debit > 0 THEN
'Invoice'
END AS Name,
Debit - Credit AS Amount
FROM dbo.AccountTransactionValues
WHERE AccountId = (SELECT TOP (1) AccountId FROM dbo.Entities WHERE Name = @EntityName) --change @EntityName back to '{ENTITY NAME}' if needed
ORDER BY Date;
OPEN cur_payments;
FETCH NEXT FROM cur_payments
INTO @Date,
@Name,
@Amount;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Balance DECIMAL(16, 2) = 0;
SELECT @Balance = ISNULL(SUM(Amount), 0) + @Amount
FROM @AccountDetails;
INSERT INTO @AccountDetails
( Date,
Name,
Amount,
Balance)
VALUES
(@Date, @Name, @Amount, @Balance);
FETCH NEXT FROM cur_payments
INTO @Date,
@Name,
@Amount;
END;
CLOSE cur_payments;
DEALLOCATE cur_payments;
SELECT TOP (1)
@LastZeroBalanceDate = Date
FROM @AccountDetails
WHERE Balance = 0
ORDER BY Date DESC;
IF (@LastZeroBalanceDate IS NOT NULL)
BEGIN
SELECT *
FROM @AccountDetails
WHERE Date >= @LastZeroBalanceDate
ORDER BY Date;
END;
ELSE
BEGIN
SELECT *
FROM @AccountDetails
ORDER BY Date;
END;
END;
--end processing DML
END;
GO
report like this: {REPORT SQL DETAILS:dbo.p_GetEntityAccountTransactionsSinceLastZeroBalance '{ENITY NAME}':F.Date,F.Name,F.Amount,F.Balance}
2 Likes