Comparing Dates, based on SubTable Contents
Printed From: Crystal Reports Book — Forum Name: Technical Questions
Posted By: Stanno — 3 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]
Posted By: DBlank — 3 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
Posted By: DBlank — 3 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
Posted By: Stanno — 3 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
Posted By: DBlank — 3 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
Posted By: Stanno — 3 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.
Posted By: DBlank — 3 Feb 2011 at 6:21am
Thsi won't work. Let me reports in a second. Edited by DBlank - 03 Feb 2011 at 6:22am
Posted By: DBlank — 3 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?
Posted By: DBlank — 3 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)
|
