Query by date range with many nested queries

Navigation
0

I have to obtain a list with the clients of the company to which we have invoiced something during the current year, indicating how much we have invoiced each month.

I have two tables: the customer table (customer) and the invoice table (invoice). The relationship is obvious: a customer can have many bills, and each invoice is related to a single customer.

The only thing that has occurred to me is the following: 

select id, 
name as "Nombre",
identity_number as "CIF", 
(select sum(total) 
    from invoice 
    where payment_date between '2017-01-01' and '2017-02-01' and customer_id=c.id group by customer_id) as "Enero",
(select sum(total) 
    from invoice 
    where payment_date between '2017-02-01' and '2017-03-01' and customer_id=c.id group by customer_id) as "Febrero",
(select sum(total) 
    from invoice 
    where payment_date between '2017-03-01' and '2017-04-01' and customer_id=c.id group by customer_id) as "Marzo",
(select sum(total) 
    from invoice 
    where payment_date between '2017-04-01' and '2017-05-01' and customer_id=c.id group by customer_id) as "Abril",
(select sum(total) 
    from invoice 
    where payment_date between '2017-05-01' and '2017-06-01' and customer_id=c.id group by customer_id) as "Mayo",
(select sum(total) 
    from invoice 
    where payment_date between '2017-06-01' and '2017-07-01' and customer_id=c.id group by customer_id) as "Junio",
(select sum(total) 
    from invoice 
    where payment_date between '2017-07-01' and '2017-08-01' and customer_id=c.id group by customer_id) as "Julio",
(select sum(total) 
    from invoice 
    where payment_date between '2017-08-01' and '2017-09-01' and customer_id=c.id group by customer_id) as "Agosto",
(select sum(total) 
    from invoice 
    where payment_date between '2017-09-01' and '2017-10-01' and customer_id=c.id group by customer_id) as "Septiembre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-10-01' and '2017-11-01' and customer_id=c.id group by customer_id) as "Octubre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-11-01' and '2017-12-01' and customer_id=c.id group by customer_id) as "Noviembre",
(select sum(total) 
    from invoice 
    where payment_date between '2017-12-01' and '2017-12-31' and customer_id=c.id group by customer_id) as "Diciembre"
from customer c
where c.branding_id=1 and (select sum(total) 
    from invoice 
    where payment_date between '2017-01-01' and '2017-12-31' and customer_id=c.id group by customer_id) > 0

This works, but I find it extremely inefficient (there are 13 subqueries, if we include the WHERE one). Is there a way to simplify this query or is it the best way to get the data?

    
asked by Pablo Lozano 12.12.2017 в 11:43
source

2 answers

1

Based on the response of @amenadiel (which is correct for MySQL), I created the following query: 

select c.id, 
c.name as "Nombre",
c.identity_number as "CIF", 
  SUM(CASE WHEN date_part('month',i.payment_date)=12 then total else 0 end ) as diciembre,
  SUM(CASE WHEN date_part('month',i.payment_date)=11 then total else 0 end) as noviembre,
  ...
  SUM(CASE WHEN date_part('year',i.payment_date)=2017 then total else 0 end) as TOTAL
from customer c join invoice i on c.id=i.customer_id
where date_part('year',i.payment_date)=2017 and c.branding_id=2
group by c.id
order by c.id

As a curiosity, the query of the question needed almost two minutes to run while this solution takes around 300ms to show the same results

    
answered by 12.12.2017 / 13:31
source
3

EDIT: after writing this answer (for MySQL) I realized that the question was in PostgreSQL. The correct syntax is in the OP response.

First, the final clause (that the sum of payments from January to December 2017 is greater than zero) is the same as saying that payment_date is from the year 2017.

Second, assuming that your invoice table is and contains: 

create table invoice (customer_id integer, total integer, payment_date datetime);
insert into invoice values (1, 5, now());
insert into invoice values (1, 15, NOW() - INTERVAL 1 DAY);
insert into invoice values (1, 45, NOW() - INTERVAL 41 DAY);
insert into invoice values (1, 12, NOW() - INTERVAL 61 DAY);
insert into invoice values (1, 13, NOW() - INTERVAL 91 DAY);
insert into invoice values (2, 55, now());
insert into invoice values (2, 15, NOW() - INTERVAL 21 DAY);
insert into invoice values (3, 145, NOW() - INTERVAL 71 DAY);
insert into invoice values (3, 1, NOW() - INTERVAL 16 DAY);
insert into invoice values (3, 3, NOW() - INTERVAL 121 DAY);

you can go doing in each column: 

SUM(IF(MONTH(payment_date)=1, total, 0) as enero

This would look something like: 

select customer_id,
  SUM(IF(month(payment_date)=12,total,0)) as diciembre,
  SUM(IF(month(payment_date)=11,total,0)) as noviembre,
  SUM(IF(month(payment_date)=10,total,0)) as octubre,
  SUM(IF(month(payment_date)=9,total,0)) as septiembre,
  SUM(IF(month(payment_date)=8,total,0)) as agosto
from invoice
where year(payment_date)=2017
group by customer_id

I did not include all the months or the JOIN with the customer table, that part is the task, but if you want to list all the clients, including those who did not pay anything this year , the FROM should be 

FROM customer c LEFT JOIN invoice on customer_id=c.id AND YEAR(payment_date)=2017

Sample Fiddle

    
answered by 12.12.2017 в 12:46
How to know if there are numbers followed vertically and horizontally in a recursive c ++ matrix? Attach document in mail with c #