Re: Reports Developer 2000 question

Hi Cynthia,
No, you will not have to use PL/SQL to solve this problem. It's fairly simple, and all is solvable in SQL (the SELECT statement). Ever used a DECODE? Well, if not now is the time :-)) What you want to do is to take the Physician type and sort it into FT, PT, PRN. For instance ... like the following:

SELECT DECODE(Physician_type,'FT',1,0) FT_TYPE,

               DECODE(Physician_type,'PT',1,0) PT_TYPE,
               DECODE((Physician_type,'PRN',1,0) PRN_TYPE,

These fields do NOT go to the layout ... and all you do is to summarize them and present the sums at the right level (in the group surrounding the detail/repeating group). If you want a grand total, just make the summaries reset on REPORT level, and put the results in the report-body or the top repeating frame.

Your second problem about only selecting the active personal is a standard SELECT issue too ... which does lead me to believe that you're rather new to Oracle or relational databases. What you want to do is to join (actually use a semi-join) your physician table to the phy_privileges, using the identifcation of the physician to get to the phy_privileges tables. Then restrict the phy_privileges rows using the ACTIVE condition and the sysdate as you suggest. But you might be in for a surprise now, because it looks like your phy_privileges is historically bound, and that yeilds another, a little more complex solution to get the PRESENT data.

What you DON'T want is ANY row where the FROM date is less than the present day ... take this example:

PHY_ID FROM-DATE ACTIVE

100          10-JAN-98         YES
100          23-MAR-98       NO
100          10-JUN-98         YES
100          01-AUG-98        NO  (planned holiday)

Now, if you select ANY row on physician 100, where the from-date is larger than today, you get nothing. Wups! That's not what we want! Running means get the LAST current row (max date) where the FROM date does not exceed the current date. To do that, you write:

SELECT * from PHY_PRIVILEGES a
where phy_id = 100

   and from_date = ( select max(from_date) from phy_priviledges b

                               where b.phy_id = a.phy_id
                                   and b.from_date <= trunc(sysdate)
                              )

This will give you the current row of PHY_PRIVILEDGES. Add the ACTIVE condition to the outer table (not the subquery) and you'll only get the active physicians. If you only select the PHY_ID from the table, use this query as an IN clause to your main query in the report.

About restricting to a company, team or whatever is a standard parameter solution in Oracle Reports. If you're all new to Reports try to consult the manual on how to create parameters.

Hope this helps ....

• Peter H. Larsen Oracle Consultant

Cynthia Langevin wrote:

> Hello,
>
> I have a question regarding report development. I hope to receive answers
> as it is most important to me.
>
> The question regards the following report:
>
> Header: Physician Status
> Run Date and Time:
> User Id:
> Page Number of Total Pages
> Company, Facility or Team Name
>
> Body:
>
> Physician Name/Degree Status
> Joe Doe, MD FT (full-time)
> John Smith, MD PT (part-time)
> Cynthia Lang, MD PRN (part-time as needed)
>
> ordered by physician id
>
> Total Number of Physicians: xxxx
> Total FT: xx
> Total PT: xx
> Total PRN: xx
>
> The question is how do you calculate the summaries for the individual
> status'? There is a function created that creates the total for Phy_id, but
> I am not sure how to do the other individual total counts for the 3
> different status.
>
> The other question is regarding the physician privileges have to be active
> for the date that the report is run on. There is a phy_privileges table
> that has privilege_status in it and if it is not active, they are not
> supposed to appear on the report. The report can be run for individual
> company, team or facility or for all companies, facilities or teams. Would
> you check the systemdate against the start_date for the physician privilege
> and how would you do that?
>
> Thanks so much for your help in trying to assist me. You can respond
> directly to me at clangevin_at_tcipro.com or I will check the newsgroup daily.
>
> Cynthia
Received on Thu Jun 11 1998 - 00:00:00 CEST