CODEWITHSUNDEEP
sqloracle-database
vinny
vinny

Reputation: 19

extract date from pl/sql

I have some issues while writting a function which return by month, year and the id of a doctor, the number of consultations he did.

The problem is: I have, as a date, any consultations of a doctor so I don't know how I can use this to sort by month of a year the number of consultations he did.

I have to write a function that returns the number of times a doctor visits a given month. The function will take as a parameter a doctor identifier, a month and one year.

Here is the table consultation:

CREATE TABLE consultation(
    n_med       integer constraint fk_consultation_medecin references medecin(n_med),
    n_malade        integer constraint fk_consultation_malade references
                    malade(n_malade) on delete cascade deferrable initially deferred,
    date_consult    date,
    h_consult   integer not null,
    prescription    varchar2(128),
    examen      varchar2(128),
    constraint pk_consultation primary key(n_med, n_malade, date_consult)
);

and here is the function : 

FUNCTION getNbConsult(id_med IN integer, mois IN integer, annee IN integer)  
    RETURN integer 
IS 
    nbConsult integer; 
BEGIN 
   select count(*) into nbConsult from consultation where N_MED = id_med and date_consult = EXTRACT(MONTH FROM DATE ???)
   RETURN nbConsult; 
END; 
/

Upvotes: 1

Views: 125

Answers (2)

MT0
MT0

Reputation: 167822

You can use to_char(date_consult,'YYYYMM') = TO_CHAR( annee, 'FM0000' ) || TO_CHAR( mois, 'FM00' ) but then Oracle will not be able to use an index on the date_consult column (and would require a function-based index) instead you could use:

FUNCTION getNbConsult(id_med IN integer, mois IN integer, annee IN integer)  
  RETURN integer 
IS 
  nbConsult integer;
  month DATE := TO_DATE( TO_CHAR( annee, 'FM0000' ) || TO_CHAR( mois, 'FM00' ), 'yyyymm' );
BEGIN
  SELECT count(*) 
  INTO   nbConsult 
  FROM   consultation 
  WHERE  date_consult >= month            
  AND    date_consult <  ADD_MONTHS( month, 1 );
  RETURN nbConsult; 
END; 
/

Upvotes: 1

Rene
Rene

Reputation: 10541

Don't know how the year and month are specified in your situation but you could select records for a specific year and month as follows:

select count(*) 
into nbConsult 
from consultation 
where to_char(date_consult,'YYYYMM') = '201709';

Ah, integers. So you can change it to 

select count(*) 
    into nbConsult 
    from consultation 
    where to_char(date_consult,'YYYYMM') = annee||lpad(mois,2,'0');

Upvotes: 0

Related Questions