-2

We are getting order wise details in a table

order orderDate order value commission partner1 partner2 partner3
1 1-Oct 1000 50 A B C
2 1-Oct 800 40 C D F
3 2-Oct 2000 100 B A K

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

PartnerID startdate enddate total earning
A 1-Oct 20-Oct 500
B 1-Oct 20-Oct 300

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

Rajpoot
  • 3
  • 2
  • 1
    Please explain how do you determine the `enddate` and also show the necessary calculation on how to derive at the earning value of `500` and `300`. Also include your current attempt – Squirrel Nov 05 '22 at 08:25
  • 1 partner can earn commission from multiple orders in a single day so need to calculate total earning day wise for each partner & then final report should have his total earning between startdate & end date for example last 1 week , last 1 month. 500, 300 is just example as single partner can earn from mutiple orders – Rajpoot Nov 05 '22 at 08:27
  • 1
    Can you share more details on: "this commission will be divided into multiple partners based on predefined formula" How do this formula look like? Also is `50` a percentage of the order (`50%` from 1000), or an absolute number (`$50` from 1000) ? – Luuk Nov 05 '22 at 08:35
  • 50 is the actual value .... for simplicity we can assume if 3 partners are part of an order 50/3 will be the gain for each partner – Rajpoot Nov 05 '22 at 08:42
  • Why should this be done in a procedure and what should happen with the results? This sounds more like a task another application should do. – Jonas Metzler Nov 05 '22 at 08:49
  • I understand this can be done using some java code but just wanted to take SQL experts advice if it can be done with SQL procedure just to avoid maintenance of seperate java application – Rajpoot Nov 05 '22 at 08:53
  • 1
    This can be done in a single SQL query, no need for an SQL procedure. – Luuk Nov 05 '22 at 09:05
  • @Luuk Could you please help with the logic/approach need to schedule it once in a day to generate daily earning report then need to aggregate for weekly or monthly earning. this earning report will save in a seperate table so that we can download anytime based on date range – Rajpoot Nov 05 '22 at 09:08
  • 1
    Do you need help on [How can I schedule a job to run a SQL query daily?](https://stackoverflow.com/questions/5471080/how-can-i-schedule-a-job-to-run-a-sql-query-daily), or do you need help on writing this basic SQL query ? – Luuk Nov 05 '22 at 09:11
  • need help on writing SQL query – Rajpoot Nov 05 '22 at 09:14
  • "just to avoid maintenance of seperate java application". With the answer I wrote you will not need to maintain a separate java application, but you need to main a query of which you do not know/understand how it works. Which of these two is the best option? – Luuk Nov 05 '22 at 09:22

1 Answers1

1

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!)
Luuk
  • 12,245
  • 5
  • 22
  • 33