Print the last record for each customer??? 2

[Gert]

Hi all

what have is a table wich hold all invoive of customer wich buy on the store:
what i need is to print only the record with the older date for each customer, there are about 300 customer.
ex:

date cuscod customer amount
12-02-2003 6 Joe 1900
13-02-2003 4 Miguel 1500
14-02-2003 5 Frank 2000
14-02-2003 6 Joe 2800
14-02-2003 4 Miguel 1000
16-02-2003 5 Frank 2530
17-02-2003 5 Frank 1235
17-02-2003 6 Joe 2700

i need like this(just the last record from a date(buyini - buyend):

14-02-2003 4 Miguel 1000
17-02-2003 5 Frank 1235
17-02-2003 6 Joe 2700

 

[JBalcerzak]

If you create a customer/date based index in descending order, you should be able to get to the last order for a customer pretty easily...

index on bintoc(cuscod,4)+dtoc(date) to SalesTable tag last_order descending

Then set the order of the table and seek your customer

set order to last_order
seek bintoc(4,4)

That will point you to the last sale...

 

[Gert]

the problem is that i need to print all record a the same time, not one by one

 

[JBalcerzak]

What you then will probably need to do is to create a separate summary table for your customers. First get a list of each individual customer from your detail table, then iterate through your customer list, pulling the last sale from the sales detail file and using the data to populate the summary table - which you populate every time you'd run this.

Otherwise, you can add a logical column to display the record, index on it. Just a little more overhead because you want the records to print (last one) to be true and the ones not to print (all the others) to be false - so you need to update all the other records when you add new ones. Then you can filter on the logical field being true, which will display only those last records if you manage all the others ok.

 

[MadTown]

I believe the following SQL command should give you the results you want

Select * FROM tablename GROUP BY cuscode,date desc into cursor tmp

This should give you one record for each customer with the latest date. then use the cursor as the source for the report. of course, you should replace the name of your table where I have tablename.

Hope this helps

Ken

 

[MadTown]

oops!
Scratch the above it doesn't work. I should have tested before I posted sorry

Ken

 

[MadTown]

Ok, I think I've found a solution

 Select * from tablename order by cuscode,orderdate desc into tmp

Then design the report with a data grouping on cuscode then use the group header as the detail line(don't use the detail line). This will give you a report showing only the first record for each cuscode from the cursor which is the the one with the latest date.

Again, I appologize for the first post. I've tested this solution and it does work.

Ken

 

[CatalinC]

Solution 1:
select cuscod,date,amount from customer order by cuscod,date desc group by cuscod into dbf custtemp distinct

Solution 2:
select *,max(date) as datamax from customer order by cuscod,datamax group by cuscod into dbf custtemp distinct

You must use distinct clause!

 

[rgbean]

CatalinC,
Just be aware that both of your SELECTS will create a syntax error in VFP 8.0, because of the non-ANSI constructs. Basically when using GROUP BY, ALL fields requested must either be in the GROUP BY list or be part of an aggregate function (e.g. MIN(), MAX(), COUNT(), SUM(), etc.). Since all other fields would have returned "undefined" results, these are no longer available. Note: While these will work in VFP 7.0 and back, and you can change the default behaviour in 8.0 to create this ANSI non-compliant result, minimally they can be misleading and may actually provide incorrect data.

Rick

 

[MadTown]

CaitlinC
Your solutions don't work. The group by clause even with distinct seems to grab the last physical record in the table no matter what the date is. This is fine as long as there isn't a possibility that a previous record could have a later date.

Ken

 

[tesar]

Gert,
may be index on ... desc UNIQUE is for you...
Tesar

 

[ramani]

Hi Gert,

What happens if there are more than one record in the last day for the same customer.

Leaving this question...

SELECT *, IIF(MAX(DateField),.t.,.f.) AS lList ;
FROM myTable .... ORDER BY ... etc..

SET FILTER TO lList = .t.
OR
SET FILTER TO lList


ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[Gert]

thanks to all