We are getting order wise details in a table

This commission will be divided into multiple partners based on predefined formula

Need to create daily & monthly earning for each partner so that amount can be given to them based on their earning

I need to write a stored procedure in SQL Server to make this daily & monthly earning report partner wise

CodePudding user response：

A short explanation is added to the DBFIDDLE:

select 
   partner,
   sum(commis) as commission
from (
   select partner1 as partner, commission/3 as commis
   from orders
   where orderDate between '2022-10-01' and '2022-10-20'
   union all 
   select partner2, commission/3 as commis
   from orders
   where orderDate between '2022-10-01' and '2022-10-20'
   union all 
   select partner3, commission/3 as commis
   from orders 
   where orderDate between '2022-10-01' and '2022-10-20'
  )c
group by partner;

NOTE:

• A long explanation is not done here because this is very basic SQL stuff.
• When calculating commission (commission/3) SQL returns an integer, because two integers are divided. It would be better to divide by 3.0, which will return a decimal value. (But this is left as an exercise for you!)