How to summing up value based on a different value range
-
Hello,
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?
-
There is a connection between the two otherwise how does the application work? You seem to be showing the results from the Payments table as the desired output, is that all you want? You need to post DDL and DML and include the related tables I am going to guess there is PatientId involved somewhere
...
-
I am joining the two tables on the basis of the number of days spent in the hospital with the range that it lies between in the payments tables.
After that i am creating a cumulative sum of the amount spent followed by ranking based on the largest cumulative sum by month by hospitalIts extra work for anyone to create DDL and DML and then trying out the query to get you the expected output. Please post DDL and DML to help others to help you as was mentioned by HappyGeeek above.
drop table paymentscreate table payments(serialnumber int,minday int,maxday int,amount int)
create table hospitalized(patientid int,eventcode int,numberofdays int,dateofarrival datetime,hospitalid int)
insert into payments values(1,0,3,300);
insert into payments values(1,3,6,350);
insert into payments values(1,6,8,400);
insert into payments values(1,10,12,450);
insert into payments values(1,12,16,500);insert into hospitalized values(1,10,5,'30-Jan-2018',100);
insert into hospitalized values(2,10,7,'25-Jan-2018',100);insert into hospitalized values(3,10,5,'1-Jan-2018',200);
insert into hospitalized values(4,10,2,'11-Jan-2018',200);select * from (
select y.hospitalid,y.month_dim,y.by_hospital_by_month_amount
,row_number() over(partition by y.month_dim order by y.by_hospital_by_month_amount desc) as rnk
from (
select x.*
,concat(convert(char(3),datename(month, dateofarrival),0),'-',datepart(year,dateofarrival)) as month_dim
,sum(x.net_amt) over(partition by x.hospitalid,concat(convert(char(3),datename(month, dateofarrival),0),'-',datepart(year,dateofarrival))) as by_hospital_by_month_amount
from (
select a.*
,p.*
,(case when a.numberofdays>p.maxday then
p.maxday
else a.numberofdays
end - p.minday )* amount as net_amt
from hospitalized a
join payments p
on a.numberofdays>p.minday
)x
)y
)z
where z.rnk=1 -
george-178499 Wow what a genius way to solve this. Was very impressed by your solution. It is helping me a lot.
Apologies for didn't posting the DDL & DML.I had one edge case in which this solution didn't work for me. Two hospitals had the same total income in some month.
I have tried to solve this by switching row_numbers to rank and adding top 1 with ties but this didn't work for me 🙁
Is there any solution for this edge case?
- vito4ico - Sunday, April 22, 2018 6:22 PM
So. If you have ties you are looking to get both the records in your output yes?. Then using rank instead of row_number should work. No need for top 1 after using rank
-
Thanks again,
I have manged to find a solution as you said I used rank in instead of row_number but also needed to add distinct on the 2nd select. otherwise it is returning to me duplicate results. Not sure way? :unsure:
But the distinct worked for me.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply