Re: how to restrict query results using date calculations

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Wed, 29 Jan 2014 15:16:22 -0800
Message-ID: <CAA2DszyMQ9Qs8aA_Y4zHO33N-DEzDcZLnR7WbwUMj8wQ3sZddg_at_mail.gmail.com>

William
  You might want to read about analytic functions. I modified your SQL little bit to help you start in the direction (and syntax mistakes are expected)..
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
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 Wed, Jan 29, 2014 at 2:59 PM, William Threlfall < William.Threlfall_at_albertahealthservices.ca> wrote:

>  Hi, I am not a newbie when it comes to Oracle SQL, however, I am far
> from an expert, so I am getting stuck on how to do certain things in
> queries.  Help is appreciated!
>
> To get the last transplant date for a patient requires the following
> subquery:
>
>     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"
>
> PD is specified in the FROM clause of the main SELECT statement.
>
> Using CURRENT_DATE as the endpoint, what query code would I use to produce
> a list of certain fields (e.g. PATIENT_DEMO.LAST_NAME, etc.) for patients
> who are 5 years or more post-transplant?
>
> The following code doesn't work; it just produces a mysterious error
> message "not a GROUP_BY expression".
>
> SELECT
>   PD.PAT_ID,
>   PD.LAST_NAME,
>   PD.FIRST_NAME,
>   (
>     SELECT
>       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",
>   (
>     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 -
>   (
>     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
> ORDER BY
>   PD.LAST_NAME;
>
> 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 - 00:16:22 CET