|
|
Posted by Slower Than You on 11/19/06 00:55
Razvan Socol wrote:
> Consider the following sample data:
>
> INSERT INTO CustTransactions VALUES (1, 1000, 0)
> INSERT INTO CustTransactions VALUES (2, 1000, 1)
> INSERT INTO CustTransactions VALUES (2, 2500, 1)
> INSERT INTO CustTransactions VALUES (1, 1000, 1)
> INSERT INTO CustTransactions VALUES (1, 1000, 1)
> INSERT INTO CustTransactions VALUES (3, 30000, 1)
> INSERT INTO CustTransactions VALUES (3, 17000, 1)
>
> What is the expected result (the output of SELECT * FROM
> CustTransactions) ?
Hi Razvan,
Thanks for responding. The expected result for the above sample data
would be:-
1, 1000, 0
2, 1000, 0
2, 2500, 0
1, 1000, 1
1, 1000, 1
3, 30000, 1
3, 17000, 1
To clarify this:-
1) The first row is completely ignored because its CustSelected field
is FALSE (as would be any other records where CustSelected = 0)
2) The rows WHERE CustSelected = 1 are sorted in descending order of
AmountSpent (where two or more records have equal values for
AmountSpent, the ordered of them is arbitrary - I don't care).
3) Any rows that would cause the sum of AmountSpent WHERE CustSelected
= 1 to exceed our selection criteria ($50,000) have their
CustSelected value set to 0.
>
> Also consider this sample data:
>
> INSERT INTO CustTransactions VALUES (1, 10000, 0)
> INSERT INTO CustTransactions VALUES (2, 20000, 1)
> INSERT INTO CustTransactions VALUES (2, 25000, 0)
> INSERT INTO CustTransactions VALUES (2, 2500, 0)
>
> What is the expected result in this case ?
Assuming our "target" figure is 50000 again:-
1, 10000, 0
2, 20000, 1
2, 25000, 0
2, 2500, 0
The three records where CustSelected = 0 are ignored. As a possible
point of additional interest, if the target figure was less than 20000
then row two would have had its CustSelected column set to 0 (because
this would have caused the "target" figure to be exceeded.
I hope I've done a better job of explaining my requirement this time
around!
--
SlowerThanYou
Navigation:
[Reply to this message]
|