Selection Formula - Grabbing a few date ranges

[ITTarget]

Background information:
I'm building a report designed to compare sales history by salesperson from "date range" this year to same "date range" last year. Included in these sales totals is a breakdown of total customer sales for each salesperson.

The problem I have is that every time we assign a different/new salesperson to a customer, our database retains info for that customer with the old salesperson. Thus, I can't just tell it to pull all accounts for the salesperson this year and last year because some accounts will have belonged to someone else last year (based on sales history). We can't get accurate growth for the salesperson by that customer. We primarily want to use this feature for gauging how new salespeople are growing a customer account from last year.

I'm open to other solutions, but what I'm currently trying to have the report select is:
(obviously not the real syntax....This portion is just trying to get the correct customer association with the saleshistory range for last year.)

invoice.InvoiceDate = daterangeLastYear //This part works
and
Salesperson = "John Smith" for daterangeThisYear //this is where I'm struggling.

I've been searching our database for a date field that would associate the correct customer with the correct salesperson. The association doesn't appear to show up unless there's sales history. Is there a way to modify my selection formula and get what I want? Is there a better way to do this than what I'm trying to do?

 

[SkipVought]

Select *
From YourTable
Where CustomerAccount in (Select CustomerAccount From YourTable Where Salesperson = 'John Smith')
And ...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lbass]

I think you could handle this by creating a SQL expression {%salesperson}:

(
select `salesperson`
from table A
where A.`customer` = table.`customer` and
A.`invoicedate` =
(
select max(`invoicedate`)
from table B
where B.`customer` = table.`Customer`
)
)

Then in the main report, create a formula {@mostrecSP} like this:

if {table.salesperson} = {%salesperson} then
{table.salesperson}

Insert a group on customer and then on {@mostrecSP}, and then group on the daterange. Or you could use a crosstab in the report header or footer with customer and {@mostrecSP} as the rows, and daterange as the column, and sum of sales as the summary.

-LB

 

[ITTarget]

I think you could handle this by creating a SQL expression {%salesperson}:

(
select `salesperson`
from table A
where A.`customer` = table.`customer` and
A.`invoicedate` =
(
select max(`invoicedate`)
from table B
where B.`customer` = table.`Customer`
)
)

Then in the main report, create a formula {@mostrecSP} like this:

if {table.salesperson} = {%salesperson} then
{table.salesperson}

Insert a group on customer and then on {@mostrecSP}, and then group on the daterange. Or you could use a crosstab in the report header or footer with customer and {@mostrecSP} as the rows, and daterange as the column, and sum of sales as the summary.

-LB

Playing around with this solution. I'll let ya know. TY!