Sum, ignoring first x number of records

[aranj]

Example would be, giving back 1% cash to all customers on all orders after their 3rd order. Theoretically, I could send a cheque (check) to them all for all their past custom.

I have:

SELECT mytable.Customerref, Count(mytable.Date) AS CountOfDate, Sum(([GrossSpend]/100)*1) AS [Refund]
FROM mytable
GROUP BY mytable.Customerref;

Which gives ref number, count of date (ie how many times they ordered - in this example they would not order more than once per day), and then the refund (1% of gross order). But I want to ignore completely their 1st two orders.

So, everyone gets 1% on their 3rd, 4th, 5th, xxxth order but not their 1st or second.

Can anyone help with this (probably simple) query. Thanks.

 

[KenHigg]

I may not be getting the entire picture here but, to start with I think I would consider a table to track these discounts. Or at least track them in the invoice table or somewhere...

kh

 

[aranj]

Say:


CustomerA
OrderDate Spend 1% refund
------------------------------------
01/01/04 £100 £1
03/04/04 £100 £1
11/04/04 £200 £2
31/07/04 £200 £2

My query gives total of gross £600, total refund £6.
I want to ignore the first 2 amounts (sorted by date) in everyone's totals to get £400 and £4.

Problem is I can't use date parameters as everyone's first 2 orders will be on different dates.

 

[KenHigg]

All of this is why I think I would run some code each time a transaction occurs that would check which order in the series a customer is at and tag an order as discounted...

kh

 

[aranj]

Thanks Ken, normally yes.

But this is retrospective and won't apply ever again. I was hoping to just do a query rather than dump the table into Excel and work it out there.

 

[KenHigg]

I can think of some ways to do this but they're a bit troublesome...

Maybe someone else will come up with something...

kh

 

[Pat Hartman]

You can create a query that returns the TOP 2 orders and use that in a subselect or nested query to select all orders EXCEPT those two.

 

[ssmithri]

Step 1: Create a new query and add "mytable"
Step 2: Right-click on the title bar of "mytable", go to alias, type in "myt1", then close the property box
Step 3: Change to SQL view and paste the following:

SELECT myt1.customerref, myt1.date, (Select Count(*) from mytable Where [Date] < [myt1].[Date]+1 AS visit
FROM mytable AS myt1
ORDER BY myt1.date;

Step 4: Save this query as "qry_Visit"
Step 5: Open a new query and add "mytable" and "qry_Visit", the change to SQL view and paste the following:

SELECT mytable.customerref, Sum((([GrossSpend]/100)*1)) AS refund
FROM mytable INNER JOIN qry_Visit ON (mytable.date = qry_Visit.date) AND (mytable.customerref = qry_Visit.customerref)
WHERE (((qry_Visit.visit)>2))
GROUP BY mytable.customerref;

That should do it.

Stu

Edit: Note that the "Wink" icon above consists of a ";" and a ")"... I can't seem to make the wink go away.

 

[KenHigg]

I can't seem to make the wink go away.

It could be just a nervous twitch...



Place it inside code tags(?)

kh

 

[aranj]

Thanks guys. Will give it a whirl.