How to mount this query

Question

I need to return new clients for a certain period, but in the bank I can only work with "anocobranca, mescobranca", to count as a new client I check everyone who hired a service within the period, only that the client can hire at different periods and I can’t count as a new one if he already hired in an earlier period.

example:

anocobranca = 2021
mescobranca = 04

let’s say the result is just 1 the id of this client is 9999 if that same client hired a service in 2017/05 it cannot be added as new in the period 2021/04.

Table:

within a foreach I assemble the query and return the quantity

$consulta = "SELECT distinct cs.idclienteunidade from clienteunidadeservico cs
    where cs.anocobranca = $ano and cs.mescobranca = $mes";

this works for the new ones of this period but in some of these returns may have one that is old and not new.

Answer 1

Assuming your database is Postgresql, I propose the SQL query below:

SELECT distinct cs.idclienteunidade 
  from clienteunidadeservico cs
    where cs.anocobranca = 2021 and cs.mescobranca = 4
    and not exists (select * 
                      from clienteunidadeservico cs2 
                     where cs2.idclienteunidade = cs.idclienteunidade
                       and cast( (cast(cs2.anocobranca as varchar) || '-' || lpad(cast(cs2.mescobranca as varchar),2,'0') || '-' || '01') as date) <
                           cast( (cast(cs.anocobranca as varchar) || '-' || lpad(cast(cs.mescobranca as varchar),2,'0') || '-' || '01') as date)
                   )

Some considerations:

• This example was done considering a Postgresql database.
• Logic Used: Select distinct clients from a specific period (year/month) other than exists a record with a period (year/month) prior to the period initially specified, such as anocobranca = 2021 and mescobranca = 04
• I preferred to generate a date from the fields anocobranca and mescobranca using the functions cast and lpad as follows:

Observe:

cast( (cast(cs.anocobranca as varchar) || '-' || lpad(cast(cs.mescobranca as varchar),2,'0') || '-' || '01') as date)

I observed that your field mescobranca is whole, so I used the function lpad to fill with a zero left if necessary, since I want to generate the dates according to your example 2017-05-01 and 2021-04-01 to make the comparison between the two dates.

Considering the sample data below:

idclienteunidadeservico	anocobrancanca	same	clientele
1	2021	4	251
2	2021	4	548
3	2020	5	251
4	2021	5	548

When executing the proposed consultation as a solution, the result was:

clientele
548

As demonstrated, I believe that the proposed consultation will solve the problem.

Observing: Postgresql would even do the cast correctly without lpad, but I preferred to use to illustrate how to do if the database system requires the two characters for the month. The query select cast('2021-4-15' as date) results 2021-04-15.

Answer 2

You should have a better performance solution (maybe with JOIN + IS NULL), but the easiest is to use a NOT IN:

1. Select what you want:

   SELECT ... WHERE cs.anocobranca = $ano and cs.mescobranca = $mes

2. Rules out what you don’t want:

   NOT IN (... WHERE cs.anocobranca != $ano and cs.mescobranca != $mes)

In the end it would look like:

SELECT DISTINCT
   cs.idclienteunidade 
from 
  clienteunidadeservico cs 
where 
      cs.anocobranca = $ano 
  and cs.mescobranca = $mes 
  and idclienteunidade NOT IN (
    select 
      idclienteunidade 
    from 
      clienteunidadeservico 
    where 
      clienteunidadeservico.anocobranca != $ano 
      and cs.mescobranca != $mes
  )