Home » SQL & PL/SQL » SQL & PL/SQL » Retrieve last month running total when current month is null (ORACLE 11G)
Retrieve last month running total when current month is null [message #686366] Tue, 16 August 2022 04:10 Go to next message
Lim
Messages: 20
Registered: January 2003
Junior Member
I will like to display the running total from previous month when this month data is null for that loc column. As I need to display monthly column wise so if the month without value display my ending balance is going to be incorrect. So, I need to display the value of last month since is in running total if there is no data in that particular month.

create table TT (loc varchar(10), tdate VARCHAR(10), Total number, rtotal number );

insert into tt ( loc, tdate, total, rtotal) values ( 'CM', '2022-06', 300, 300 );
insert into tt ( loc, tdate, total, rtotal) values ( 'CM', '2022-07', 500, 800 );
insert into tt ( loc, tdate, total, rtotal) values ( 'CP', '2022-06', 275, 275 );

my expected output will be


LOC TDATE    TOTAL RTOTAL
CM  2022-06  300   300
CM  2022-07  500   800
CP  2022-06  275   275
CP  2022-07  0     275

Thanks
Re: Retrieve last month running total when current month is null [message #686367 is a reply to message #686366] Tue, 16 August 2022 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Mon, 01 November 2021 08:15

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And to prevent from the same issues than in this one:

Michel Cadot wrote on Mon, 01 November 2021 09:18

Read again my last sentence and post accordingly. a test case must representative of your data.
Read again my last but one sentence and accordingly.

Michel Cadot wrote on Mon, 01 November 2021 10:53

Please, test your test case before posting it.

[Updated on: Tue, 16 August 2022 05:39]

Report message to a moderator

Re: Retrieve last month running total when current month is null [message #686368 is a reply to message #686367] Tue, 16 August 2022 08:16 Go to previous messageGo to next message
Lim
Messages: 20
Registered: January 2003
Junior Member
Sorry, the tdate suppose to be date field from my raw data, but I have sum it with YYYY-MM format.

create table TT (loc varchar(10), tdate date, Total number);

insert into tt ( loc, tdate, total, rtotal) values ( 'CM', to_date('01/06/2022', 'DD/MM/YYYY') , 300);
insert into tt ( loc, tdate, total, rtotal) values ( 'CM', TO_DATE('02/07/2022','DD/MM/YYYY'), 500 );
insert into tt ( loc, tdate, total, rtotal) values ( 'CP', TO_DATE('02/06/2022', 'DD/MM/YYYY') , 275 );

My expected output is to sum total of the month and put into YYYY-MM format of running total by different loc, and with running total. My current problem if the loc having no data at month 2022-07 then the whole line not showing as no data but I need it to show the sum from the previous month into the running total column. Hence, my expected output is :-

Loc    tdate    Total Rtotal
CM     2022-06  300   300
CM     2022-07  500   800
CP     2022-06  275   275
CP     2022-07  0     275

Hope this is OK now. Thanks
Re: Retrieve last month running total when current month is null [message #686369 is a reply to message #686368] Tue, 16 August 2022 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said:

Michel Cadot wrote on Tue, 16 August 2022 12:38

...Michel Cadot wrote on Mon, 01 November 2021 10:53

Please, test your test case before posting it.
SQL> insert into tt ( loc, tdate, total, rtotal) values ( 'CM', to_date('01/06/2022', 'DD/MM/YYYY') , 300);
insert into tt ( loc, tdate, total, rtotal) values ( 'CM', to_date('01/06/2022', 'DD/MM/YYYY') , 300)
                                            *
ERROR at line 1:
ORA-00947: not enough values
Re: Retrieve last month running total when current month is null [message #686370 is a reply to message #686369] Tue, 16 August 2022 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, can there be multiple rows for the same month and loc?

Re: Retrieve last month running total when current month is null [message #686371 is a reply to message #686368] Tue, 16 August 2022 08:25 Go to previous messageGo to next message
Lim
Messages: 20
Registered: January 2003
Junior Member
Sorry... The correct one

create table TT (loc varchar(10), tdate date, Total number);

insert into tt ( loc, tdate, total) values ( 'CM', to_date('01/06/2022', 'DD/MM/YYYY') , 300);
insert into tt ( loc, tdate, total) values ( 'CM', TO_DATE('02/07/2022','DD/MM/YYYY'), 500 );
insert into tt ( loc, tdate, total) values ( 'CP', TO_DATE('02/06/2022', 'DD/MM/YYYY') , 275 );

Re: Retrieve last month running total when current month is null [message #686372 is a reply to message #686371] Tue, 16 August 2022 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with months as (select distinct to_char(tdate,'YYYY-MM') mnth from tt)
  2  select loc, mnth, nvl(sum(total),0) total,
  3         sum(sum(total)) over (partition by loc order by mnth) rtotal
  4  from months left outer join tt partition by (loc) on to_char(tdate,'YYYY-MM') = mnth
  5  group by loc, mnth
  6  order by loc, mnth
  7  /
LOC        MNTH         TOTAL     RTOTAL
---------- ------- ---------- ----------
CM         2022-06        300        300
CM         2022-07        500        800
CP         2022-06        275        275
CP         2022-07          0        275
Re: Retrieve last month running total when current month is null [message #686373 is a reply to message #686372] Tue, 16 August 2022 08:48 Go to previous messageGo to next message
Lim
Messages: 20
Registered: January 2003
Junior Member
Thanks.. Can you please quote in oracle sql as I am not familiar with sql plus.
Re: Retrieve last month running total when current month is null [message #686374 is a reply to message #686373] Tue, 16 August 2022 08:51 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL*Plus executes (or rather sends) Oracle SQL.
SQL*Plus is just a client tool to send SQL requests to Oracle databases.

[Updated on: Tue, 16 August 2022 08:54]

Report message to a moderator

Previous Topic: trigger not update the column
Next Topic: PIPE ROW
Goto Forum:
  


Current Time: Fri Mar 29 01:23:35 CDT 2024