Last post May 31, 2018 09:47 AM by 1jus
May 29, 2018 03:24 PM|1jus|LINK
I am having trouble working out the best way to aggregate a reasonably heavy query.
In the first instance I'd welcome an opinion from any of you SQL gurus as to whether what I'm trying to achieve is possible with SQL Server alone?
The requirement is to calculate a teams commission based on the rank (which is calculated on sales volume to determine the tier of commission payable). In addition if any "sub-team" (a team leader below the main team leader with his or her own team) accounts
for 40% of the main teams total sales then that sub-team sales total must not be included in the commission calculation. The steps as I see it:
If this is possible using just SQL, should I create 5 stored procedures to achieve this or is there a better approach?
May 29, 2018 03:48 PM|PatriceSc|LINK
It starts to be a bit complex but should be still possible. IMO it might be interesting to have a look at :
It both help in doing recursive queries and so to speak allows to define queries you can later immediately reuse to tackle complexity (kind of temporary views). Wiht this you should be able to create 2/3 base queries to get the basic information you need
and then assemble those queries to produce the final result you want...
Still unlike creating separate views you still have all under your eyes to first make this work (still if intermediate results are needed in some other context you could move some of that later to real separate views).
May 30, 2018 11:20 AM|1jus|LINK
Thanks for the reply PatriceSc, I have the recursion working which is a good step! For the remaining tasks I'm not sure if CTE would help? Also in your opinion do you think separate Stored Procs for each task?
May 30, 2018 11:45 AM|PatriceSc|LINK
To reuse a result set coming from a stored procedure is less natural. IMO even if not planning to use a CTE in the final result, it can be still convenient during the query design phase. Note that it allows to define multiple expression, and they can depend or
not from a previous expression :
WITH a AS (
SELECT etc... WHERE etc...
SELECT etc.. FROM a etc... -- reuse a
SELECT ... FROM ... -- from some other tables
-- Combine in your final query
SELECT * FROM a
JOIN b etc...
JOIN c etc...
So when writing a complex query it can be imo a good tool (even if you'll finally reuse each part separately).
May 31, 2018 09:47 AM|1jus|LINK