Compare data sql

Navigation
1

I need to compare date at a certain time interval. I need to get the DAT_REPOT_REALZ field 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) in query I did not work very well because I am not very familiar with sql .

Note that I'm using 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
    
asked by anonymous 12.02.2016 / 11:40

2 answers

1

You can use BETWEEN and the DATE_SUB

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

A sample fiddle .

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

complete 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()
    
12.02.2016 / 12:11
2

You can do this: 

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';

The above SQL will return the total number of people who attended the meeting. To count separated, 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 specific, 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 everything in a select, dividing as quoted in the other answer.

    
12.02.2016 / 12:06
AES JSF Encryption Encrypt SQLite3