CODEWITHSUNDEEP
sqlsql-serverdatabaseselectzoho
DaVinci007
DaVinci007

Reputation: 83

LAG and LEAD alternative in SQL syntax

My Table

Hi there,

I am writing an SQL query to derive closing balance and value of which shall be used to calculate opening balance for next row and so on. My Table structure is pasted in the image above code is as mentioned below :

SELECT
         t1."Document Date" as txn_dt,
         t1."GL Account Code" as gl_code,
         t1."GL Account Description" as cst_name,
         t1."Debit Amount" as debit,
         t1."Credit Amount" as credit,
         t2."Opening Balance" as op_bal,
         t1."Debit Amount" + t1."Credit Amount" + t2."Opening Balance" as closing_bal
FROM  "Trial Balance 1617" t1,
     "GL Master Account" t2 
WHERE    t1."GL Account Code"  = t2."GL Account Code"
 AND    t1."GL Account Code"  = 'A3010101B058'

Please note that LAG and LEAD is unsupported in ZOHO reports Can someone please guide me ?

Upvotes: 1

Views: 428

Answers (1)

Max Szczurek
Max Szczurek

Reputation: 4334

Assuming "Trial Balance 1617" is a rollup table that has a unique index on GL Account Code and Document Date, you can just manually calculate sums of debits and credits leading up to a particular entry like this:

create table "Trial Balance 1617" ("Document Date" date, "GL Account Code" varchar(25), 
    "Debit Amount" numeric(12,2), "Credit Amount" numeric(12,2))
create table "GL Master Account" ("GL Account Code" varchar(25), "Opening Balance" numeric(12,2))

insert into "GL Master Account" values ('A3010101B058', '8110339.14')
insert into "Trial Balance 1617" values ('4/1/2016', 'A3010101B058', 5332269.28, 0)
insert into "Trial Balance 1617" values ('4/2/2016', 'A3010101B058', 741674.9, 0)
insert into "Trial Balance 1617" values ('4/4/2016', 'A3010101B058', 570253.96, 0)
insert into "Trial Balance 1617" values ('4/5/2016', 'A3010101B058', 0, -148839.52)

SELECT
    t1."Document Date" as txn_dt,
    t1."GL Account Code" as gl_code,
    t1."Debit Amount" as debit,
    t1."Credit Amount" as credit,
    case 
    when not exists 
        (select * from "Trial Balance 1617" where "GL Account Code" = t1."GL Account Code" and "Document Date" < t1."Document Date")
            then t2."Opening Balance" 
    else
        t2."Opening Balance" + (select sum("Debit Amount") from "Trial Balance 1617" where "GL Account Code" = t1."GL Account Code" and "Document Date" < t1."Document Date")
            - (select sum("Credit Amount") from "Trial Balance 1617" where "GL Account Code" = t2."GL Account Code" and "Document Date" < t1."Document Date") 
            - (select sum("Credit Amount") from "Trial Balance 1617" where "GL Account Code" = t2."GL Account Code" and "Document Date" < t1."Document Date")

    end as op_bal,
    case 
    when not exists 
        (select * from "Trial Balance 1617" where "GL Account Code" = t1."GL Account Code" and "Document Date" < t1."Document Date")
            then t2."Opening Balance" 
    else
        t2."Opening Balance" + (select sum("Debit Amount") from "Trial Balance 1617" where "GL Account Code" = t1."GL Account Code" and "Document Date" < t1."Document Date")
            - (select sum("Credit Amount") from "Trial Balance 1617" where "GL Account Code" = t2."GL Account Code" and "Document Date" < t1."Document Date") 
            - (select sum("Credit Amount") from "Trial Balance 1617" where "GL Account Code" = t2."GL Account Code" and "Document Date" < t1."Document Date")

    end + t1."Debit Amount" + t1."Credit Amount" as closing_bal
FROM "Trial Balance 1617" t1
INNER JOIN "GL Master Account" t2 ON t1.[GL Account Code] = t2.[GL Account Code]
WHERE t1."GL Account Code"  = 'A3010101B058'

Returns:

txn_dt      gl_code         debit       credit      op_bal      closing_bal
2016-04-01  A3010101B058    5332269.28  0.00        8110339.14  13442608.42
2016-04-02  A3010101B058    741674.90   0.00        13442608.42 14184283.32
2016-04-04  A3010101B058    570253.96   0.00        14184283.32 14754537.28
2016-04-05  A3010101B058    0.00        -148839.52  14754537.28 14605697.76

If you can use CTEs, you can clean it up a lot by calculating the opening balance in a separate query:

;with cte_op_balance as
(
    SELECT
         t1."Document Date" as txn_dt,
         t1."GL Account Code" as gl_code,
         case 
            when not exists 
                (select * from "Trial Balance 1617" where "GL Account Code" = t1."GL Account Code" and "Document Date" < t1."Document Date")
                    then t2."Opening Balance" 
            else
                t2."Opening Balance" + (select sum("Debit Amount") from "Trial Balance 1617" where "GL Account Code" = t1."GL Account Code" and "Document Date" < t1."Document Date")
                    - (select sum("Credit Amount") from "Trial Balance 1617" where "GL Account Code" = t2."GL Account Code" and "Document Date" < t1."Document Date") 
                    - (select sum("Credit Amount") from "Trial Balance 1617" where "GL Account Code" = t2."GL Account Code" and "Document Date" < t1."Document Date")

         end as op_bal
        FROM "Trial Balance 1617" t1
        INNER JOIN "GL Master Account" t2 ON t1.[GL Account Code] = t2.[GL Account Code]
        WHERE t1."GL Account Code"  = 'A3010101B058'
)
SELECT
         t1."Document Date" as txn_dt,
         t1."GL Account Code" as gl_code,
         t1."Debit Amount" as debit,
         t1."Credit Amount" as credit,
         c.op_bal,
         c.op_bal + t1."Debit Amount" + t1."Credit Amount" as closing_bal
FROM "Trial Balance 1617" t1
INNER JOIN "GL Master Account" t2 ON t1.[GL Account Code] = t2.[GL Account Code]
INNER JOIN cte_op_balance c ON t1.[GL Account Code] = c.gl_code and t1.[Document Date] = c.txn_dt
WHERE t1."GL Account Code"  = 'A3010101B058'

Upvotes: 1

Related Questions