Daily count - PL/SQL

Asked

Viewed 125 times

-2

Good morning! In the example sent we have a number of attendance, the date of arrival and the date of departure of the patient; and the daily count. In the daily count I am considering only the period from 01/07/2017 to 31/07/2017, including the first day. That is, on line 1 I am counting the days from 01/07 until 16/07, which returns me 16 days. So far, ok. The problem is that in line two the patient had a change of sector, staying in this new sector until the day 29/07. That’s where I need your help. As the count of line 1 already considers day 16/07, on line 2 I would need the date of entry to be counted only from day 17/07. The field DT_ENTR_AJUSTE 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 is needed is to verify that the date (hh/mm/ss) of line 1 output is exactly equal to the date (hh/mm/ss) of line 2 input (in the same number of service), the field DT_ENTR_AJUSTE must be filled with +24h. But I’m not getting that result. If you need the query, send later.

resultado da query

oracle pl-sql

  • I don’t understand what you want... You want to check if when the dt_input equals the previous dt_output add + 24hrs?

  • 1

    Hello Andre! That’s right, when the exit date of line 1 is equal to the entry date of line 2, fill in the field DT_ENTR_AJUSTE of line 2 with an extra 24hs. example: the exit date of line 1 = 16/07/2017 10:55:46, which is exactly equal to the entry date of line 2, then the DT_ENTR_AJUSTE of row 2 must be filled with 17/07/2017 10:55:46.

  • I get it... if you can wait, at noon I’ll help you with that. ?

  • Sure, thank you!

  • @Andréfilipe, there is no need for a dump with a simple example of this, this your requirement sounds like something very malicious.

1 answer

0

Analytical function can be used Lead, making it possible to access values of different Rows. Excerpt: 

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 checking the dt_output of a record ahead, considering the order dt_decreasing entry, if the value returned is the same as the current dt_entry, it increments 1 day, otherwise it returns the current value.

example in sql fiddle: http://sqlfiddle.com/#! 4/7503df9/1/0

Browser other questions tagged oracle pl-sql

You are not signed in. Login or sign up in order to post.