Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
qwertyjjj
Posting Yak Master
131 Posts |
 Posted - 2008-06-27 : 11:27:26
|
I have a table which splits monthdata into separate datasets by the MonthData column (this is because each month is itself used in a report).I need to get a top 100 client list out of this but the only unique reference is the docno, which is repeated in many months. I need to pick up the value from the latest month and then group it for a client. Any ideas?Sample datadocno client code amount month9110422 FGH112 100 06/2008912467 ASD112 99 06/20089110422 FGH112 100 05/2008 So, a report on the above ignores the older entry in 05/2008 and anything in 04/2008, 03/2008, etc.But the latest entry for one of the other invoices for client FGH112 might have been a few months ago as well.Essentially a turnover report. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-27 : 12:41:46
|
| not quite clear. do you mean you need top 100 distinct client list in decreasing order of month? |
 |
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2008-06-30 : 07:30:05
|
Top 100 by invoice amountSo withdocno client code amount month9110422 FGH112 100 06/2008912467 ASD112 99 06/20089110422 FGH112 100 05/20089110199 FGH112 50 03/2008 I would expect a result sayingFGH112 150ASD112 99docno's are not unique, I could use a temporary table maybe but prefer to use it on the fly.Also, it has to look historically and pick up a docno plus amount evcen if it isn't in the current month. |
|
|
|
|
|
|
getting top 100