How to get Opening Balance Value

Opening Balance = Total Credit - Total Debit BEFORE :FromDate

You already have the main transactions between :FromDate and :ToDate, but the Opening Balance needs to be calculated separately using a subquery or a Common Table Expression (CTE).

 

Here's how you can get the Opening Balance

You can wrap your existing query with a UNION ALL block for the opening balance. Below is how you could do it:

-- Opening Balance before FromDate
SELECT 
    T3."ShortName" AS "Vendor Code",
    (SELECT MAX("CardName") FROM OCRD WHERE "CardCode" = T3."ShortName") AS "Vendor Name",
    NULL AS "Debit",
    SUM(T3."Credit") - SUM(T3."Debit") AS "Credit"
FROM "TEST"."OJDT" T2 
INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId"
WHERE 
    (T2."TransId" NOT IN (SELECT T0."StornoToTr" FROM OJDT T0 WHERE T0."StornoToTr" IS NOT NULL)) 
    AND (T2."StornoToTr" IS NULL)
    AND T3."ShortName" = :Vendor
    AND T2."TaxDate" < TO_DATE(:FromDate)
GROUP BY T3."ShortName"

UNION ALL

-- Journal Transactions in date range
SELECT  
    T3."ShortName" AS "Vendor Code",
    (SELECT MAX("CardName") FROM OCRD WHERE "CardCode" = T3."ShortName") AS "Vendor Name",
    T3."Debit", 
    T3."Credit"
FROM "TEST"."OJDT" T2 
INNER JOIN JDT1 T3 ON T2."TransId" = T3."TransId"
WHERE 
    (T2."TransId" NOT IN (SELECT T0."StornoToTr" FROM OJDT T0 WHERE T0."StornoToTr" IS NOT NULL)) 
    AND (T2."StornoToTr" IS NULL)
    AND T3."ShortName" = :Vendor
    AND T2."TaxDate" >= TO_DATE(:FromDate)
    AND T2."TaxDate" <= TO_DATE(:ToDate)

UNION ALL

-- Invoice transactions
SELECT 
    T2."CardCode" AS "Vendor Code",
    T2."CardName" AS "Vendor Name", 
    (T2."DocTotal" + T2."DpmAmnt" + T2."DpmVat") AS "Debit", 
    0 AS "Credit"
FROM "TEST"."OINV" T2
WHERE 
    T2."DocDate" >= TO_DATE(:FromDate) 
    AND T2."DocDate" <= TO_DATE(:ToDate)
    AND T2."CardCode" = :Vendor
    AND T2."CANCELED" NOT IN ('Y','C')
 

 

Notes

1. Opening Balance is shown as a row where only the Credit column is populated (Net balance = Credit - Debit before FromDate).

2. You can label the row as "Opening Balance" by replacing "Vendor Code" with a string like 'Opening Balance', but that may affect sorting or formatting.

3. If needed, you can use an alias or extra column to indicate that it's the opening balance row, like:

SELECT 'Opening Balance' AS "Description", ...
 

Hello Ramco,

Use this query which will help you out, Create CTE(Common table expression) and then filter records based on from date, Here we need to consider only date before from date. 

WITH OpeningBalance AS (
    SELECT 
        T3."ShortName" AS "Vendor Code",
        SUM(T3."Debit") - SUM(T3."Credit") AS "Opening Balance"
    FROM 
        "TEST"."OJDT" T2 
    INNER JOIN 
        JDT1 T3 ON T2."TransId" = T3."TransId"
    WHERE
        (T2."TransId" NOT IN (SELECT T0."StornoToTr" FROM OJDT T0 WHERE T0."StornoToTr" IS NOT NULL))
        AND T2."StornoToTr" IS NULL
        AND T2."TaxDate" < TO_DATE(:FromDate)
        AND T3."ShortName" = :Vendor
    GROUP BY 
        T3."ShortName"
)
SELECT 
    OB."Opening Balance",
    -- Other columns from your main query
    T3."ShortName" AS "Vendor Code",
    (SELECT MAX("CardName") FROM OCRD WHERE "CardCode" = T3."ShortName") AS "Vendor Name",
    T3."Debit", 
    T3."Credit"
FROM 
    OpeningBalance OB
LEFT JOIN (
    SELECT 
        T3."ShortName",
        T3."Debit",
        T3."Credit"
    FROM 
        "TEST"."OJDT" T2 
    INNER JOIN 
        JDT1 T3 ON T2."TransId" = T3."TransId"
    WHERE
        (T2."TaxDate" >= TO_DATE(:FromDate) AND T2."TaxDate" <= TO_DATE(:ToDate))
        AND T3."ShortName" = :Vendor
) T ON OB."Vendor Code" = T."ShortName";