Home » SQL & PL/SQL » SQL & PL/SQL » date question
date question [message #418259] Thu, 13 August 2009 13:21 Go to next message
iverson_mania
Messages: 6
Registered: August 2009
Junior Member
I have a table

say create table book
(
...
..
..
startdate date,
enddate date
....
...);

from this table book. a guy loans a book for a period of time. all the information is stored.

for example. from january 1 2005 to january 1 2006 he loand specific book.


how can i retireve number of books loaned by a guy per month?
say first he loaned a book in january 1 2005, and now we are january 1 2009. and during this period he loaned 48 books. around 1 book
Re: date question [message #418260 is a reply to message #418259] Thu, 13 August 2009 13:28 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

You might need a calendar table which you can find by SEARCH this forum.

You'd probably benefit from reading up on TO_CHAR() & TO_DATE() functions
Re: date question [message #418264 is a reply to message #418259] Thu, 13 August 2009 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste what you already tried.
Post a working Test case: create table and insert statements along with the result you want with these data.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: date question [message #418268 is a reply to message #418259] Thu, 13 August 2009 13:58 Go to previous messageGo to next message
iverson_mania
Messages: 6
Registered: August 2009
Junior Member
create table equipment_loaned
(
startdate date,
enddate date,
customerid int,
foreign key ( customerid) references customer(customerid)

);


create table customer
(
customerid int,
name varchar(255),
primary key customerid
);


thats the two tables.

insert into customer
Values (1,'jack');

Insert into customer
values (2,'bob');

insert into equipment loaned
values (to_date('11/11/2008',MM/DD/YYYY), to_date('11/13/2008',MM/DD/YYYY), 1);

insert into equipment loaned
values (to_date('12/12/2008',MM/DD/YYYY), to_date('12/13/2008',MM/DD/YYYY), 1);


now i have this table


equipment loaned

customerid 1 start date 11/11/2008 enddate 11/13/2008
customerid 1 start date 12/12/2008 end date 12/13/2008


i need to retrieve how many equipments each customer loaned per month.

the result returned should be



  1. customerid 1
  2. number of equipment per month 0.25



  1. customerid 2
  2. number of equipment per month 0




the answer is 0.25 for the first one. because first equipment was loaned on 11/11/2008 ( i need lowest startdate for a customer) and sysdate ( 11 august 2009).. so 2 equipments over 8 months, which is 0.25.

someone help me with this query

[Updated on: Thu, 13 August 2009 14:03]

Report message to a moderator

Re: date question [message #418270 is a reply to message #418268] Thu, 13 August 2009 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is still NOT a full test case.
This is still NOT formatted.
There is still NO Oracle version.

Waiting for requested data...

Regards
Michel
Re: date question [message #418271 is a reply to message #418259] Thu, 13 August 2009 14:07 Go to previous messageGo to next message
iverson_mania
Messages: 6
Registered: August 2009
Junior Member
create table equipment_loaned
(
equipmentid int,
startdate date,
enddate date,
customerid int,
primary key (eqipmentid),
foreign key ( customerid) references customer(customerid)

);


create table customer
(
customerid int,
name varchar(255),
primary key customerid
);


thats the two tables.

now insert into tables this values


insert into customer
Values (1,'jack');

Insert into customer
values (2,'bob');

insert into equipment_loaned
values (1,to_date('11/11/2008','MM/DD/YYYY'), to_date('11/13/2008','MM/DD/YYYY'), 1);

insert into equipment_loaned
values (2,to_date('12/12/2008','MM/DD/YYYY'), to_date('12/13/2008','MM/DD/YYYY'), 1);



now i have this table


equipment loaned

customerid 1 start date 11/11/2008 enddate 11/13/2008
customerid 1 start date 12/12/2008 end date 12/13/2008


i need to retrieve how many equipments each customer loaned per month.

the result returned should be



1. customerid 1
2. number of equipment per month 0.25




1. customerid 2
2. number of equipment per month 0





the answer is 0.25 for the first one. because first equipment was loaned on 11/11/2008 ( i need lowest startdate for a customer) and sysdate ( 11 august 2009).. so 2 equipments over 8 months, which is 0.25.

someone help me with writing the query to retrive that result.

i am using oracle toad 9.0.1

[Updated on: Thu, 13 August 2009 14:10]

Report message to a moderator

Re: date question [message #418272 is a reply to message #418271] Thu, 13 August 2009 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
thats the two tables.

Quote:
now i have this table

We haven't nor your ORACLE version.

Regards
Michel
Re: date question [message #418273 is a reply to message #418259] Thu, 13 August 2009 14:12 Go to previous messageGo to next message
iverson_mania
Messages: 6
Registered: August 2009
Junior Member
whats problem with u?

i am doing a sample test, which i need help in.

i gave u the tables u need. plus isnerted values..
just need a query to retrieve that result.whats not understandable?

i am using toad oracle 9
Re: date question [message #418274 is a reply to message #418259] Thu, 13 August 2009 14:20 Go to previous messageGo to next message
iverson_mania
Messages: 6
Registered: August 2009
Junior Member
CREATE TABLE equipment_loaned (
  equipmentid INT,
  startdate   DATE,
  enddate     DATE,
  customerid  INT,
    PRIMARY KEY ( eqipmentid ),
    FOREIGN KEY ( customerid ) references customer(customerid));

create table customer
(
customerid int,
name varchar(255),
primary key customerid
);

INSERT INTO customer
VALUES     (1,
            'jack');

INSERT INTO customer
VALUES     (2,
            'bob');

INSERT INTO equipment_loaned
VALUES     (1,
            To_date('11/11/2008','MM/DD/YYYY'),
            To_date('11/13/2008','MM/DD/YYYY'),
            1);

INSERT INTO equipment_loaned
VALUES     (2,
            To_date('12/12/2008','MM/DD/YYYY'),
            To_date('12/13/2008','MM/DD/YYYY'),
            1);




Re: date question [message #418275 is a reply to message #418273] Thu, 13 August 2009 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TOAD version is NOT Oracle version.
Oracle is a RDBMS, TOAD is a program that query some RDBMS.
Do you see the difference?

Your test case is NOT working:
SQL> create table customer
  2  (
  3  customerid int,
  4  name varchar(255),
  5  primary key customerid
  6  );
primary key customerid
            *
ERROR at line 5:
ORA-00906: missing left parenthesis

SQL> CREATE TABLE equipment_loaned (
  2    equipmentid INT,
  3    startdate   DATE,
  4    enddate     DATE,
  5    customerid  INT,
  6      PRIMARY KEY ( eqipmentid ),
  7      FOREIGN KEY ( customerid ) references customer(customerid));
    PRIMARY KEY ( eqipmentid ),
                  *
ERROR at line 6:
ORA-00904: "EQIPMENTID": invalid identifier

Why should we have to debug your test case?

Quote:
the answer is 0.25 for the first one. because first equipment was loaned on 11/11/2008 ( i need lowest startdate for a customer) and sysdate ( 11 august 2009).. so 2 equipments over 8 months, which is 0.25.

How does it come that 2 equipements over 8 months become 0.25?
It may be obvious for you not for me.
Give the formula.

Regards
Michel


Re: date question [message #418276 is a reply to message #418259] Thu, 13 August 2009 15:27 Go to previous messageGo to next message
iverson_mania
Messages: 6
Registered: August 2009
Junior Member
okey am working with toad.

the formula is simple..
you have to take the first date the customer loaned an equipment.

imagine he loaned 5 with start dates( 12 january 2008, 11 novemeber 2008, 11 january 2009..)
you have to calculate from 12 january 2008 till now.
which means 20 months. we are august 13 2009 now ( using sysdate)

so he has loaned in 20 months 5 equipments, how much he loaned in one month??? 0.25 thats your answer.


CREATE TABLE equipment_loaned (
  equipmentid INT,
  startdate   DATE,
  enddate     DATE,
  customerid  INT,
    PRIMARY KEY ( equipmentid ),
    FOREIGN KEY ( customerid ) references customer(customerid));

create table customer
(
customerid int,
name varchar(255),
primary key (customerid)
);

INSERT INTO customer
VALUES     (1,
            'jack');

INSERT INTO customer
VALUES     (2,
            'bob');

INSERT INTO equipment_loaned
VALUES     (1,
            To_date('11/11/2008','MM/DD/YYYY'),
            To_date('11/13/2008','MM/DD/YYYY'),
            1);

INSERT INTO equipment_loaned
VALUES     (2,
            To_date('12/12/2008','MM/DD/YYYY'),
            To_date('12/13/2008','MM/DD/YYYY'),
            1);




this code is working with me.

[Updated on: Thu, 13 August 2009 15:30]

Report message to a moderator

Re: date question [message #418286 is a reply to message #418259] Thu, 13 August 2009 19:52 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>this code is working with me.
Claimed without proof which is provided below.
Ready, Fire, Aim.

I was going to provide the solution, but I have no test case to validate my SQL against.

SQL> @id6
SQL> CREATE TABLE equipment_loaned (
  2    equipmentid INT,
  3    startdate   DATE,
  4    enddate	   DATE,
  5    customerid  INT,
  6  	 PRIMARY KEY ( equipmentid ),
  7  	 FOREIGN KEY ( customerid ) references customer(customerid));
    FOREIGN KEY ( customerid ) references customer(customerid))
                                          *
ERROR at line 7:
ORA-00942: table or view does not exist


SQL> 
SQL> create table customer
  2  (
  3  customerid int,
  4  name varchar(255),
  5  primary key (customerid)
  6  );

Table created.

SQL> 
SQL> INSERT INTO customer
  2  VALUES	(1,
  3  		 'jack');

1 row created.

SQL> 
SQL> INSERT INTO customer
  2  VALUES	(2,
  3  		 'bob');

1 row created.

SQL> 
SQL> INSERT INTO equipment_loaned
  2  VALUES	(1,
  3  		 To_date('11/11/2008','MM/DD/YYYY'),
  4  		 To_date('11/13/2008','MM/DD/YYYY'),
  5  		 1);
INSERT INTO equipment_loaned
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 
SQL> INSERT INTO equipment_loaned
  2  VALUES	(2,
  3  		 To_date('12/12/2008','MM/DD/YYYY'),
  4  		 To_date('12/13/2008','MM/DD/YYYY'),
  5  		 1);
INSERT INTO equipment_loaned
            *
ERROR at line 1:
ORA-00942: table or view does not exist
Re: date question [message #418313 is a reply to message #418286] Fri, 14 August 2009 02:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Could we stop being so completely anal about the requirements please. If as much time was spent actually helping as is spent carping about posts, then this site would be a substantially better place.
If you're not actually going to help, then JUST DON'T POST. It's that easy.


@Black Swan - I'm sorry that the possibility of moving the two tables around in order to get the script to run didn't occur to you, but I guess we'll have to live without input on this one.


@iverson_mania
Apologies for the signal to noise ratio in the replies you've been getting so far. There is an ongoing disagreement round here about what quality of question merits a reply.

I would use a piece of SQL like this. It gets the earliest loan start date for a customer and works out how many months ago that was. You might want to play with the rounding a bit (possibly using FLOOR / CEIL instead of ROUND):
select customerid,round(count(equipmentid)/round(months_between(sysdate,min(startdate)),0),2) equip_per_month
from   equipment_loaned
group by customerid;
Re: date question [message #418318 is a reply to message #418313] Fri, 14 August 2009 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If you're not actually going to help,

I actually wanted to help
Quote:
JUST DON'T POST. It's that easy.

And I will post when I want to post until Frank ban me.

If people don't want this kind of answer, people just have to post a working test case (2 create table statements, 2 erroneous statements! - I'm not talking about the order - and OP says "this code is working with me"...) all the more OP needs it at least to test the answers we might give. It's that easy.
I admit it is harder to post an Oracle version.

Regards
Michel
Re: date question [message #418323 is a reply to message #418318] Fri, 14 August 2009 03:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I appreciate that you want to help Michel, but you have to admit that in this case you have provided no actual help to the OP.

I maintain that it would not have taken you much more time to fix the test case and demonstrate a way of solving the problem than it took you to make 3 posts about the inadequacies of the test case.
It would also make OraFAQ look more approachable and less officious.
Re: date question [message #418325 is a reply to message #418323] Fri, 14 August 2009 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I appreciate that you want to help Michel, but you have to admit that in this case you have provided no actual help to the OP.

Because until the last post of OP I didn't understand what was the desired result (note that he still did not give the formula) and your post answer to it so I had no reason to repeat the same thing I had in mind (but for floor/ceil/round point).

Regards
Michel
Re: date question [message #418327 is a reply to message #418323] Fri, 14 August 2009 03:31 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
[Devil'sAdvocateMode]
My tuppence Ha'penny:
Although I agree with you that sometimes many of us can be over the top when requiring posters to correctly post their code and other information, I think that there is often an underlying reason behind this. i.e. get the OP to think more about what they are doing. Get them to take more care when in ordering their thoughts and pulling their requirements together. Often the main problems occur because someone hasn't truly figured out what they actually need to do. Maybe if those of us who demand very precise test cases and requirements were to try to be a bit more understanding in our explanations as to exactly what we require and some of the reasons as to why we require it, it might go some way towards encouraging the OPs to put in a little bit more thought and effort into their questions.[/Devil'sAdvocateMode]

Edite: to fix poer spallign

[Updated on: Fri, 14 August 2009 03:33]

Report message to a moderator

Re: date question [message #418467 is a reply to message #418259] Sat, 15 August 2009 19:13 Go to previous messageGo to next message
hadweir
Messages: 8
Registered: August 2009
Location: Manchester United
Junior Member
you have to create a sequence, and assign it to start_date and end_date. so that it will be easy for to querry the table.
Re: date question [message #418468 is a reply to message #418467] Sat, 15 August 2009 19:55 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
hadweir wrote on Sun, 16 August 2009 02:13
you have to create a sequence, and assign it to start_date and end_date. so that it will be easy for to querry the table.


Hu? Does that have to do anything at all with the actual requirement? How will an ID make it any easier to figure out what month it it?
Previous Topic: update the char column to upper after insert or update to the same column
Next Topic: sysdate - sql query
Goto Forum:
  


Current Time: Tue Dec 06 06:15:46 CST 2016

Total time taken to generate the page: 0.15729 seconds