Hi everyone, hope you can help.

I’m trying to get delivery times by compare dates and whilst I’m fine with the datediff function there is some added complexity as to which date I compare to.  There are several tables involved but the 3 key ones are as below so here are the basics.

Referrals.referralsID
Referrals.firstcontactdate
Referrals.dept

RecordsOfWork.referralsID
RecordsOfWork.ROWID
RecordsOfWork.DateWorked

Equipment.ROWID
Equipment.LoanedDate


So Referrals.referralsID links to RecordsOfWork.referralsID and RecordsOfWork.ROWID links to Equipment.ROWID.  Referrals is linked to by something else I need and Equipment links to another table I need but I don’t want to overcomplicate things so I’ve left them out.

RecordsOfWork.ROWID ends in /001 for the first entry in that table, /002 for the second and so on.  Referrals.firstcontactdate is an automatic copy of RecordsOfWork.DateWorked for the first entry (/001)

I’m trying to report on the time taken from the DateWorked to the LoanedDate.  I’ve initially got it working using firstcontactdate but I need to also check against the DateWorked for a specific ROWID (in this case one that ends in /002) when the Referrals.dept matches certain criteria.  If I can specify which ROWID I want to compare on then I can get rid of firstcontactdate and just check for DateWorked on ROWID /001 as it’s the same.

My Initial selection criteria retrieves records for a LoanedDate range and then I’d like to use something like this pseudo code to pick which date I compare LoanedDate against using the datediff function

If referrals.dept = “RH” then DateVar StartDate := RecordsOfWork.DateWorked WHERE RecordsOfWork.ROWID Like “*/002” else
DateVar StartDate := RecordsOfWork.DateWorked WHERE RecordsOfWork.ROWID Like “*/001”

If I can get something like that WHERE pseudo command for real I think I’ve got it.  I’ve played around creating a separate SQL Select query using Add Command in the Database Expert  to create a separate table for /002 but to no luck as it throws up all sorts of problems for me as I’m not very familiar with that side of things.

Additional info, as I said I’m working okay with firstcontactdate and am using a cross tab to group into 0-7 days, 8-15 days etc and would like to continue using the cross tab as it’s clear to read.

Any help or suggestions will be most welcome

Ben

Unfortunately no,  I'm just getting a null return for any entry where the referral.dept = RH.  I'm thinking it's due to my original selection criteria of {Equipment.Loaned_Date} in {?@From_Date} to {?@To_Date} as this would only then supply the linked ROWID for when the equipment was loaned, and if that wasn't on ROWID /002 then it would produce a null as /002 wouldn't have been selected  I think I need to create a seperate table of  ROWID's and Dates and work from that maybe.  I'm not sure.