SQL - Query Doubt

2

I'm wondering with a tremendous doubt, I have these 3 columns just need to bring the smallest record if the date is between the between. Example: If you enter date 12/23/2015 to 01/01/2016 it is not to bring record because the smaller record is dated 12/18/2015.

Myqueryfollowsbutitiswrong,becauseitisbringingthesmallestrecordevenifthedateisnot12/18/2015.

selectmin(b.reg)Menor_Reg,b.dt_ate,b.prontfromrecadatebinnerjointbcboproconb.crm=c.codinnerjointbprofisdonc.id_tbprofis=d.idinnerjointbcbosuseone.id=c.id_tbcbosusinnerjoinrepacagdfonf.crm=b.crminnerjointbconvengonb.conv=g.codwhereb.dt_atebetween'23.12.2015'and'01.01.2016'andb.pront=116312groupbyb.pront,dt_ate

I wanted you to bring the smallest record only if the date was for example 12/18/2015.

Thank you in advance, any help is welcome ...

    
asked by anonymous 26.07.2016 / 17:07

1 answer

2

But the date you want 12/18/2015 is not between the 23.12.2015 and 01.01.2016 range, it seems to bring the lowest only if the general lower is in the requested range, you want to do it when it is not bringing null ?

Try something like this yet

select min(b.reg) Menor_geral, 
       min(case when b.dt_ate between '23.12.2015' and '01.01.2016' then b.reg else null end) menor_intervalo,
b.dt_ate, b.pront
from recadate b
inner join tbcbopro c on b.crm=c.cod
inner join tbprofis d on c.id_tbprofis=d.id
inner join tbcbosus e on e.id=c.id_tbcbosus
inner join repacagd f on f.crm=b.crm
inner join tbconven g on b.conv=g.cod
where b.pront=116312
group by b.pront, dt_ate
    
26.07.2016 / 17:40