• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

Subqueries in Crystal

Hello,

I have two tables that have data:

ROOM table - contains data of all the possible rooms
ROOM_PERSON table - contains data of all room assignments with person's name and dates for the assignment

Suppose I have the following data:

ROOM
---------------------
B102
B103
B104
C103
C104

ROOM_PERSON
---------------------------------------------------
Joe Smith    B102    Jan 1, 05          Jun 1, 05
Chris Jones  B102    Jun 2, 05         Dec 1, 05
Tom Smith  B103    Jun 2, 05         Dec 1, 05
Jon Kim       C104    Jun 2, 05        Dec 1, 05


I want to create a report that shows the people in a given room as of a date, along with any available rooms.  For example, if I ran the report with a date of Jul 1, 05, the report should return

Chris Jones  B102     Jun 2, 05   Dec 1, 05
Tom Smith   B103    Jun 2, 05    Dec 1, 05
Jon Kim       C104    Jun 2, 05    Dec 1, 05
<null>        B104    <null>        <null>
<null>        C103    <null>        <null>

This means I have to pull all records from ROOM_PERSON as of a date, and then compare that listing with the ROOM table and display any additional rooms that are in ROOM that are not in the listing from ROOM_PERSON.  I know how to do this in SQL using union queries and such, but I'm not quite sure how to do it in Crystal.  Any ideas or help would be appreciated!  

Thanks!
 

0
ccorrente
Asked:
ccorrente
  • 4
  • 4
1 Solution
 
mlmccCommented:
Which version of Crystal?

Can you build the query in the database (if so the report will run much faster)

Select Room.RoomNo, RoomPerson.*
FROM  Room LEFT OUTER JOIN RoomPerson
ON Room.RoomNo = RoomPerson.RoomNo

Create a parameter of type DATE
SelectedDate

Then use the Select Expert to add the filter criteria

{?SelectedDate} in {RoomPerson.StartDate} to {RoomPerson.EndDate}

mlmcc
0
 
ccorrenteAuthor Commented:
I am using crystal 8.5.  So are you saying that I do not use the report wizard, but just create the SQL myself in Crystal (I don't have the abilty to create the SQL in the database).  How do I create thw query in Crystal without using the Report Expert.

Also, I've had problems where if I modify the SQL statement manually instead of using the Report Wizard, it ignores my select critieria.  Will that be the case here as well?
0
 
ccorrenteAuthor Commented:
Will that query give me what I want?  Once I add the selected date parameter, won't it will then filter out any rooms that do not have a move in date (which means they are currently not in use)

For example, if I have this data:

ROOM
---------------------
B102
B103
B104
C103
C104

ROOM_PERSON
---------------------------------------------------
Joe Smith    B102    Jan 1, 05          Jun 1, 05
Chris Jones  B102    Jun 2, 05         Dec 1, 05
Tom Smith  B103    Jun 2, 05         Dec 1, 05
Jon Kim       C104    Jun 2, 05        Dec 1, 05


I think the query you gave me will return this:

Joe Smith    B102    Jan 1, 05          Jun 1, 05
Chris Jones  B102    Jun 2, 05         Dec 1, 05
Tom Smith  B103    Jun 2, 05         Dec 1, 05
Jon Kim       C104    Jun 2, 05        Dec 1, 05
<null>        C103    <null>            <null>

If I then add the date parameter of 7/1/2005, it will return

Chris Jones  B102    Jun 2, 05         Dec 1, 05
Tom Smith  B103    Jun 2, 05         Dec 1, 05
Jon Kim       C104    Jun 2, 05        Dec 1, 05

and room C103 will drop off the list because it does not have a date, right?  Furthermore, if C103 had an assignment but it was for 2004, that assignment would not show up on the list, nor would it show me that it is available.  For example, if I had this data:


ROOM
---------------------
B102
B103
B104
C103
C104

ROOM_PERSON
---------------------------------------------------
Joe Smith    B102    Jan 1, 05          Jun 1, 05
Chris Jones  B102    Jun 2, 05         Dec 1, 05
Tom Smith  B103    Jun 2, 05         Dec 1, 05
Jon Kim       C104    Jun 2, 05        Dec 1, 05
Tim Tom     C103    Jun 2, 04        Dec 1, 04

Running the query would give me exacty what's in Room_Person.  Then, when I applied the date filter, C103 would drop off the list but I would want to show that it was available.  That's why I think I need to use subqueries:  first run a query from Room_Person to get the assignments as of a date, then compare that list with what's in Room to pull any additional rooms that were not in the first query.  But I don't know how to do that in Crystal.  Maybe I am missing something?? :)
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
mlmccCommented:
Using a LEFT OUTER JOIN will ensure all rooms in the ROOM table are included in the query.  If the room is empty then the columns from the other table will have NULL.

To get the empty rooms included after the selection criteria try

(isNull({?SelectedDate})) OR ({?SelectedDate} in {RoomPerson.StartDate} to {RoomPerson.EndDate})

mlmcc
0
 
ccorrenteAuthor Commented:
Thanks again for the info.  Now, I tried the left outer join in SQL+ (not in Crystal yet because I am working with someone who has CR 8.5 installed whereas I do not) but it does not give me what I want.  The left outer join just gives me NULLS in cases where the room was NEVER in the room_person table, but I need to factor in a date in order for this query to give me what I want.  If I apply the filter after that query, it will give me rooms that were booked as of a date, but since the initial query did not have the correct listing of NULLS, my empty rooms will not appear properly (it will only be rooms that were NEVER in the room_person table).  

I'm really having a hard time seeing how I can do this without using a UNION query to union rooms as of a date with all rooms missing from that initial list.  For example, I tried this and it gave me what I wanted:

select a.pk_bed_space,  b.ck_move_in_date, b.room_person_move_out_date, c.ix_last_name,  
c.ix_first_name
from rooms a left outer join room_person b
on a.pk_bed_space = b.ck_bed_space, person c where a.pk_bed_space like 'AL%'  
and b.ck_move_in_date <= '2005-09-01' and b.room_person_move_out_date >= '2005-09-01' and
a.secondary_bed_space is not null and c.pk_rms_id = b.ck_rms_id
 
UNION
 
select a.pk_bed_space , NULL, NULL, NULL, NULL from rooms a where  
a.pk_bed_space not in (select a.pk_bed_space from rooms a left outer join room_person b
on a.pk_bed_space = b.ck_bed_space where a.pk_bed_space like 'AL%'  
and b.ck_move_in_date <= '2005-09-01' and b.room_person_move_out_date >= '2005-09-01' and
a.secondary_bed_space is not null) and a.secondary_bed_space is not null and
a.pk_bed_space like 'AL%'

This gives me an accurate snapshot of rooms in the AL building as of 9/1/2005.  The problem is, I want to make this query parmeter based so I can pass the date and the building via a parameter, but I'm not sure how to do it using this type of UNION query in Crystal...  Any thoughts?

Thanks again for all of your help so far! :)
0
 
mlmccCommented:
If you can build the query in the database Crystal may accept it.  To be able to pass the date in, you will need to use a stored procedure.

Crystal based queries will not handle such complexity.

mlmcc
0
 
ccorrenteAuthor Commented:
We used a subreport to get us what we needed.  Thanks!
0
 
mlmccCommented:
Glad i could help

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now