how to dynamic calculate the To date based on the next transaction posting date.

Mayuran Thiru 31 Reputation points
2022-09-06T00:23:24.623+00:00

Hello community, some help please with interest calculation for monthly debt calls.

I have a report where I would like to determine autonomously the "To" date. The "To" date is determined based on the next transaction posting date.
For example, I have a transaction posting date of 02-10-2021 and the subsequent transaction for this same entity on 05-03-2021. Therefore "To" date for this transaction should be 02-28-2021

Subsequently, the transaction posting date from 05-03-2021, the "To" date will depend on the next transaction posting date. If I have another transaction within the same month, for example occurring on 05-10-2021. Then the "To" date for the subsequent transaction will be 05-09-2021.

237992-image.png

Any help with this will be great. 237952-to-date.png

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,784 questions
0 comments No comments
{count} votes

Accepted answer
  1. Joyzhao-MSFT 15,561 Reputation points
    2022-09-06T04:42:56.447+00:00

    Hi @Mayuran Thiru ,
    At first, you need to obtain the next row value of the [From] field through the Lead Function when querying the dataset: SELCT LEAD([From]) OVER (ORDER BY Partner) AS NextValue.

    Since the SSRS dataset query does not support the "OVER" statement, the Common Table Expression (CTE) is referenced in the dataset query. The dataset query is as follows:

    ; WITH cte AS (  
    SELECT LEAD([From]) OVER (ORDER BY Partner) NextValue, *  
    FROM TableName)  
       
    SELECT * FROM cte  
    

    Through this query you will get an extra column [NextValue], which is the value of the next [From] field obtained after sorting according to the [Partner] field.

    237975-01.png

    Then insert a new column [To] on the right side of the [From] field, using the following expression:

    =IIF(  
    Month(Fields!From.Value)>=Month(Fields!NextValue.Value),   
    FORMAT(dateadd("d", -1, Fields!NextValue.Value),"MM-dd-yyyy"),  
    FORMAT(DateSerial(Year(Fields!From.Value), Month(Fields!From.Value), "1").AddMonths(1).AddDays(-1),"MM-dd-yyyy")  
    )  
    

    The expression means: when the month in the [From] field is equal to the value of the month in the [NextValue] field, the day before the [NextValue] field of the row is displayed; when the month in the [From] field is less than or greater than [NextValue] The value of the month in the field (in your case the [From] field will not be greater than the [NextValue] field), then the last day of the month in the [From] field is displayed.

    Design:

    237970-design.png

    Preview:

    238017-preview.png
    Best Regards,
    Joy


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1 additional answer

Sort by: Most helpful
  1. Mayuran Thiru 31 Reputation points
    2022-09-07T12:42:32.217+00:00

    ok thank you.