Trying to find if a purchaser that made a return ever came back 1

[Hollymc]

I am trying to find out how many of my online purchasers ever bought anything and returned it and if they returned again and purchased something. So A buys B and returns B and then comes back and buys C and keeps it. I only want those that come back and buy junk from me.

Heres what I have so far. ExternalRef is the same on a purchases and a rejection. So 1011 would be the purchase reference and then 1011 would be the return reference, hence using it for the join.

SELECT DISTINCT a.accountnumber,a.dateplaced as purchasedate,a.exteranlref as [purchase ref] b.dateplaced as returndate,a.amount as purchase

from Transactions a join Transactions b
ON a.externalref = b.externalref
WHERE
a.Category1 = 'purchase' and
b.Category1 = 'return' and
a.RemoteAddress like '10.5%' and
a.AccountNumber = b.accountNumber and
a.DateSettled between @StartDate and
@EndDate and
b.DateSettled > @StartDate and
a.Operation = 'f' and b.Operation = 'f' and
a.ExternalRef not like '%n%'

Any suggestions would be super duper...Thanks

 

[gmmastros]

I don't always understand things so good. But...

It seems like a count of purchases, compared to a count of returns would do the trick.

Select accountnumber,
Sum(Case When Category1 = 'purchase'
Then 1
Else 0 End) As PurchaseCount,
Sum(Case When Category1 = 'Return'
Then 1
Else 0 End) As ReturnCount
From Transactions
Group By AccountNumber

Hopefully, your business rules prevent a customer from returning something that they did not purchase from you. If you assume this, then it's true that the number of returns can equal the number of purchases OR the number of returns can be less than the number of purchases. If the number of returns is less than the number of purchases, then you want to return the record, otherwise, don't return it. Extending on the previous query would give us...

Select accountnumber, 
       Sum(Case When Category1 = 'purchase'
                Then 1
                Else 0 End) As PurchaseCount,
       Sum(Case When Category1 = 'Return'
                Then 1
                Else 0 End) As ReturnCount
From   Transactions
Group By AccountNumber
Having Sum(Case When Category1 = 'purchase'
                Then 1
                Else 0 End)
       >
       Sum(Case When Category1 = 'Return'
                Then 1
                Else 0 End)

Of course, this is just a start. You can add additional where lause criteria immediately following the From clause to further filter the results.

Hoep this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Hollymc]

Thanks a bunch. That made perfect sense. What I may do is a simple join to just find the ones that did return something and then run what you gave me and see who repurchased.

The reason for this is sending Christmas cards to clients. And I don't want to send a Card to a jackass that bought and returned and never came back. But do want to send one to those that bought, returned and bought again.

Thanks.

 

[gmmastros]

I like christmas cards! I bet that if you tried hard enough, you could figure out where to send it.

Anyway, glad to help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom