asked on

oracle analytic function - sql syntax

I have a table which records episodes of clients going missing.

The table records (see fig1 of attached):

      - the client ID and name,
      - the episodes unique ID,
      - the episode missing date/ time (episode start date),
      - the date returned date/ time (episode end date),
      - the number of hours the client was missing in the episode (returned date/ time - missing date/time)
      - the number of times the client was missing in 28 days prior to the current episode (including the current episode).

The column I need some help to calculate is shown in fig2 named PREV_MIS_EPI_28Day_over72. This column is a Y/N flag to flag if any of the previous episodes in the last 28 days (including the current) have lasted >=72 hours.

any help is appreciated.
ee-example-20151022.xlsx

Sean Stuber

what are your column data types and what determines the 28 day window?

IS it based on missing? or returned? Does time factor in that or not?

That is, if it is currently 14:20 does a time of 13:10 from 28 days ago count because it was still 28 days by the calendar (ignoring time) or does it not count because it's not strictly 28 days (including time?)

why are your date and time columns separate? you know oracle dates store time with them, right?

awking00

Is fig1. actual data in a table or the result of a query in which you have done some calculations? I suspect the latter and, if so, can you produce the query and the table data that produced those results?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial