Doubt to make consultation with period of days

Asked

Viewed 25 times

0

Below I have the statement of an exercise and my query. In case I am not able to implement the part of the last 30 days.

I did some research and tested some things on forums, but nothing worked. Could anyone tell me what I could try?

Create a function (entitled fn_vendedores_ativos) returning TRUE for sellers with status ‘A’ (active) and having sales in the last 30 days. Your role will receive the code of a seller.

drop function if exists fn_vendedores_ativos(_codigo integer) ;

CREATE  OR  REPLACE FUNCTION  fn_vendedores_ativos(_codigo integer) RETURNS boolean AS  $$
DECLARE
BEGIN
 perform(select nome from vendedor vdd
          inner join venda v
          on v.codvendedor = vdd.codigo
          where v.codvendedor = _codigo
         and situacao = 'A')
         ;
 
 if not found then
 return false;
 else
 
 return true;
 end if;
 
END;
$$  LANGUAGE  plpgsql;

DATABASE


CREATE SCHEMA vendas;

SET search_path = vendas;

create table vendedor(
   codigo serial primary key,
   nome varchar(50) not null,
   ctps varchar(20) not null,
   situacao char(1) not null default 'A'
);

create table cliente(
   codigo serial primary key,
   nome varchar(50) not null,
   telefone char(10) not null
);

create table venda(
   codigo serial primary key,
   data date not null default current_date,
   codcliente int not null,
   codvendedor int not null,
   valor numeric(12, 2) not null,
   foreign key(codcliente) references cliente(codigo),
   foreign key(codvendedor) references vendedor(codigo)
);

insert into cliente(nome, telefone) values('Fulano', '1111111111');
insert into cliente(nome, telefone) values('Beltrano', '2222222222');
insert into cliente(nome, telefone) values('Sicrano', '3333333333');
insert into vendedor(nome, ctps, situacao) values('Jose', '1111', 'A');
insert into vendedor(nome, ctps, situacao) values('Maria', '2222', 'I');
insert into vendedor(nome, ctps, situacao) values('Ana', '3333', 'A');
insert into vendedor(nome, ctps, situacao) values('Henrique', '4444', 'I');
insert into vendedor(nome, ctps, situacao) values('Pedro', '5555', 'I');
insert into venda(data, codcliente, codvendedor, valor) values('2011-09-06', 1, 1, 200);
insert into venda(data, codcliente, codvendedor, valor) values('2012-06-09', 2, 2, 100);
insert into venda(data, codcliente, codvendedor, valor) values('2013-02-01', 3, 1, 1200);
insert into venda(data, codcliente, codvendedor, valor) values('2010-05-30', 2, 3, 1700);
insert into venda(data, codcliente, codvendedor, valor) values('2012-02-01', 1, 4, 1900);
insert into venda(data, codcliente, codvendedor, valor) values('2010-01-13', 1, 4, 500);
insert into venda(data, codcliente, codvendedor, valor) values('2013-03-01', 2, 1, 700);
insert into venda(data, codcliente, codvendedor, valor) values('2011-03-14', 1, 2, 350);
insert into venda(data, codcliente, codvendedor, valor) values('2012-03-22', 3, 1, 900);
insert into venda(data, codcliente, codvendedor, valor) values('2010-03-01', 1, 3, 5000);
insert into venda(data, codcliente, codvendedor, valor) values('2014-11-03', 3, 1, 1000);
insert into venda(data, codcliente, codvendedor, valor) values('2010-06-01', 1, 3, 2000);
insert into venda(data, codcliente, codvendedor, valor) values('2012-04-26', 1, 1, 3000);
insert into venda(data, codcliente, codvendedor, valor) values('2010-08-28', 1, 4, 4000);
  • Perhaps adding: AND v.data > CURRENT_DATE - 30.

  • I tried and it wasn’t :/

  • Post what tried to.

  • Instead of placing the query in parentheses after PERFORM simply replace SELECT with PERFORM.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.