Hi,

I'd previously posted a query and following a reply realised I was completely barking up the wrong tree hence this second post.  I'm using Crystal 2008 btw.

I’m planning on using the datediff function to calculate the number of weekdays from the request for some equipment to when it is issued but my initial selection criteria omits numerous records that include the date I want to check against.  

The WorkTable links on ROWID to the EquipmentTable.  ROWID is a text string that ends in /001, /002, /003 and so forth.

ReferralTable     WorkTable          EquipmentTable

Dept                     ROWID  ----------  ROWID

ReferralID  ------  ReferralID            IssuedDate

                            DateofWork
                            Notes

My Select uses parameters for the user to enter the chosen date range of issued equipment

IssuedDate in @FromDate to @ToDate

So for example someone might state that equipment is needed on ROWID /002 and then two other entries /003 and /004 are entered with various notes (item out of stock, item ordered etc) and eventually it is issued on ROWID /005.  I would specify the DateofWork for either ROWID /001 or /002 as the one to check dependant on Dept in the ReferralTable as they have different deadlines.  The problem is that the link to EquipmentTable is on ROWID so it only seems to have linked to the matching ROWID in the WorkTable in which the equipment was issued, in the example above this would be ROWID /005 as that was the one in which the equipment was issued and the ROWID /002 which contains the DateofWork I need to check is discarded as any ROWID’s not linked to EquipmentTable are not retrieved.

My current thinking is that I create a COMMAND to add some kind of buffer table between WorkTable and EquipmentTable that will still ensure all WorkTable entries are received.  

ReferralTable     WorkTable          Command           EquipmentTable

Dept                     ROWID                ROWID   ------->  ROWID

ReferralID  ------  ReferralID  ---->   ReferrallD           IssuedDate

                            DateofWork
                            Notes

However it freezes when run and I have to head into Task Manager to shut Crystal down.

Any suggestions or do you think the is a no hoper due to the design of the database itself.

Ben

Sure

ReferralID is simply an incremental number for each new referral, let's say 12345
ROWID is ReferralID with /001 etc appended so 12345/001 for the first entry, 12345/002 for the second entry.  WorkTable.notes is just a freeform text box and not really relevant to my query but is to demonstrate that there may be many increments starting 12345/001 until the equipment is finally issued.
All the Dates and your normal Date Time format

I'm trying to calculate a StartDate thusly
DatetimeVar StartDate;

If {ReferralTable.Dept} = "Reg" then
    StartDate := {ReferralTable.ReferralDate}
else
If ({ReferralTable.Dept} = "RH" and {WorkTable.RoWID} Like "*/002")
         then StartDate:= {WorkTable.DateofWork}
else
If ({ReferralTable.Dept} = "ADL" and {WorkTable.RoWID} Like "*/001")
         then StartDate:= {WorkTable.DateofWork}
I'm then using this to calculate the difference

(datediff("d",{@StartDate}, {EquipmentTable.IssuedDate}) -
    DateDiff ("ww", {@StartDate}, {EquipmentTable.IssuedDate, crSaturday) -
     DateDiff ("ww", {@StartDate}, {EquipmentTable.IssuedDate, crSunday))

This won't work because the StartDate formula can't retrieve the correct dates for /001 and /002 as they are excluded due to my inital Select based on the IssuedDate
So my attempt was to Add Command under the database expert  using this
 
SELECT "WorkTable"."ReferralID", "WorkTable"."RoWID"
FROM  "Register"."dbo"."WorkTable" "WorkTable"

And this in theory would still allow all the WorkTable entries to come through as only the Command would be stripped of non matching rows.

All I need to get is the DateofWork for matching /001 or /002 and if I can get that everything will work fine but those entries usually get missed because I am also needing to select only items with a IssuedDate in a parameter period.

Also fyi
I'm counting a Unique ID in the EquipmentTable and putting this in a crosstab and grouping on a specified order (0-7days, 8-15 etc).   If I just wanted to work on ReferralTable.ReferralDate which is the Date the referral was created everything works fine but some departments need to talk to the person getting the equipment first and then record it in the WorkTable.Notes box which is why I need to specify only the StartDate that matches the DateofWork for /001 or /002.