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 |
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 |
|
Michel Cadot
Messages: 68665 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 |
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 |
|
Michel Cadot
Messages: 68665 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 #686371 is a reply to message #686368] |
Tue, 16 August 2022 08:25 |
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 |
|
Michel Cadot
Messages: 68665 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
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 23:38:51 CDT 2024
|