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
tonMachine100Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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?
awking00Information Technology SpecialistCommented:
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?
sdstuberCommented:
also, why in Fig2 are these rows "N"

1045407      Paul      A5628652      9/7/2015      22:45      9/8/2015      0:05      1      3      N
1045407      Paul      A5679233      9/14/2015      12:30      9/15/2015      12:40      24      4      N

Previous episodes for that client had exceeded 72 hours.



if we can assume those two hours are wrong then try this...



  SELECT e.*,
         CASE
             WHEN MAX(
                      missing_duration_hours
                  )
                  OVER(
                      PARTITION BY client_id
                      ORDER BY b_date_missing
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                  ) >= 72
             THEN
                 'Y'
             ELSE
                 'N'
         END
             prev_mis_epi_28day_over72
    FROM tbl_episodes e


or, if you aren't already pre-filtering your 28 day window then try something like this


  SELECT e.*,
         CASE
             WHEN MAX(
                      missing_duration_hours
                  )
                  OVER(
                      PARTITION BY client_id
                      ORDER BY b_date_missing
                      RANGE BETWEEN INTERVAL '28' DAY PRECEDING AND CURRENT ROW
                  ) >= 72
             THEN
                 'Y'
             ELSE
                 'N'
         END
             prev_mis_epi_28day_over72
    FROM tbl_episodes e


note, I'm using b_date_missing as the driver.  You may want to change it to be the return date instead

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.