45 Days between multiple occurrences. My brain may explode! (help)

  • Thread starter Thread starter Simon Jester
  • Start date Start date
S

Simon Jester

Hi everyone. I really thought I was getting a handle on all this till
this evening. Now I am worried that my brain is going to explode.
My db has two kinds of customer services, I'll loosely define them as
Type_A and Type_B. I have a list telling me which service category id
falls under which type. Each service record has a unique service id,
customer id, service category id, and a service date. There can be
(and usually are) several different service records for each customer
from both categories.
All I want to see are the the customer ids of those who receive a
Type_B service within 45 days of receiving a Type_A service. It gets
confusing (and over my head), because of all the different services
that can be delivered. A simple datediff won't do it, I don't think.
Anyone have any thoughts on how I can do this? Can this even be done?
I hope I explained this clearly enough.
Any help would be ever so appreciated.
Thanks!
SJ
 
Use a subquery to find the closest service.

The specifics will depend on how your tables are set up.
This example assumes that Table1 has these fields:
ID primary key
CustomerID relates to the primary key of your Customer table.
ServiceDate date of the service
ServiceType contains 'Type_A' or 'Type_B' or whatever.

If that's the kind of thing you have, create a query using Table1.
Then type this whole thing (it's one line) into a fresh column in the Field
row:
ClosestService: (SELECT TOP 1 ServiceDate
FROM Table1 AS Dupe
WHERE (Dupe.CustomerID = Table1.CustomerID)
AND (Dupe.ServiceType = 'Type_B')
ORDER BY (Dupe.ServiceDate - Table1.ServiceDate), Dupe.ID)

In the Criteria row under the ServiceType field, enter:
'Type_A'
For each Type_A service, the subquery will return the date of the closest
Type_B service for the same customer. You can now add criteria under this
field:
Between Table1.ServiceDate - 45 And Table1.ServiceDate + 45

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Use a subquery to find the closest service.

The specifics will depend on how your tables are set up.
This example assumes that Table1 has these fields:
ID primary key
CustomerID relates to the primary key of your Customer table.
ServiceDate date of the service
ServiceType contains 'Type_A' or 'Type_B' or whatever.

If that's the kind of thing you have, create a query using Table1.
Then type this whole thing (it's one line) into a fresh column in the Field
row:
ClosestService: (SELECT TOP 1 ServiceDate
FROM Table1 AS Dupe
WHERE (Dupe.CustomerID = Table1.CustomerID)
AND (Dupe.ServiceType = 'Type_B')
ORDER BY (Dupe.ServiceDate - Table1.ServiceDate), Dupe.ID)

In the Criteria row under the ServiceType field, enter:
'Type_A'
For each Type_A service, the subquery will return the date of the closest
Type_B service for the same customer. You can now add criteria under this
field:
Between Table1.ServiceDate - 45 And Table1.ServiceDate + 45

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Back
Top