Home » SQL & PL/SQL » SQL & PL/SQL » Scenario Dates (oracle 12)
Scenario Dates [message #674939] Mon, 25 February 2019 01:13 Go to next message
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 #674941 is a reply to message #674939] Mon, 25 February 2019 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66471
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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.

Re: Scenario Dates [message #674942 is a reply to message #674941] Mon, 25 February 2019 03:29 Go to previous messageGo to next message
cookiemonster
Messages: 13642
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you want the last balance by date for each id for each month?
How do you decide the date range to include?
Re: Scenario Dates [message #674943 is a reply to message #674942] Mon, 25 February 2019 04:03 Go to previous messageGo to next message
satishtab89
Messages: 6
Registered: February 2019
Junior Member
id date balance
1 10-10-2018 99
1 11-10-2018 0

in the above case it has to return
id Balance
1 0
since in october month with respect to id =1 11-10-2018 is max date
same for other cases
Re: Scenario Dates [message #674944 is a reply to message #674943] Mon, 25 February 2019 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66471
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel 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.

Re: Scenario Dates [message #674945 is a reply to message #674943] Mon, 25 February 2019 04:08 Go to previous messageGo to next message
cookiemonster
Messages: 13642
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66471
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 25 February 2019 11:07
Michel 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13642
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 #674959 is a reply to message #674958] Tue, 26 February 2019 04:19 Go to previous messageGo to next message
satishtab89
Messages: 6
Registered: February 2019
Junior Member
select to_char(to_date(date1,'dd-mm-yyyy'),'dd-mm-yyyy') td from sdates;
Re: Scenario Dates [message #674960 is a reply to message #674959] Tue, 26 February 2019 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 13642
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've missed the point - you need to use to_date in the insert statements so everyone can run them.
Re: Scenario Dates [message #674961 is a reply to message #674960] Tue, 26 February 2019 05:37 Go to previous messageGo to next message
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)
Re: Scenario Dates [message #674962 is a reply to message #674961] Tue, 26 February 2019 05:45 Go to previous message
cookiemonster
Messages: 13642
Registered: September 2008
Location: Rainy Manchester
Senior Member
Again - oct doesn't work for people who don't use english. 10 always works.
Previous Topic: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction (merged)
Next Topic: INSERT and SELECT statements
Goto Forum:
  


Current Time: Thu Jul 18 14:34:00 CDT 2019