Click here to Skip to main content
15,901,505 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello All,

I want to calculate the room charges in my application.The condition is,
If any patient registered in between 12.01 AM to 11.59 PM should be applicable for whole day charges as per room segment, i.e. General Ward, Delux, etc,
After 12 AM in night it will be applicable to charge the second day charges as per ward type.

Can anyone suggest me to do that in sql script or in coding?

Thanks for Future Help.

This is code:

C#
decimal total_A = (Total_Hours / 24) * ChargesPer24Hour;
decimal total_B = 0;
decimal R = Total_Hours % 24;
if (R != 0)
total_B = ChargesPer24Hour;
else
total_B = 0;


ChargesPer24Hour taking from db for ward type charges.
Total_Hours, calculation done in SQL only and that this division will give us the exact day.
Posted
Updated 6-Jan-15 0:13am
v2
Comments
CHill60 6-Jan-15 5:49am    
What have you tried so far?
DT_2 6-Jan-15 5:53am    
I have tried for 24 hours after registering the patient,and I ma successful in that, not for my condition in question?
CHill60 6-Jan-15 5:55am    
Post the code that you have used
DT_2 6-Jan-15 6:06am    
[Redundant code removed — SA]
DT_2 6-Jan-15 6:10am    
[Redundant code removed — SA]

The first bug which immediately catches nearly anyone's eye is: you divide by 24 in first line. If gives you total roughly 24 times less ("roughly" because of discretisation of integer type). Why it happens?

Because you put the "problem" upside down. The real total should be
C#
decimal_total = daysOfStay * pricePerDay;

Now, the only problem is the daysOfStay. Usually people know it, not "hours". But let's assume that the system only knows check-in and check-out time. Again, normal hotel systems don't work with actual stay hours. They count days of stay and have certain checkout time, in terms of time of the day. If actual check-out time is earlier, they count days of stay, in later, one day is added.

But if we can assume that you use different system, how to get actual time of stay, to convert it to days or not? Well, it could be too much of assumption on my side, but… Let's say,
C#
System.DateTime checkIn = System.DateTime.Now;

// ...later:

System.DateTime checkOut = System.DateTime.Now;

System.TimeSpan timeOfStay = checkOut - checkIn;

And now you can extract any time measures (total millisecond, for example :-)) or number of days, or anything else from timeOfStay:
http://msdn.microsoft.com/en-us/library/system.timespan%28v=vs.110%29.aspx[^].

And then count those money the way you want. No need to "round" anything, fine nearest number or anything like that.

—SA
 
Share this answer
 
Comments
Maciej Los 6-Jan-15 18:03pm    
Great tutorial!
Sergey Alexandrovich Kryukov 6-Jan-15 18:19pm    
Thank you, Maciej.
—SA
Hello All,

Thanks for giving me the suggestions and solution on my query, I have find the solution as Sql query I have write as :
SQL
DATEDIFF(HOUR, STARTDATE, ENDDATE)
,

I have to just replace HOUR to DATE,
SQL
DATEDIFF(DATE, STARTDATE, ENDDATE)+1


Special thanks to @Sergey Alexandrovich Kryukov for giving us your valuable tutorial type suggestion.

Thanks
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900