Link to home
Create AccountLog in
Avatar of OCUW
OCUWFlag for United States of America

asked on

Dynamically determining the anniversary date or a customer’s purchase activities SQL2008R2

I would like to determine an anniversary date of sorts based on when at least 50% of the total order activity received from a customer based on the data in a SQL2008R2 database infrastructure.

For an example a customer’s total orders were $1,000 for the year but they were spread across multiple orders.
1-10-2012                 $   15.00            or 2% of the total
3-01-2012                       $ 525.00            or 54% of the cumulative total
5-29-2012                       $ 400.00            or 94% of the cumulative total
9-15-2012                       $   60.00            or 100% of the cumulative total

In this case the total orders on or before 3-01-2012 was $540 at 54% so I would like to see that date and cumulative amount as the results of my query.  

I would like to look at the total transactions and find the date the customer’s order amount totals hit the 50% mark and then extract that date to be used as an anniversary date.  Needless to say this would need to done for thousands of customers with the output being dynamic in the form of a view or stored procedure.

Thank you for any suggestions,
ASKER CERTIFIED SOLUTION
Avatar of OriNetworks
OriNetworks

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account