select for records with no matches in date range 1

[JanTeb]

What I am trying to do is have 2 tables linked by an outer join and I want to find all vehilces from my first table that have no fuel records (my second table) with in the last 6 months. I can get what has been fueled and I can get what has never been fueled but as soon as I try to put the six month criteria on it, it doesn't work. I am using version 8.5 and have order v9.

Here ia my selection criteria
({DATA_ENTITIES_COH.ENT_SuspendYN} = "N"
and {DATA_STATISTICTRANSACTIONS_COH.STATTRANS_ReadingOn} >= dateadd('m',-6,currentdate()))
or ({DATA_ENTITIES_COH.ENT_SuspendYN} = "N"
and isnull({DATA_STATISTICTRANSACTIONS_COH.STATTRANS_ReadingOn}))

hope someone can help.

Jan

 

[MJRBIM]

Can you post some sample data from the two tables and an example of how you would like the data returned and formatted?

 

[IdoMillet]

Try to reverse the order so that the IsNull condition occurs first:
----------------------------------------------------
({DATA_ENTITIES_COH.ENT_SuspendYN} = "N"
and isnull({DATA_STATISTICTRANSACTIONS_COH.STATTRANS_ReadingOn}))
or
({DATA_ENTITIES_COH.ENT_SuspendYN} = "N"
and {DATA_STATISTICTRANSACTIONS_COH.STATTRANS_ReadingOn} >= dateadd('m',-6,currentdate()))
----------------------------------------------------

I assume the join is an outer join...

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[JanTeb]

Example data is

DATA_ENTITIES_COH DATA_STATISTICTRANSACTIONS_COH
1 fueled 10 litres 03/01/01
1 fueled 50 litres 03/02/08
2 fueled 35 litres 03/02/04
2 fueled 40 litres 03/06/20
3 fueled 55 litres 03/08/15
4 fueled 23 litres 03/02/23
5 fueled 15 litres 03/07/10


Therefore I would like to see all vehicles not fueled in last 6 months or since May. My report should show

Entity
1
4

 

[GMcNamara]

You need to use a subquery. Do something like:

SELECT * FROM VEHICLE
WHERE VEHICLE.VEHICLE_ID IN
(SELECT FUEL.VEHICLE_ID FROM FUEL
WHERE FUEL.DATE >= DATEADD('M',-6,CURRENTDATE())

In 8.5, you will have to manually modify the SQL Query. In 9, you can use this directly in the SQL Command.

 

[lbass]

To maintain the left join, do not use a select on the right-hand table which limits the dates. Instead, group on {DATA_ENTITIES_COH.Entity} and then create a formula {@inlast6mos} for the detail section:

if {DATA_STATISTICTRANSACTIONS_COH.STATTRANS_ReadingOn} >= dateadd("m",-6,currentdate) then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@inlast6mos}, {DATA_ENTITIES_COH.Entity}) = 0

This should return only those groups with no service within the last six months.

-LB

 

[GMcNamara]

Oops, my SQL should have read:

SELECT * FROM VEHICLE
WHERE VEHICLE.VEHICLE_ID NOT IN
(SELECT FUEL.VEHICLE_ID FROM FUEL
WHERE FUEL.DATE >= DATEADD('M',-6,CURRENTDATE())

Depending on your database, you should be able to replace the Crystal CurrentDate and DateAdd functions with SQL so all of the selection is done on the server.

 

[JanTeb]

Thanks for all the help. Problem solved.

 

[GMcNamara]

I am assuming you took lbass's suggestion and I think that will work fine, IF you are simply doing a list. However, if you want to do any type of summary, etc. at the report level, I suspect you will run into trouble due to the group selection formulas. Just a thought.

 

[lbass]

GMcNamara--

I agree that using a subquery is probably the best approach, since it limits the number of records returned and then it is possible to do the usual summaries on those records. I don't regularly use subqueries, mostly because I'm just not familiar with them and have some trouble setting them up so that they work, but sometimes they are the only reasonable answer.

JanTeb--

What GMcNamara is referring to as troublesome is that you cannot use the usual summaries after doing a group select, but instead must use running totals. If you insert a sum, for example, the sum will include non-group selected records, while a running total would only include those records which are displayed.

-LB

 

[GMcNamara]

lbass,
Well said. You explained it much better than I did. I am using Crystal 9, so I prefer to work primarily with SQL Commands because I think the SQL Commands are easier to manage than the Linking Expert.