How to summing up value based on a different value range

by VitoDeep   Last Updated April 22, 2018 17:06 PM

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?

enter image description here

enter image description here

Related Questions

Data driven subscription with multi value parameter

Updated August 24, 2018 04:06 AM

Problem running SSIS package remotely using PoSH

Updated April 23, 2015 01:02 AM