Home » SQL & PL/SQL » SQL & PL/SQL » Scenario Dates (oracle 12)
Scenario Dates [message #674939] |
Mon, 25 February 2019 01:13 |
|
satishtab89
Messages: 6 Registered: February 2019
|
Junior Member |
|
|
input data
ID Transaction ID Date Balance
1 22 10-10-2018 99
1 23 11-10-2018 0
1 24 02-11-2018 198
1 25 13-12-2018 50
2 3 08-11-2018 99
2 4 15-11-2018 0
3 66 29-10-2018 500
3 67 31-10-2018 100
3 68 11-11-2018 200
3 69 01-12-2018 150
3 70 04-12-2018 250
3 71 07-12-2018 50
4 13 26-11-2018 500
4 14 28-11-2018 300
4 15 29-11-2018 400
Output should be like this
Result
Balance for Oct-18
ID Balance
1 0
2 0
3 100
4 0
Balance for Nov-18
ID Balance
1 198
2 0
3 200
4 400
Balance for Dec-18
ID Balance
1 50
2 0
3 50
4 0
how to write a query?
|
|
|
|
|
|
|
Re: Scenario Dates [message #674945 is a reply to message #674943] |
Mon, 25 February 2019 04:08 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You'll want to use the keep option on the max aggregate.
If you want us to write an example for you then you should post a test case as Michel indicated above so we can recreate your table and data.
|
|
|
Re: Scenario Dates [message #674952 is a reply to message #674945] |
Tue, 26 February 2019 00:25 |
|
satishtab89
Messages: 6 Registered: February 2019
|
Junior Member |
|
|
create table sdates(ID number(2),Traid number(2),dates1 date,Balance number(3));
insert into sdates values(1,22,'10-oct-2018'99)
insert into sdates values(1,23,'11-oct-2018',10)
insert into sdates values(2,3,'08-nov-2018',99
insert into sdates values(2,23,'15-nov-2018',20)
insert into sdates values(3,69,'01-dec-2018',150)
insert into sdates values(3,70,'10-dec-2018',250)
insert into sdates values(4,11,'28-oct-2018',350)
insert into sdates values(4,15,'29-nov-2018',450)
insert into sdates values(4,11,'28-dec-2018',150)
the output should be shown month wise
Oct-18
ID Result
1 10
2 0
3 0
4 350
Nov-18
ID Result
1 0
2 20
3 0
4 450
Dec-18
ID Result
1 0
2 0
3 250
4 150
if id is same it will take max date with in same month
|
|
|
Re: Scenario Dates [message #674954 is a reply to message #674952] |
Tue, 26 February 2019 00:41 |
|
Michel Cadot
Messages: 68659 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Mon, 25 February 2019 11:07Michel Cadot wrote on Mon, 25 February 2019 08:57
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as 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.
Test and fix your test case.
SQL> create table sdates(ID number(2),Traid number(2),dates1 date,Balance number(3));
Table created.
SQL> insert into sdates values(1,22,'10-oct-2018'99)
2 insert into sdates values(1,23,'11-oct-2018',10)
3 insert into sdates values(2,3,'08-nov-2018',99
4 insert into sdates values(2,23,'15-nov-2018',20)
5 insert into sdates values(3,69,'01-dec-2018',150)
6 insert into sdates values(3,70,'10-dec-2018',250)
7 insert into sdates values(4,11,'28-oct-2018',350)
8 insert into sdates values(4,15,'29-nov-2018',450)
9 insert into sdates values(4,11,'28-dec-2018',150)
10 /
insert into sdates values(1,22,'10-oct-2018'99)
*
ERROR at line 1:
ORA-00917: missing comma
'10-oct-2018' is NOT a date, it is a string:
SQL> select to_date('10-oct-2018') from dual;
select to_date('10-oct-2018') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Read documentation about TO_DATE and apply the correct format (and take about we don't speak the same language).
|
|
|
Re: Scenario Dates [message #674957 is a reply to message #674954] |
Tue, 26 February 2019 01:15 |
|
satishtab89
Messages: 6 Registered: February 2019
|
Junior Member |
|
|
SQL> create table sdates(ID number(2),Traid number(2),dates1 date,Balance number(3));
Table created.
SQL> insert into sdates values(1,22,'10-oct-2018',99)
2 insert into sdates values(1,23,'11-oct-2018',10)
3 insert into sdates values(2,3,'08-nov-2018',19)
4 insert into sdates values(2,23,'15-nov-2018',20)
5 insert into sdates values(3,69,'01-dec-2018',150)
6 insert into sdates values(3,70,'10-dec-2018',250)
7 insert into sdates values(4,11,'28-oct-2018',350)
8 insert into sdates values(4,15,'29-nov-2018',450)
9 insert into sdates values(4,11,'28-dec-2018',150)
|
|
|
Re: Scenario Dates [message #674958 is a reply to message #674957] |
Tue, 26 February 2019 03:25 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You've left the line numbers in so we can't copy and paste it.
It won't work on any DB that doesn't have the session date format set to dd-mon-yyyy and is running in English (Michel's runs in French).
Use to_date with a format mask and numeric months so it works for everyone.
e.g.
to_Date('10-10-2018', 'DD-MM-YYYY')
|
|
|
|
|
Re: Scenario Dates [message #674961 is a reply to message #674960] |
Tue, 26 February 2019 05:37 |
|
satishtab89
Messages: 6 Registered: February 2019
|
Junior Member |
|
|
insert into sdates values(1,22,to_date('10-oct-2018','dd-mm-yyyy'),99)
insert into sdates values(1,23,to_date('11-oct-2018','dd-mm-yyyy'),10)
insert into sdates values(2,3,to_date('08-nov-2018','dd-mm-yyyy'),19)
insert into sdates values(2,23,to_date('15-nov-2018','dd-mm-yyyy'),20)
insert into sdates values(3,69,to_date('01-dec-2018','dd-mm-yyyy'),150)
insert into sdates values(3,70,to_date('10-dec-2018','dd-mm-yyyy'),250)
insert into sdates values(4,11,to_date('28-oct-2018','dd-mm-yyyy'),350)
insert into sdates values(4,15,to_date('29-nov-2018','dd-mm-yyyy'),450)
insert into sdates values(4,11,to_date('28-dec-2018','dd-mm-yyyy'),150)
|
|
|
|
Goto Forum:
Current Time: Mon Jun 17 01:21:07 CDT 2024
|