This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,810 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Categories

How can I populate missing values in a balance sheet?

User_ZR3PE
User_ZR3PE Member Posts: 26 Green Ribbon
in SQL & PL/SQL


I have a balance sheet table but it does not show start balance of the days. How can I populate this information?


image.png


image.png
image.png


Tagged:
· Share on TwitterShare on Facebook

Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    Answer ✓

    Something like:

    with balance_sheet as (
                      select to_date('5.11.2022 15:28:27','dd.mm.yyyy hh24:mi:ss') dt,'Deposit' trx_type,-632 amt,645.18 balance from dual union all
                      select to_date('5.11.2022 15:27:51','dd.mm.yyyy hh24:mi:ss') dt,'Deposit' trx_type,1769 amt,1277.18 balance from dual union all
                      select to_date('2.11.2022 19:46:11','dd.mm.yyyy hh24:mi:ss') dt,'Withdraw' trx_type,-500 amt,-491.82 balance from dual union all
                      select to_date('1.11.2022 11:30:33','dd.mm.yyyy hh24:mi:ss') dt,'Withdraw' trx_type,-300 amt,8.18 balance from dual
                     ),
     t as (
           select  b.*,
                   trunc(lead(dt,1,dt) over(order by dt)) - trunc(dt) +
                   case lead(dt,1,dt) over(order by dt)
                     when trunc(lead(dt,1,dt) over(order by dt)) then 0
                     else 1
                   end gap
             from  balance_sheet b
          )
select  to_char(case l when gap then dt else trunc(dt) + l end,'dd.mm.yyyy hh24:mi:ss') "Date",
        case l when gap then trx_type end "Transaction Type",
        case l when gap then amt end "Amount",
        balance "Balance"
  from  t,
        lateral(
                select  level l                        
                  from  dual                  
                  connect by level <= gap
               )
  order by case l when gap then dt else trunc(dt) + l end desc
/

Date                Transaction Type     Amount    Balance
------------------- ---------------- ---------- ----------
05.11.2022 15:28:27 Deposit                -632     645.18
05.11.2022 15:27:51 Deposit                1769    1277.18
05.11.2022 00:00:00                                -491.82
04.11.2022 00:00:00                                -491.82
03.11.2022 00:00:00                                -491.82
02.11.2022 19:46:11 Withdraw               -500    -491.82
02.11.2022 00:00:00                                   8.18
01.11.2022 11:30:33 Withdraw               -300       8.18

8 rows selected.

SQL>

    SY.

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    Answer ✓

    Please point to where, in your initial post, you mentioned anything about the "Friday-Saturday-Sunday" business.

    If you can't, please explain.

    · Share on TwitterShare on Facebook

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    Answer ✓

    Something like:

    with balance_sheet as (
                      select to_date('5.11.2022 15:28:27','dd.mm.yyyy hh24:mi:ss') dt,'Deposit' trx_type,-632 amt,645.18 balance from dual union all
                      select to_date('5.11.2022 15:27:51','dd.mm.yyyy hh24:mi:ss') dt,'Deposit' trx_type,1769 amt,1277.18 balance from dual union all
                      select to_date('2.11.2022 19:46:11','dd.mm.yyyy hh24:mi:ss') dt,'Withdraw' trx_type,-500 amt,-491.82 balance from dual union all
                      select to_date('1.11.2022 11:30:33','dd.mm.yyyy hh24:mi:ss') dt,'Withdraw' trx_type,-300 amt,8.18 balance from dual
                     ),
     t as (
           select  b.*,
                   trunc(lead(dt,1,dt) over(order by dt)) - trunc(dt) +
                   case lead(dt,1,dt) over(order by dt)
                     when trunc(lead(dt,1,dt) over(order by dt)) then 0
                     else 1
                   end gap
             from  balance_sheet b
          )
select  to_char(case l when gap then dt else trunc(dt) + l end,'dd.mm.yyyy hh24:mi:ss') "Date",
        case l when gap then trx_type end "Transaction Type",
        case l when gap then amt end "Amount",
        balance "Balance"
  from  t,
        lateral(
                select  level l                        
                  from  dual                  
                  connect by level <= gap
               )
  order by case l when gap then dt else trunc(dt) + l end desc
/

Date                Transaction Type     Amount    Balance
------------------- ---------------- ---------- ----------
05.11.2022 15:28:27 Deposit                -632     645.18
05.11.2022 15:27:51 Deposit                1769    1277.18
05.11.2022 00:00:00                                -491.82
04.11.2022 00:00:00                                -491.82
03.11.2022 00:00:00                                -491.82
02.11.2022 19:46:11 Withdraw               -500    -491.82
02.11.2022 00:00:00                                   8.18
01.11.2022 11:30:33 Withdraw               -300       8.18

8 rows selected.

SQL>

    SY.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    Hi, @User_ZR3PE

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    · Share on TwitterShare on Facebook
  • User_ZR3PE
    User_ZR3PE Member Posts: 26 Green Ribbon

    I want to find out min value for each day for everyday in the month. But i will combine Friday-Saturday-Sunday as 3 interrupted day. For example if monday has lowest value 100 I take 100, but if Friday has 200, Saturday has 50 and Sunday has 150 as lowest value I will take 50 as lowest value for Friday-Saturday-Sunday. For that reason I can not simply lookup previous balance value .

    Oracle version : 11.2.0.1.0

    /* CREATE TABLE */

    CREATE TABLE overdraft(

    TrasactionDate DATE,

    Transaction_Type VARCHAR(100),

    Amount NUMERIC(6, 2),

    Balance NUMERIC(6, 2)

    );


    /* INSERT QUERY NO: 1 */

    INSERT INTO overdraft (TrasactionDate, Transaction_Type, Amount, Balance)

    VALUES

    (TO_DATE('05.11.2022 15:28:27', 'dd/mm/yyyy hh24:mi:ss')

    , 'Deposit', -632, 645.18

    );


    /* INSERT QUERY NO: 2 */

    INSERT INTO overdraft (TrasactionDate, Transaction_Type, Amount, Balance)

    VALUES

    (TO_DATE('5/11/2022 15:27:41', 'dd/mm/yyyy hh24:mi:ss'), 'Deposit', 1769, 1277.18

    );


    /* INSERT QUERY NO: 3 */

    INSERT INTO overdraft (TrasactionDate, Transaction_Type, Amount, Balance)

    VALUES

    (TO_DATE('2/11/2022 19:46:11', 'dd/mm/yyyy hh24:mi:ss'), 'Withdraw', -500, -491.82

    );


    /* INSERT QUERY NO: 4 */

    INSERT INTO overdraft (TrasactionDate, Transaction_Type, Amount, Balance)

    VALUES

    (TO_DATE('1/11/2022 11:30:33', 'dd/mm/yyyy hh24:mi:ss'), 'Withdraw', -300, 8.18

    );

    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond

    BTW, one of Dr Codd's rules was you shouldn't store information that can be derived, so in your case it should be calculated and exposed via a view or MView.

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    Answer ✓

    Please point to where, in your initial post, you mentioned anything about the "Friday-Saturday-Sunday" business.

    If you can't, please explain.

    · Share on TwitterShare on Facebook
  • User_ZR3PE
    User_ZR3PE Member Posts: 26 Green Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16854846#Comment_16854846

    Yes I want to populate them for calculation , i do not want to insert them into database.

    · Share on TwitterShare on Facebook
  • User_ZR3PE
    User_ZR3PE Member Posts: 26 Green Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16854848#Comment_16854848

    I miss clicked as answer.

    I want to group them like below.

    image.png
    image.png
    image.png


    image.png
    image.png


    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 11,041 Black Diamond

    I think you made a mess of your question. You mention "min value" per day - but this wasn't in the initial question either.

    Are you only looking to populate the beginning balance each day, so that - in a later step - you can choose the min balance each day? That is - if there are withdrawals during the day, the min balance may be intra-day, not the opening balance - and it may not be the closing balance for the day either, if after the withdrawal there is a deposit? And also, in this later processing, you are going to combine Fri-Sat-Sun and take the min balance over the 72 hours, and assign the same min balance to each of Fri, Sat, Sun? (As an aside, I wonder if that's what my bank does; and if it is, how they justify it. This seems like a dirty trick to me.)

    If that is your problem, it would be best to state it as such. Perhaps there are ways to combine the problems - get a solution that does everything in one go. But if in fact that is your problem, not the partial problem you showed us, then I expect that the desired output is also different; perhaps with one row per day, showing just the min balance (with the Fri-Sat-Sun business also handled as described).

    · Share on TwitterShare on Facebook
  • User_ZR3PE
    User_ZR3PE Member Posts: 26 Green Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16854856#Comment_16854856

    It may changes country to country but probably they do the same thing because in saturday and sunday market is closed, bank can not lend the money again if you pay saturday and sunday. So payments are considered made the next business day.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    https://community.oracle.com/tech/developers/discussion/comment/16854851#Comment_16854851

    So, you want only two columns in the result set: Lowest_Balance and Weekday. Is that right?

    I want to find out min value for each day for everyday in the month. 

    Do you want one row in the result set for

    • every day in the month (which month?)
    • every day in the month except Saturdays and Sundays
    • every day in the month up to and including the last TransactionDate in the table
    • every day in the month except Saturdays and Sundays up to and including the last TransactionDate in the table
    • every day in the month that is a TransactionDate in the table
    • something else (explain exactly what)

    ?

    What would be the desired results of you added the following two rows to the sample data? 

    INSERT INTO overdraft (TransactionDate,                           Transaction_Type, Amount, Balance)
VALUES (TO_DATE ('01.11.2022 10:00:00', 'dd/mm/yyyy hh24:mi:ss'), 'Deposit',        500,    308.18);
INSERT INTO overdraft (TransactionDate,                           Transaction_Type, Amount, Balance)
VALUES (TO_DATE ('31.10.2022 11:00:00', 'dd/mm/yyyy hh24:mi:ss'), 'Withdraw',       -100,   -191.82);

    Why?

    If the last day of the month is a Friday, do you want any transactions made on the 1st or 2nd of the following month treated as if they were on the previous Friday?

    · Share on TwitterShare on Facebook