SkidsMcCoy
MIS
Hi Guys - new poster here. Enjoying the archives but can't find a solution to this problem.
I have a list of customers and transactions, and need to band them into 5 percentiles. Let's say there a million customers - I need to know the bandings for the top 200,000, the next 200,000 etc. These bandings to be determined by the number of transactions they have in the table.
Quick sample data (somewhat unreal):
Customer Transaction
Smith Apple
Smith Banana
Jones Banana
The problem is - the SQL on this database is very limited. For a start, there is no case statement. More importantly, there is very limited subquery functionality. You can pull pack a single column query for an 'IN' statement, and you can pull back whatever you want in an 'EXISTS' clause. But you can't join subqueries to eachother.
And I've a feeling you can only do Cartesian joins.
I realise this is not strictly ANSI-SQL, but this forum seemed the closest match. Besides, I'm sure there must be folks out there who would appreciate this kind of challenge. I fear it may not be possible without pulling off the full list and checking manually, but I'd love to be corrected.
Thanks,
Skids
I have a list of customers and transactions, and need to band them into 5 percentiles. Let's say there a million customers - I need to know the bandings for the top 200,000, the next 200,000 etc. These bandings to be determined by the number of transactions they have in the table.
Quick sample data (somewhat unreal):
Customer Transaction
Smith Apple
Smith Banana
Jones Banana
The problem is - the SQL on this database is very limited. For a start, there is no case statement. More importantly, there is very limited subquery functionality. You can pull pack a single column query for an 'IN' statement, and you can pull back whatever you want in an 'EXISTS' clause. But you can't join subqueries to eachother.
And I've a feeling you can only do Cartesian joins.
I realise this is not strictly ANSI-SQL, but this forum seemed the closest match. Besides, I'm sure there must be folks out there who would appreciate this kind of challenge. I fear it may not be possible without pulling off the full list and checking manually, but I'd love to be corrected.
Thanks,
Skids