Table joins ignoring settings?

[petejigsaw]

I have two tables that I have linked together. One holds footfall data through the shop door, so there is a footfall total for every day of a given month.

I have linked it to the sales data table by date, using an = non-enforced LEFT OUTER JOIN as I need to show the footfall for every day and then any corresponding sales data.

However, with this link, I am failing to pull into the report the footfall data from the primary table for any day where there were no sales recorded.

I understood that LEFT OUTER JOINS pulled back every record in the primary table, irrespective of whether there was any matching data in the secondary table. Am I missing something?

Thanks in anticipation of assistance......

 

[IanWaterman]

If you add a filter based on a field in the second table you effectively over ride the Left Outer join.

ie if you have a select statement

table2.field = x

change to

(isnull(table2.field) or table2.field = x)

Ian

 

[petejigsaw]

Thanks Ian

I understand what you're saying, but am not sure how I can implement it given my requirements.

The footfall data (from {daily_summary}) is Table A and the sales data (from {peranal}) is in Table B.

On a given day (here the 14 March 2010), there were no sales transactions made (therefore {peranal} has no entries for the 14/3/2010.

The selection formula (shown below as it is currently) has some filters to limit sales data to the transactions that are constituents of a day's sales.


{daily_summary.summary_date} in DateTime (2010, 03, 14, 00, 00, 00) to DateTime (2010, 03, 14, 00, 00, 00) and
{daily_summary.zone_id} = "10" and
({peranal.pa_type} = "01" or
{peranal.pa_type} = "02" or
{peranal.pa_type} = "03" or
{peranal.pa_type} = "04" or
{peranal.pa_type} = "05" or
{peranal.pa_type} = "08" or
{peranal.pa_type} = "09" or
{peranal.pa_type} = "10" or
{peranal.pa_type} = "14" or
{peranal.pa_type} = "54" or
{peranal.pa_type}= "06" and {peranal.pa_proc} = "RPO")

How do I implement your suggestion so as to make sure that data is pulled back from {peranal} when there are transactions matching the criteris in the selection formula, but on days when there are not, the footfall data from {daily_summary} shows?

I've tried a few combinations without success.

Thanks

 

[IanWaterman]

Sorry misunderstood your post. It started by saying you have data for every day in first table, but later on explain there are days missing.

YOu need a calendar table which truly has every date and then link your current table with a left outer from this new table.

Ian

 

[petejigsaw]

Hi Ian

Apologies - my mistake if it was unclear.

TableA (the footfall table - {daily-summary}) does have data for EVERY DAY of the year for each store.

TableB (the sales data table - {peranal}) doesn't necessarily have data for every day. However, where there is data for a given day, I need to filter it because not all transactions recorded in {peranal} relate to sales.

Does that make sense now?

Many thanks

 

[IanWaterman]

TRy

and
(
isnull({peranal.pa_type}) or
({peranal.pa_type} = "01" or
{peranal.pa_type} = "02" or
{peranal.pa_type} = "03" or
{peranal.pa_type} = "04" or
{peranal.pa_type} = "05" or
{peranal.pa_type} = "08" or
{peranal.pa_type} = "09" or
{peranal.pa_type} = "10" or
{peranal.pa_type} = "14" or
{peranal.pa_type} = "54" or
{peranal.pa_type}= "06" and {peranal.pa_proc} = "RPO")
)

 

[petejigsaw]

Hi Ian

I tried that without success......

I think I'm well and truly stuck!

 

[IanWaterman]

Can you show SQL generated by Crystal.

It will give further clues as to why LO is being over ridden

Ian

 

[petejigsaw]

Ian

SQL query shown below:

Footfall SQL
SELECT "daily_summary"."summary_date", "daily_summary"."zone_id", "daily_summary"."total_in"
FROM "Footfall"."dbo"."daily_summary" "daily_summary"
WHERE ("daily_summary"."summary_date">={ts '2010-03-14 00:00:00'} AND "daily_summary"."summary_date"<{ts '2010-03-14 00:00:01'})
EXTERNAL JOIN daily_summary.summary_date={?FRS: peranal.pa_date} AND daily_summary.zone_id={?FRS: peranal.pa_branch}


FRS
SELECT peranal.pa_type, peranal.pa_proc, peranal.pa_branch, peranal.pa_date
FROM frs:radii.peranal peranal
WHERE peranal.pa_date={?Footfall SQL: daily_summary.summary_date} AND peranal.pa_branch={?Footfall SQL: daily_summary.zone_id}

 

[IanWaterman]

Are you using two commands on different databases.

For a left outer to work they must be on same database, you will have to use a link within your footfall dB to link to Peranal table on FRS.

Ian

 

[petejigsaw]

Ian

The data is in two different databases, but even if I create the {peranal} table in the footfall database and use the LO link on date as above, the same problem occurs.

I think it's back to the drawing board......

Many thanks for your assistance.

 

[IanWaterman]

What does the SQL look like with Peranal on the footfall DB.

Ian

 

[lbass]

If you can join the tables from the two databases, you could use a command as your datasource and solve this problem by using the selection criteria in your from clause. This allows the left outer join to remain in effect. It would take the form of:

select TableA.field1, TableB.field2
from TableA left outer join TableB on
TableA.key = TableB.key and
TableB.date >= {?Start} and
TableB.date < {?End}+1 and
TableB.field = "10"

//etc.

Another route is to remove the selection criteria from the second table and use them instead in conditional formulas in the body of the report.

-LB