Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Comparing Dates, based on SubTable Contents Post Reply Post New Topic
Author Message
Stanno
Newbie
Newbie
Avatar

Joined: 19 Jan 2011
Location: United Kingdom
Online Status: Offline
Posts: 9
Quote Stanno Replybullet Topic: Comparing Dates, based on SubTable Contents
     Posted: 03 Feb 2011 at 5:07am
Good morrow once more, Font of Crystal Knowledge.
 
I come before you today bearing yet another query.
 
I have two tables:
[Events]
[Eventtypes]
 
[Events] contains information pertaining to specific meetings/appointments, in term of worker, time, overall type of event.
 
[Eventtypes] contains the specific types of interactions recorded for the event.  Eventtypes is linked to Events by Events.ID
 
What I want to do is find the set of records where B happens before A.  The A and B components are recorded in Eventtypes, whereas the date they are related to is in Events.
 
More explicitly:
 
Where Eventtypes.Eventname = "B" and Events.Eventdate is less than the Events.Eventdate where Eventtypes.Eventname = "A" then [select record]
 
Nuke
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Feb 2011 at 5:19am
Are you limited to crystal only or can you write a SQL view or stored procedure as your source?

Edited by DBlank - 03 Feb 2011 at 5:21am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Feb 2011 at 5:22am
also do you need any display data form EventTypes or do you just need display data from Events?

Edited by DBlank - 03 Feb 2011 at 5:23am
IP IP Logged
Stanno
Newbie
Newbie
Avatar

Joined: 19 Jan 2011
Location: United Kingdom
Online Status: Offline
Posts: 9
Quote Stanno Replybullet Posted: 03 Feb 2011 at 5:55am
Heya Dblank,
 
I don't have direct SQL access, unfortunately.  I can get views installed, but I have to pass them to a separate department to get them loaded (have done that once before).
 
I wouldn't particularly need the eventype information displayed, an Event.ID list alone would be enough to do what I need to do.


Edited by Stanno - 03 Feb 2011 at 5:56am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Feb 2011 at 5:57am
SQL views would be more efficient but you can do this in a Crystal Command or using the group select expert. What is your preference?

Edited by DBlank - 03 Feb 2011 at 5:58am
IP IP Logged
Stanno
Newbie
Newbie
Avatar

Joined: 19 Jan 2011
Location: United Kingdom
Online Status: Offline
Posts: 9
Quote Stanno Replybullet Posted: 03 Feb 2011 at 6:02am
Using a Crystal solution, I can implement it tomorrow.  Using a View solution, would be a week due to having to pass it to another department*.
 
So, the Crystal method(s) are definitely my preferred.
 
------------
* I'm data analyst for a set of four services, that are part of a hundred service organisation; and we're not exactly 'core-service' to our organisation, hence there are delays when I put requests forward to manage their servers.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Feb 2011 at 6:21am

Thsi won't work. Let me reports in a second.



Edited by DBlank - 03 Feb 2011 at 6:22am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Feb 2011 at 6:27am

Do you always have and an A and B record or do you sometimes only have one of the 2?

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Feb 2011 at 6:29am

Both are Crystal solutions but I will use the group select expert here.

You may need to tweak my logic but this concept should work if you allways have both and A and B.
 
Pull all data with no record level selction unless you need to limit the data in some other way.
Group on Events.ID
create to formulas
B_date as
if Eventtypes.Eventname = "B" then Events.Eventdate else currentdate + 1
A_date
if Eventtypes.Eventname = "A" then Events.Eventdate else currentdate + 1
Now make 2 group level summaries using these formula fields
MINIMUM(B_Date,event.eventid)
and
MINIMUM(A_Date,event.eventid)
look in your grop footer and you should see claearly when you have any B that is less then A
Now you can use these in the select expert as a GROUP select
MINIMUM(B_Date,event.eventid) < MINIMUM(A_Date,event.eventid)
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum