Select earliest date before certain date, grouped by certain fields

[Mikey1987]

Hello,



I'm afraid I'm gonna need some gosu help with a query I'm making
I have sales representatives who visit clients, after which they (usually) send them one or more mails.

There are two tables with their fields below:

• VisitTable

ID
Rep_Name
CustomerKey
VisitDate

• MailTable

Rep_Name
CustomerKey
MailDate

For 1 visit, there are 0 to n mails.

What I want, is a query that tells me when my sales rep visited my customer (so my entire visit table), with in the next column the MailDate (if he never sent a mail, leave it blank). If there are more mails for 1 visit, just duplicate the visit rows.
A mail belongs to the visit that is the closest in the past.

Let's say i have 2 records in my sales rep visit table
ID | Rep | Customer | VisitDate
1 | John | Brian | 01/01/2015
2 | John | Brian | 02/03/2015

And 3 records in my mail table
ID | Rep | Customer | MailDate
1 | John | Brian | 02/01/2015
2 | Johan | Brian | 25/01/2015
3 | Johan | Brian | 08/03/2015

Then I'd like my query to say
ID | Rep | Customer | VisitDate | MailDate
1 | John | Brian | 01/01/2015 | 02/01/2015
1 | John | Brian | 01/01/2015 | 25/01/2015
2 | John | Brian | 02/03/2015 | 08/03/2015

The following query doesn't seem to work, because I don't get ALL my visits (I want to see my visits even if there are no mails)
Also, I don't know how I can say that an email doesn't only have to be greater than the visit date, but also small than the visit date of the next visit...


SELECT
Visit.ID
, Visit.Rep_Name
, Visit.CustomerKey
, Visit.VisitDate
, Mail.MailDate


FROM
Mail RIGHT JOIN Visit ON (Mail.CustomerKey = Visit.CustomerKey) AND (Mail.Rep_Name = Visit.Rep_Name)

WHERE
(Mail.MailDate)>=[visit].[VisitDate];


Does anyone have an idea?

Regards,
Mikey

[NTC]

it can be done if the ID column is sequential; joined on itself with +1; then Mail date is > than Visit Date and less than Visit Date ID+1

ID | Rep | Customer | VisitDate | MailDate ID+1 VisitDate+1
1 | John | Brian | 01/01/2015 | 02/01/2015 2 08/03/15
1 | John | Brian | 01/01/2015 | 25/01/2015 2 08/03/15
2 | John | Brian | 02/03/2015 | 08/03/2015 3 ???

But there are a couple of flaws in this for the last sequence of date there will be no data, and relying on sequential numbering of an autonumber field fails as soon as a record is deleted...

So simple +1 isn't a robust design.... should be able to do with a self join of Min ID > ID (rather than just +1) ..... but working out the syntax is a lot of work......

[ssanfu]

I don't think it can be done with the current table design.

Your results query has
ID | Rep | Customer | VisitDate | MailDate
1 | John | Brian | 01/01/2015 | 02/01/2015
1 | John | Brian | 01/01/2015 | 25/01/2015
2 | John | Brian | 02/03/2015 | 08/03/2015

but the date "08/03/2015" is for Rep "Johan", not "John"



You said

I have sales representatives who visit clients, after which they (usually) send them one or more mails.

1 visit -> 0 or more mail
1 mail -> 1 visit

This is a 1 to many relationship.


I would have the tables structured as:
tblVisits
-----------
VisitID_PK
Rep_Name_FK (foreign key to Rep table - a Long, not text)
CustomerKey_FK (foreign key to customer table - a Long, not text)
VisitDate
Desc

tblMail
-----------------
MailID_PK
VisitID_FK (foreign key to tblVisits)
MailDate
Desc (optional - why the mail??)

[Mikey1987]

Hello Sanffu,

you're right, I made a typo, "Johan" is in fact "John"

But sadly, I can't structure my tables the way i want and I can't have the VisitID_FK which you're referring to (getting that is the goal of this exercise).

I have a list of mails (exported from a mailing client) and a list of visits (exported from our CRM system).
We want to link the two, based on the date.

Can it be done, knowing that in my example John was Johan?

NTC, I think I understand your comment and I'll give it a go, but it seems very "fragile" to calculate with IDs, isn't it?

[NTC]

agreed on fragile reliance on ID - unless your table set is definitely including a sequential numbering.... as noted at end of my prior email - it will involve using the Min/Max of the prior record and one may as well use the date field rather than the ID field.... It is do-able but too complex to attempt some air code on the fly.....