Re: how to restrict query results using date calculations

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Thu, 30 Jan 2014 13:07:22 -0800
Message-ID: <CAA2Dszy_49NCg6_yLmcPc64gs77Y-94A_P=LBRDupFL_fPdLrg_at_mail.gmail.com>

Hello Bill
  It may be readable to enclose the statement in an inline view and then appy the predicate. Here is an example:

/
oriyaj1:sql riyaj$ vi /tmp/a1.lst

select * from (
SELECT

  PD.PAT_ID,
  PD.LAST_NAME,
  PD.FIRST_NAME,
   (
     SELECT /*+ not sure about the logic here. */
       EPI.EXT_PAT_ID
     FROM
       EXT_PAT_IDS EPI
     WHERE
       EPI.PAT_ID            = PD.PAT_ID
     AND EPI.EXT_PAT_ID_TYPE = 0
     AND RowNum              < 2

   ) AS "UAH MRN",
  TO_DATE(MAX(PT.DATE_OF_TRANS) over (partition by PD.PAT_ID order by PD.PAT_ID rows between unbounded preceding and unbounded following), 'yyyy.mm.dd') "Last Tx",

--   (


--     SELECT

--       TO_DATE(MAX(PT.DATE_OF_TRANS), 'yyyy.mm.dd')

--     FROM

--       PAT_TRANSPLANT PT

--     WHERE

--       PT.PAT_ID = PD.PAT_ID

--   ) AS "Last Tx",
(TO_CHAR(((CURRENT_DATE - MAX(PT.DATE_OF_TRANS) over (partition by
PD.PAT_ID) ) / 365.25 + 0.05), '9999D9')) AS "Years Since Last Tx",
--  (TO_CHAR(((CURRENT_DATE - ( SELECT MAX(PT.DATE_OF_TRANS) FROM
PAT_TRANSPLANT PT WHERE PT.PAT_ID = PD.PAT_ID)) / 365.25 + 0.05),
'9999D9')) AS "Years Since Last Tx",
  PRC.CARE_ID                    AS "Post Tx Coord"

FROM
  PATIENT_DEMO PD,
  PAT_REF_CARE PRC,
  PAT_TRANSPLANT PT,
  ORGAN_TYPES OT
WHERE

  PD.PAT_ID       = PT.PAT_ID
AND PD.PAT_ID     = PRC.PAT_ID
AND PT.ORGAN_TYPE = OT.ORGAN_TYPE

AND
  (((CURRENT_DATE -

      (
        SELECT
          MAX(PT.DATE_OF_TRANS)
        FROM
          PAT_TRANSPLANT PT
        WHERE
          PT.PAT_ID = PD.PAT_ID
      )

    ) / 365.25 + 0.05)) >= 5.0
  AND LOWER(OT.ORGAN_LONG_NAME) LIKE 'liver%'   AND PD.PATIENT_STATUS NOT IN (1, 5)
  AND PRC.CARE_ID IN (15538, 20472, 36774, 62594) --GROUP BY
-- PRC.CARE_ID
)
where "Years Since Last Tx" > 5.0
ORDER BY
  PD.LAST_NAME; Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/> , Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert
RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

On Thu, Jan 30, 2014 at 12:35 PM, William Threlfall < William.Threlfall_at_albertahealthservices.ca> wrote:

>  Hi Riyaj,
>
>
>
> Thank You!  You are correct, I don't yet know anything about analytic
> functions, but I'm reading about them now.
>
> I modified the code and now it seems to be calculating Last Tx date and
> Years Since Last Tx correctly.
>
>
>
> However, I still cannot see any way to select out only patients where
> Years Since Last Tx is >= 5.0.
>
> I can't use the MAX function in the WHERE clause, and "Years Since Last
> Tx" is an invalid identifier in the WHERE clause.
>
>
>
> This can't be that difficult to do.  Do you have any suggestions for me?
>
>
>
> Thanks, - Bill.
>
>
>
> ------------------------------
> This message and any attached documents are only for the use of the
> intended recipient(s), are confidential and may contain privileged
> information. Any unauthorized review, use, retransmission, or other
> disclosure is strictly prohibited. If you have received this message in
> error, please notify the sender immediately, and then delete the original
> message. Thank you.
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 30 2014 - 22:07:22 CET