how to restrict query results using date calculations

From: William Threlfall <William.Threlfall_at_albertahealthservices.ca>
Date: Wed, 29 Jan 2014 15:59:14 -0700
Message-ID: <36800EC4761690448F1B444A1AEF44BB0BE30F55_at_EXMBXC5.crha.bewell.ca>

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 Wed Jan 29 2014 - 23:59:14 CET