Date BETWEEN clause being ingored in complex query with JOINS
Hello,
I am attempting to add two date paramaters to an existing query using a "between" clause like this:
The problem is that this part seems to get ignored (doesn't change the results of the query) when added.
I do not receive any errors and I have tried various ways of referencing the ADMINISTERED_TIME field with not avail.
The following query (for testing) works as expected:
Here is the entire query where the date between clause has no effect:
I am attempting to add two date paramaters to an existing query using a "between" clause like this:
AND TIMES.ADMINISTERED_TIME between '2017-01-01' and '2018-01-01'The problem is that this part seems to get ignored (doesn't change the results of the query) when added.
I do not receive any errors and I have tried various ways of referencing the ADMINISTERED_TIME field with not avail.
The following query (for testing) works as expected:
SELECT * FROM ORDER_MED_ADMIN_TIMES WHERE ADMINISTERED_TIME BETWEEN '2017-01-01' AND '2018-01-01'Here is the entire query where the date between clause has no effect:
SELECT
DISTINCT
ORD.CLIENTID ||' '|| TRIM(CLTLST)||', '||TRIM(CLTFST) AS CLIENT
, Case when varchar_format (stopdatetime, 'YYYY-MM-DD HH24:MI') >= varchar_format (I.CHANGESTAMP, 'YYYY-MM-DD HH24:MI') then 'discharged' else ORD.STATUS end as STATUS
,UPPER(BRAND_NAME) AS TEST
,TRIM(BRAND_NAME)||' ('||TRIM(GENERIC_NAME)||') '||TRIM(STRENGTH) AS MEDICATION
,TRIM(SIG_ACTION)||' '|| TRIM(SIG_DOSE)||' '|| TRIM(SIG_DOSE_UNIT)||' '|| TRIM(SIG_ROUTE)||' '|| TRIM(SIG_DOSE_TIMING)||' '|| TRIM(SIG_DOSE_OTHER)||' '|| TRIM(SIG_DOSE_OTHER) AS SIG
, DATE(SCHEDULEDSTARTDT) AS START_DATE
, DATE(SCHEDULEDENDDT) AS END_DATE
, DATE(I.DISCHARGEDATE) as INPATIENT_DISCHARGE
,TRIM(INSTRUCTIONS) AS INSTRUCTION
,TRIM(STAFF.SMMLNM) ||', '|| TRIM(STAFF.SMMFNM) AS ORDERING_STAFF
,TRIM(STAFF2.SMMLNM) ||', '|| TRIM(STAFF2.SMMFNM) AS AUTHORIZING_STAFF
,TRIM(STAFF3.SMMLNM) ||', '|| TRIM(STAFF3.SMMFNM) as MODIFYING_STAFF
FROM ORDERS ORD
LEFT OUTER JOIN CLTMSTPF CLT ON ORD.CLIENTID = CLT.CLTCAS
LEFT OUTER JOIN ORDER_MED_ADMIN_TIMES TIMES ON ORD.UID = TIMES.ORDER_ID
LEFT OUTER JOIN EMAR_ACTIONS ON EMAR_ACTIONS.UID = TIMES.EMAR_ACTION
LEFT OUTER JOIN RCOPIA_MEDICATIONS MED ON MED.RCOPIA_ID = ORD.RCOPIA_ID
LEFT OUTER JOIN RCOPIA_DRUGS DRUG ON DRUG.RCOPIA_DRUG_ID = MED.RCOPIA_DRUG_ID
LEFT OUTER JOIN STAFFMSTR STAFF ON STAFF.SMMNUM = ORD.ORDERING_STAFF
LEFT OUTER JOIN STAFFMSTR STAFF2 ON STAFF2.SMMNUM = CREATED_USER
LEFT OUTER JOIN STAFFMSTR STAFF3 ON STAFF3.SMMNUM = MODIFY_USER
LEFT OUTER JOIN DOCTSK DOC ON DOC.CLIENT_ID = CLT.CLTCAS AND UPPER(DOC.FORM_ID) = 'SHIFT NOTES' AND UPPER(DOC.STATUS) = 'NEW' --AND DOC.EVETRN_ID <> 0
JOIN INPATIENTEPISODES I on I.UID = (Select MAX (UID) from INPATIENTEPISODES where CLIENTID = ORD.CLIENTID and DISCHARGEDATE is not null)
WHERE ORD.ORDERTYPEID = 3
AND TIMES.ADMINISTERED_TIME between '2017-01-01' and '2018-01-01'
AND BRAND_NAME LIKE '%nitrofurantoin'
OR BRAND_NAME LIKE '%cephalexin'
OR BRAND_NAME LIKE '%amoxicillin'
OR BRAND_NAME LIKE '%clindamycin'
OR BRAND_NAME LIKE '%mupirocin'
OR BRAND_NAME LIKE '%sulfamethoxazole'
OR BRAND_NAME LIKE '%amox'
OR BRAND_NAME LIKE '%azithromycin'
OR BRAND_NAME LIKE '%valacyclovir'
OR BRAND_NAME LIKE '%cefuroxime'
OR BRAND_NAME LIKE '%minocycline'
OR BRAND_NAME LIKE '%fluconazole'
OR BRAND_NAME LIKE '%levofloxacin'
ORDER BY UPPER(BRAND_NAME) ASCASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just a note, no more.
While it seems like "between" is useful for date ranges, in fact it can be plain wrong. In SQL "between" is just an abbreviation for the following:
AND DATE(ORD.SCHEDULEDSTARTDT)
The problem is that there are 2 equal signs! and what you will get is every for the year 2017 PLUS ALL OF '2018-01-01' as well
A much more accurate way of defining you date range is this:
AND ORD.SCHEDULEDSTARTDT >= '2017-01-01' and ORD.SCHEDULEDSTARTDT < '2018-01-01'
Note that you also do not need DATE() either, just leave the data untouched by that function, it will NOT help.
If (for some strange reason) you really do want one year and one day of data, then all you need to do is move the later date to to the next day:
AND ORD.SCHEDULEDSTARTDT >= '2017-01-01' and ORD.SCHEDULEDSTARTDT < '2018-01-02'
Please don't use between for date ranges, it has the potential of getting you into trouble. Way better to use the direct >= and < operators. For more on this topic please see: "Beware of Between"
While it seems like "between" is useful for date ranges, in fact it can be plain wrong. In SQL "between" is just an abbreviation for the following:
AND DATE(ORD.SCHEDULEDSTARTDT)
The problem is that there are 2 equal signs! and what you will get is every for the year 2017 PLUS ALL OF '2018-01-01' as well
A much more accurate way of defining you date range is this:
AND ORD.SCHEDULEDSTARTDT >= '2017-01-01' and ORD.SCHEDULEDSTARTDT < '2018-01-01'
Note that you also do not need DATE() either, just leave the data untouched by that function, it will NOT help.
If (for some strange reason) you really do want one year and one day of data, then all you need to do is move the later date to to the next day:
AND ORD.SCHEDULEDSTARTDT >= '2017-01-01' and ORD.SCHEDULEDSTARTDT < '2018-01-02'
Please don't use between for date ranges, it has the potential of getting you into trouble. Way better to use the direct >= and < operators. For more on this topic please see: "Beware of Between"
the best practice with date and time ranges is to avoid BETWEEN and to always use the form:Itzik Ben-Gan
WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
ASKER