Daily Count - PL / SQL

Navigation
-2

Good morning! In the example we have a service number, the date of entry and the date of leaving the patient; and the daily count. In the daily count I am only considering the period from 07/01/2017 to 07/31/2017, counting even the first day. That is, in line 1 I am counting the days from 07/07 to 16/07, which returns me 16 days. Until then, okay. The problem is that in line two the patient had a change of sector, staying in this new sector until July 29. That's where I need some help. As the count on line 1 already considers the day 16/07, on line 2 I would need the date of entry to be counted only from day 17/07. The DT_ENTR_AJUSTE field was created in the query already to try to solve this problem, but it is still bringing the day 16/07, when it would need to bring the day 17/07. What I need is to verify that the output date (hh / mm / ss) of line 1 is exactly equal to the input date (hh / mm / ss) of line 2 (same attendance number), the field DT_ENTR_AJUSTE should be filled with + 24h. But I'm not getting this result. If you need the query, I'll send it later.

    
asked by anonymous 07.11.2018 / 11:47

1 answer

0

You can use the Lead analytic function, allowing you to access values from different rows. Example: 

select e.dt_entrada,
       decode(lead (dt_saida,1)  over ( ORDER BY dt_entrada desc), e.dt_entrada, dt_ajuste+1, dt_ajuste) as dt_ajuste,
       e.dt_saida
    from exemplo e
 order by dt_entrada

In the example this is checking the output of a forward record, considering the descending dt_entry order, if the returned value is the same as the current dt_entry, it increments by 1 day, otherwise returns the current value.

example in sql fiddle: link

    
07.11.2018 / 17:59
How to disable textarea when select is set to false How to return data from a class within an option