Home » SQL & PL/SQL » SQL & PL/SQL » exluding records based on date
exluding records based on date [message #186808] Wed, 09 August 2006 12:23 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I have the following query that pulls information based on the greatest start date entered. However, due to a data entry error I have three records showing up that I would like to exclude. Here is the query:

SELECT DISTINCT 
   res1.Patient_Name || ' - ' ||
   res1.Patient_ID Patient_Name,
   res1.Room_Bed,
   res1.admit_dt,
   res1.Age,
   res1.sex,
   res1.provider_type,
   ddt.tochar(res1.start_ddt,'mm/dd/yyyy'),
   ddt.tochar(res1.end_ddt)
   FROM
   (SELECT DISTINCT
      patient1.last_name ||', '|| patient1.first_name ||' '||   patient1.middle_name Patient_Name,
      patient1.patient_id AS Patient_ID,
      patient1.room_id || ' - ' || patient1.bed_id Room_Bed,
      assigned_staff1.staff_name || ' - ' || provider_types1.provider_type as Staff_Name,
      patient1.room_id,
      patient1.admit_dt,
      decode(trunc((SYSDATE - patient1.BIRTH_DT)/365.25), 0,
         trunc(months_between(SYSDATE,patient1.BIRTH_DT))||' Months', 1,
         trunc(months_between(SYSDATE,patient1.BIRTH_DT))||' Months', NULL,'Not Available', 
         trunc((SYSDATE - patient1.BIRTH_DT)/365.25)||' Years') Age,
      CASE patient1.sex
         WHEN 'M' THEN 'Male'
         WHEN 'F' THEN 'Female'
      ELSE 'Other'
      END Sex,
      provider_types1.provider_type,
      staff_assign1.start_ddt,
      staff_assign1.end_ddt,
      MAX(ccdba.staff_assign1.start_ddt)
      OVER 
         (PARTITION BY 
             patient1.last_name|| ',' || patient1.first_name|| ' ' || patient1.middle_name,
             patient1.patient_id) max_start_ddt
      FROM
         patient patient1,
         provider_types provider_types1,
         ccdata.staff_local  assigned_staff1,
         ccdba.staff_assign staff_assign1,
         staff_facility staff_facility1
      WHERE
                  patient1.pat_seq = staff_assign1.pat_seq
         AND staff_assign1.staff_seq = assigned_staff1.staff_seq
         AND staff_facility1.provider_type = provider_types1.provider_type
         AND assigned_staff1.staff_seq = staff_facility1.staff_seq
         AND patient1.facility_id  IN ('P')
         AND patient1.dept_id IN ('5RP')
         and ddt.tochar(staff_assign1.start_ddt) <= to_char(sysdate,'mm/dd/yyyy hh24:mi')
         and provider_types1.provider_type IN ('RN','LPN','ELPN','SA')
         AND trunc(ddt.todate(ddt.fromdate(patient1.discharge_dt))) IS NULL )res1
WHERE
       res1.start_ddt = res1.max_start_ddt
   AND (ddt.tochar(res1.end_ddt,'mm/dd/yyyy HH24:MI') <= to_char(sysdate,'mm/dd/yyyy HH24:MI')
   and ddt.tochar(res1.start_ddt,'mm/dd/yyyy') <= to_char(sysdate,'mm/dd/yyyy'))
order by
   room_bed



Here is sample output and lines 1, 2, and 7 should be excluded based on the start_ddt being greater than the sysdate.



Room		admit_dt	start_ddt	sysdate	

06 – 01		08-AUG-06	04/23/2021	08/09/06 08:30
16 – 01		04-AUG-06	04/23/2021	08/09/06 08:30
26 – 01		06-AUG-06	08/08/2006	08/09/06 07:15
28 – 01		19-JUL-06	08/08/2006	08/09/06 11:00
29 – 01		06-AUG-06	08/08/2006	08/09/06 11:00
31 – 01		04-AUG-06	08/08/2006	08/09/06 11:00
40 – 01		04-MAY-06	04/23/2021	08/09/06 08:30






I thought the last line in the query would eliminate these three lines. Any hints?

Thanks,
Stan

[Updated on: Wed, 09 August 2006 12:32]

Report message to a moderator

Re: exluding records based on date [message #186813 is a reply to message #186808] Wed, 09 August 2006 12:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Classic example of why you should always compare DATEs to DATES.

If you rewrite the last line of the query as
and res1.start_ddt <= sysdate

it should work perfectly.

By converting them to mm/dd/yyyy dates, you screwed up the ordering:

'01/01/2999' will always come before '31/12/1900' in a string comparison.

Never ever do what I'm going to show you now.
Ever.
Just compare dates to dates.
Ok?

You could rewrite it as
and ddt.tochar(res1.start_ddt,'yyyymmdd') <= to_char(sysdate,'yyyymmdd'))

and have it work, as that way, the most significant parts of the date are always to the left.
But don't do that.
Re: exluding records based on date [message #186815 is a reply to message #186808] Wed, 09 August 2006 12:49 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I had already tried that and got the following error:

inconsistant datatypes: expected NUMBER got DATE

Why do you say not to try your example? It worked!

Thanks,
Stan

[Updated on: Wed, 09 August 2006 13:08]

Report message to a moderator

Re: exluding records based on date [message #186818 is a reply to message #186815] Wed, 09 August 2006 13:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Excuse me while I put on my 'Really Bad Schema Design' gloves.

Would I be right in guessing that res1.start_ddt isn't a DATE at all?
Because if it was a date, then the comparison to sysdate would work just fine.

If I went further and suggested that it is a NUMBER datatype, and that at some point the Coding Pixies had slipped some absinth into everyones coffee, and when your eyesight returned you'd coded a package to convert dates stored as numbers into character strings, would I be far from the truth?

What I suggested will work (of course), but it is more cumbersome, less easy to read, uses more cpu time, and resticts the use of indexes more than just comparing a date column to sysdate.

The DATE datatype is there for a reason.
Re: exluding records based on date [message #186820 is a reply to message #186808] Wed, 09 August 2006 13:09 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I much rather prefer to use
and res1.start_ddt <= sysdate


but get the aforementioned error.
Re: exluding records based on date [message #186821 is a reply to message #186820] Wed, 09 August 2006 13:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So, what datatype is res1.start_ddt?
Is it actually a date?
Re: exluding records based on date [message #186822 is a reply to message #186808] Wed, 09 August 2006 13:30 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
res1.start_ddt datatype is number.

I though that converting sysdate to a number would work

to_number(sysdate) but I get an error with this.

[Updated on: Wed, 09 August 2006 13:34]

Report message to a moderator

Re: exluding records based on date [message #186840 is a reply to message #186822] Wed, 09 August 2006 15:56 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you should consider a Julian date, which is a number:
SQL> SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'j')) FROM dual;

TO_NUMBER(TO_CHAR(SYSDATE,'J'))
-------------------------------
                        2453957

SQL>


[EDIT] I didn't read previous posts carefully; if NUMBER datatype column has date in it (for example, 20060809), convert it to date using
SQL> select to_date('20060809', 'yyyymmdd') from dual;

TO_DATE(
--------
09.08.06

SQL>


[Updated on: Wed, 09 August 2006 15:59]

Report message to a moderator

Previous Topic: repeatedly calc avg of numbers between date limits, then subsequently on next same limits, repeat
Next Topic: SQL help needed
Goto Forum:
  


Current Time: Sun Dec 11 04:08:48 CST 2016

Total time taken to generate the page: 0.04255 seconds