asked
Complicated SQL Function
I’m struggling to write a SQL function for a while now and I’m hoping someone has an idea on how to write this. I have customer table and each week a new record is inserted for each customer. The table has customer name, quantity purchased, and total amount purchased. A new record is inserted each week regardless of whether customer purchased a product or not. I want to find out when a customer becomes inactive based on the following logic: -if customer buys a product (where quantity or total amount is greater than 0) each week then they are active. If customer did not purchase a product more weeks in a row than they have purchased a product in a row then they are inactive or if they have missed 9 weeks in a row. For example, let’s say customer bought at least a product for 3 weeks in a row but after that did not purchase a product 4 weeks in a row then they would become inactive. However if a customer purchase a product for 3 weeks in a row and misses the next 3 weeks in a row and in the 7th week purchases a product then their status would remain active. So the next time in order for customer to become inactive is to miss 8 or more weeks in a row. Is it possible to write a SQL function for the below: ![alt text][1] [1]: /storage/temp/3634-parity.png
@Haque What should the function do? Is this going to be used to calculate the customer status and display records for a given week passed as a parameter? Or the function should calculate the current status for the latest week? Or all weeks displaying the historical status for each customer for all weeks? Is this correct that when inactive customer makes a purchase then it automatically becomes active thus resetting all previous counters meaning that it has to stop making purchases for 2 weeks in a row in order to become inactive again? Is there any possibility to modify the table to add the CustomerStatus column for each record? In this case, it would be a bit easier to write a function to calculate the current status and populate the values when the records for latest week are inserted (I assume that the records are inserted not row-by-row but rather by the means insert into select form statement). Please provide this info. Thank you. By the way, it looks like you meant to add the image to the question but the image did not come out right.
if you could add some example data, table structures and expected output, it will be much easier for people to pick this up and try and help
The solution for this (regardless of the answer to the questions in the previous comments) will depend on which SQL Server version you are running. If you run SQL Server 2012 or later, you could use window functions which would simplify the task a lot.
19 People are following this question.
