How to count the number of clients that have returned from one year to the other through SQL?

I was able to solve the problem as follows:

SELECT * FROM(
    SELECT DISTINCT(os1.cdcliente)
    FROM ordemservico os1
    WHERE os1.auxordemservico = '/12'
        AND EXISTS (
            SELECT os1.cdcliente
            FROM ordemservico os2
            WHERE os2.auxordemservico = '/13'
                AND os1.cdcliente = os2.cdcliente
        )
) AS temp_table

It searches line by line for all records of "/ 12" which are also in "/ 13"

You need to search for the year using YEAR that serves to extract the year in string format so you can compare, and give group by to Client for example:

SELECT count(*) FROM Servicos WHERE YEAR(Data)='2012' OR YEAR(Data)='2011' GROUP BY CLiente

This select was made based on the data you passed to us.

Sorry for my mistake. Try:

SELECT cliente.nmcliente 
FROM cliente 
INNER JOIN (SELECT o1.cdcliente FROM ordemservico o1 WHERE o1.auxordemservico = '/12') AS r1
   ON r1.cdcliente = cliente.cdcliente
INNER JOIN (SELECT o1.cdcliente FROM ordemservico o1 WHERE o1.auxordemservico = '/13') AS r2
   ON r2.cdcliente = cliente.cdcliente

Due to INNER JOIN will only select those that exist in the 2 years.

Depending on your DBMS you can use INTERSECT:

SELECT cliente.nmcliente
FROM cliente 
INNER JOIN 
    ((SELECT cdcliente FROM ordemservico WHERE auxordemservico = '/12')
        INTERSECT
    (SELECT cdcliente FROM ordemservico WHERE auxordemservico = '/13')) AS r
   ON cliente.cdcliente = r.cdcliente)

See 3 options in this test in SQL Fiddle:
link

To achieve this result you can use numerous solutions, one of them follows.

Suppose the following data schema exists:

CREATE TABLE Servicos
(
  Nro_Serv int primary key,
  Data date,
  Cliente int,
  Valor float
);

INSERT INTO Servicos
(Nro_Serv, Data, Cliente, Valor)
VALUES
(1, '2011-03-01', 200, 53.66),
(2, '2011-03-05', 200, 108.90),
(3, '2012-01-25', 200, 85.45),
(4, '2012-02-09', 300, 785.45),
(5, '2013-11-09', 300, 77.45),
(6, '2014-01-22', 400, 87.45),
(7, '2011-03-05', 300, 778.90);

Based on this scheme we can use the following query:

select cliente 
from Servicos 
where YEAR(data) = '2011' 
group by cliente
UNION
select cliente 
from Servicos 
where YEAR(data) = '2012' 
group by cliente

Basically there are two queries, each grouping per client and filtering per year. Then one of them is for year 2011 and the other for year 2012 that in the end are united using the operator UNION, which results in what is expected.

To test the above proposal, see everything in sqlfiddle .

Adapt this script to the fields in your table.

Return is just the idClient without the reps.

select o.idCliente from ordem as o where o.idCliente IN (SELECT o.idCliente from cliente as c inner join ordem as o ON c.idcliente = o.idCliente where o.ano = '2013') and o.ano = '2012' group by o.idcliente;

Here's my contribution. I ran tests and it worked.

create table ORDEMSERVICO
(
    CDCLIENTE int,
    AUXORDEMSERVICO varchar(3)
);

insert into  ORDEMSERVICO values (12, '/12');
insert into  ORDEMSERVICO values (13, '/13');
insert into  ORDEMSERVICO values (25, '/12');
insert into  ORDEMSERVICO values (25, '/13');

select
  CDCLIENTE,
  sum(case when AUXORDEMSERVICO = '/12' then 1 else 0 end) as Ordem2012,
  sum(case when AUXORDEMSERVICO = '/13' then 1 else 0 end) as Ordem2013
from
  ORDEMSERVICO
group by 
  CDCLIENTE
having
  sum(case when AUXORDEMSERVICO = '/12' then 1 else 0 end) > 0 and
  sum(case when AUXORDEMSERVICO = '/13' then 1 else 0 end) > 0

Anything screams there ... Simulated here:

link