Compare data sql

Question

I need to compare date in a certain time interval. I need to pick up the field DAT_REPOT_REALZ and check the dates between TODAY and seven days ago. I also need to count the number of V (visitor) and the number of L (leader) and M (participant), at query that I did didn’t work out because I’m not very familiar with sql.

It is worth noting that I am using the MYSQL.

I tried to do so

select case FLG_IDENT_PESSO when 'V' then count(FLG_IDENT_PESSO) when 'M' then count(FLG_IDENT_PESSO) end from tbl_PRESENCA WHERE FLG_IDENT_PRESE = 'S' and DAT_REPOT_REALZ <= now()-7

The structure of my table:

COD_IDENT_REUNI bigint(20) UN PK 
COD_IDENT_PESSO bigint(20) UN PK 
FLG_IDENT_PRESE char(1) //PODE SER S (presente) ou N (nao presente)
FLG_IDENT_PESSO char(1) // PODE SER V (visitantes) ou L (lider) ou ainda M (participante)
DAT_REPOT_REALZ datetime // É O DIA EM QUE FOI FEITO O REPORTE

Answer 1

It is possible to do so:

select 
    count(COD_IDENT_PESSO); 
FROM 
    tbl_PRESENCA 
WHERE 
    DAT_REPOT_REALZ BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() AND 
    FLG_IDENT_PRESE = 'S';

SQL above will return the total number of people who were present at the meeting. To count separately, just use another condition in WHERE

select 
    count(FLG_IDENT_PESSO); 
FROM 
    tbl_PRESENCA 
WHERE 
    DAT_REPOT_REALZ BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() AND 
    FLG_IDENT_PRESE = 'S' AND
    FLG_IDENT_PESSO = '?';

Where '?' would be what you want to look for in particular, V, M or L

select 
    sum(case FLG_IDENT_PESSO when 'V' then 1 else 0 end) as 'Visitantes',
    sum(case FLG_IDENT_PESSO when 'L' then 1 else 0 end) as 'Lideres',
    sum(case FLG_IDENT_PESSO when 'M' then 1 else 0 end) as 'Participantes',
FROM 
    tbl_PRESENCA 
WHERE 
    DAT_REPOT_REALZ BETWEEN DATE_ADD(CURDATE(), INTERVAL -7 DAY) AND CURDATE() AND 
    FLG_IDENT_PRESE = 'S';

Or all in one select, dividing as it was quoted in the other reply.

Answer 2

You can use BETWEEN and the function DATE_SUB

Your Where looks like this : where DAT_REPOT_REALZ between DATE_SUB(now(), interval 7 day) and now()

Follows a fiddle for example .

Regarding Count, you can put a sum in a case

example :

SUM(case FLG_IDENT_PESSO when 'V' then 1 else 0 end) as Visitantes

full query :

    select sum(case FLG_IDENT_PESSO when 'V' then 1 else 0 end) as visitantes , 
    sum(case FLG_IDENT_PESSO when 'M' then 1 when 'L' then 1 else 0 end) as participantes 
  from tbl_PRESENCA where DAT_REPOT_REALZ between DATE_SUB(now(), interval 7 day) and now()