I am developing a reporting framework that requires multi-step calculations for numerous customers. An added complication is that customers have specific though similar methods of calculations. For example some customers include datasets from stat holidays and some don't.

My question is around how I should stage these calculations. There are about 4 different methods for 6 customers. Does it make sense to make everything into 1 complex query using some type of boolean (0 or 1) and matrix multiplication. So I would add fields to my customer table that I could join to my data table and multiply each type of calculation by 0 or 1 depending if that specific customers uses that step or not. Or should I create a query for each method but then how do I filter out customers not using that method from the query without having to hardcode them out specifically.

Performance is not a huge concern because the datasets are relatively small and this process is performed only once a month.

Please let me know what further clarifications are required. Thanks


Use the second option "create a query for each method".

But then how do I filter out customers not using that method?

Create a table CUSTOMER, a table METHOD and and join table CUSTOMER_METHOD which tells which customer uses which method.

That way you don't have to harcode anything.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.