Re: Dev. 2000 Reports 3.0 and SQL

From: Rohrbacher, Boris <rohbo_at_sbox.tu-graz.ac.at>
Date: Mon, 29 Jun 1998 21:52:00 +0200
Message-ID: <3597F060.F2AD18A2@sbox.tu-graz.ac.at>

HI cynthial

> I am looking for suggestions how to write the SQL query from a view
> that contains physician's privileges to work at a facility, company or
> team and they have a start_date and end_date for these privileges.

So I assume your view gives you something like this

select physi_nr , priv_site_type, start_date, end_date from physi_privi_siti

where physi_nr is foreign key for physician table

           priv_site_type is 'FACILITY' , 'COMPANY' , 'TEAM' or the foreign key to a lookup table

           start_date, end_date as you described.

> So, in the report, there is a parameter form that the user can enter
> a date range to run a report for. If they select say 01/01/1998 to
> 01/02/1998, then how do I write the SQL statement that checks to see
> if the start_date falls between this time period and their end_date
> might be NULL?
>

Parameter :DATE_WINDOW_START , :DATE_WINDOW_END-- All physis + sites where start_date >= to beginning of date window and <= end of date window

--                                   and end_date is either null  or also

within the date window

select physi_nr , priv_site_type, start_date, end_date from physi_privi_siti
where start_date between :DATE_WINDOW_START and :DATE_WINDOW_END

   and ( end_date is null or ( end_date between :DATE_WINDOW_START and :DATE_WINDOW_END ) )

Performance : If you have an index on start_date query should be fast. should do a RANGESCAN on start_date

> I do not want any physicians to print that are not privileged to work
> at a facility with a start_time earlier than what the user enters as a
> date range, or if they leave the date range a generic start_date of the
> beginning of time, I want to get them all that are privileged.

I couldn't figure out what's the beginning of time ( reply-email from Stephan Hawking didn't give satisfying answer, at least nothing which I could possibly include in a sql statement )But if christs birthday is small enough :
( hm how comes that we celebrate his birthday on 25th of december but start to count the year with the 1st of january ? - anyway )

select physi_nr , priv_site_type, start_date, end_date from physi_privi_siti
where start_date between nvl( :DATE_WINDOW_START, to_date('01-01-0001','DD-MM-YYYY')

                                      and nvl( :DATE_WINDOW_END ,'WHAT IF

INFINTE END_DATE' )
   and ( end_date is null or ( end_date between nvl( :DATE_WINDOW_START , 'AS ABOVE' ) and nvl( :DATE_WINDOW_END, 'AS_ABOVE' ) )

I hope I got you right. HTH

Regards
 Robo Received on Mon Jun 29 1998 - 14:52:00 CDT