SQL - Doubt in a query

Question

SQL - Doubt in a query

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

1

Thanks to Motta's help I managed to get into a satisfactory query but I ran into another query because I need to list only the smallest record by cod , and count the amount of the menorregistro column by name. >

Could someone help?

Here is the return of the query for example:

Thatis,IneedyoutolistonlytherowswiththeMinorRecordcolumn=558,896,and1321.AndthatitcountstheMinorRegistrobynamegettinglikethis:

Note: I do not need the DT_ATE column and even if I list the smallest record I just want to count how many smaller records each name had, I left the columns just to better illustrate the problem.

Follow the query:

select b.pront as cod, 
       d.nome,
       b.dt_ate,
       min(case when b.dt_ate between '19.12.2015' and '08.01.2016' then b.reg else null end) menorRegistro
            from recadate b
            inner join tbcbopro c on b.crm=c.cod
            inner join tbprofis d on c.id_tbprofis=d.id
              where b.conv between :ConvInicial and :ConvFinal
              and b.unidade between :Unidade_inicial and :Unidade_final
              and b.pront in (111811, 210313, 395415) 
group by d.nome, b.pront, b.reg, b.dt_ate 
having min(case when b.dt_ate between '19.12.2015' and '08.01.2016' then b.reg else null end) is not null

sql firebird

asked by anonymous 28.07.2016 / 17:54

3 answers

Create list sorted by letter with AngularJS Json for Markers android

score 1 · Answer 1

EDIT 2:

SELECT cod, nome, MIN(menorregistro) AS menorregistro
FROM (
    SELECT
      b.pront AS cod,
      d.nome,
      b.dt_ate,
      MIN(IIF(b.dt_ate BETWEEN '2015-12-19' AND '2016-01-08', b.reg, NULL)) AS menorregistro
    FROM recadate b
    INNER JOIN tbcbopro c ON b.crm = c.cod
    INNER JOIN tbprofis d ON c.id_tbprofis = d.id
    WHERE 
      AND b.conv    BETWEEN :ConvInicial     AND :ConvFinal
      AND b.unidade BETWEEN :Unidade_inicial AND :Unidade_final
      AND b.pront   IN (111811, 210313, 395415)
    GROUP BY 1, 2, 3
    HAVING MIN(IIF(b.dt_ate BETWEEN '2015-12-19' AND '2016-01-08', b.reg, NULL)) IS NOT NULL
) GROUP BY 1, 2

score 1 · Answer 2

I used with only to create a table and work with the select you already defined, after that I just performed a group by cod and a count of the smallest records.

with tabela as(
 select b.pront as cod,
        d.nome,
        b.dt_ate,
        min(case when b.dt_ate between '19.12.2015' and '08.01.2016' then b.reg else null end) menorRegistro
             from recadate b
             inner join tbcbopro c on b.crm=c.cod
             inner join tbprofis d on c.id_tbprofis=d.id
               where b.conv between :ConvInicial and :ConvFinal
               and b.unidade between :Unidade_inicial and :Unidade_final
               and b.pront in (111811, 210313, 395415)
 group by d.nome, b.pront, b.reg, b.dt_ate
 having min(case when b.dt_ate between '19.12.2015' and '08.01.2016' then b.reg else null end) is not null
)


 select cod
       ,nome
       ,count(menorRegistro)
   from tabela
 group by cod

score 0 · Answer 3

You can use SubSelect to do this, and then group your data into the inner select using Min () to get the lowest value and then make the Count() in the select out.

As follows.

select pront, nome, count(b.reg) as menorRegistro 
from (
    select b.pront, d.nome, min(b.reg) menorRegistro
        from recadate b
        inner join tbcbopro c on b.crm=c.cod
        inner join tbprofis d on c.id_tbprofis=d.id
            where b.conv between :ConvInicial and :ConvFinal
            and b.unidade between :Unidade_inicial and :Unidade_final
            and b.pront in (111811, 210313, 395415) 
    group by d.nome, b.pront, b.reg, b.dt_ate 
)subconsulta
group by nome, pront