Two Queries in A Crosstab

[fionama]

Hi,
I am using Crystal Reporter V10 and a SQL Server database.
I am trying to create a report in Crystal which runs off two queries. I have previously done this in Business objects.

I want to show a table with a column of number of hospital admissions by specialty and consultant. This figure is obtained by entering a start date and an end date taken from the EpisodeI.AdmissionDate field.

The second column is "number of discharges". This will also be by specialty and consultant, and obtained by entering a start date and end date. However, this start date and end date is taken from the EpisodeI.DischargeDate field.


Specialty Consultant #Admissions #Discharges

Specialty1 Consultant1 15 20
Specialty2 Consultant2 12 11
....etc

Is it possible to link the specialties and consultants from both queries?
How is this done in Crystal? Thanks in advance.
Fiona

 

[lbass]

You could use "Add Command" as your datasource, and use a union all statement which would essentially merge the date fields into one column. That would allow you to use datetype (admission, discharge) as a column field in the crosstab. Try something like:

Select
table.`specialty1`,table.`consultant`,table.`admissiondate`, 'Admission' as datetype
From
`table` table
Where table.`admissiondate` >= {?startdate} and
table.`admissiondate` <= {?enddate}
union all
table.`specialty1`,table.`consultant`,table.`dischargedate`, 'Discharge' as datetype
From
`table` table
Where table.`dischargedate` >= {?startdate} and
table.`dischargedate` <= {?enddate}

-LB

 

[fionama]

Hi,
That worked...thanks very much. I have done a further breakdown on the example given.

Select Specialty_Code1, Consultant_Code, AdmissionDate AdmDate, 'Admission' as datetype, EpisodeNum, lower(Description) Specialty, Daycase,PatientStatus_Code, Discharge_Code
from EpisodeI, Specialty
where AdmissionDate between '{?1. Enter Start Date}' and '{?2. Enter End Date}'
and EpisodeI.Specialty_Code1=Specialty.Code
union all
Select Specialty_Code1, Consultant_Code, DischargeDate DchDate, 'Discharge' as datetype, EpisodeNum, lower(Description) Specialty, Daycase,PatientStatus_Code, Discharge_Code
from EpisodeI, Specialty
where DischargeDate between '{?1. Enter Start Date}' and '{?2. Enter End Date}'
and EpisodeI.Specialty_Code1=Specialty.Code

Under the "Admissions" column, I have the figures broken down into Day Cases/Inpatients and Public/Private.

I would like to view the "Discharges" column without the Inpatient/Day Case, Public/Private breakdown.

Admissions Discharges
DC IP
Pub Priv Pub Priv

Spec1 Cons1 2 6 1 7 18
Spec2 Cons2 6 11 5 8 20


....etc

Is there a way to do that?
Many thanks,
Fiona

 

[lbass]

For your column fields, use datetype first, and then for daycase/inpatients, use a formula {@DC/IP}:

if {command.datetype} = "Admission" then
{command.daycase} else "" //assuming daycase results in DC or IP

For the third column, use a similar formula:
if {command.datetype} = "Admission" then
{command.patientstatuscode} else ""//if this is code for pub/priv

Then go to the customize style tab and select the top two fields and check "suppress subtotal".

-LB