Based on the tables below I need to Write a query that shows for each month in 2018 what was the best hospital in terms of total income. I need to return the hospitalID, the month, And the amount of monthly income from hospital admissions received during that period.
The month is determined by the dateOfArrival for example if somebody arrived on 30/01/2018 and numberOfDays is 5. The income should be counted for January and should be (3*300+2*350=1600).
Also Hospitalized and payment tables are not connected by any keys.
I'm having trouble finding a way of building this type of query. Can somebody guide me, please?