Re: how to restrict query results using date calculations
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: William Threlfall <William.Threlfall@xxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 30 Jan 2014 13:07:22 -0800
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@xxxxxxxxxxxxxxxxxxxxxxxx> 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.
>
Other related posts:
