Calculation of Room Charges.

Question

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.

Piyush Pansuriya · Answer

Hi Dipesh If in every cases you have to add one more day than you can use it. DATEDIFF(DAY STARTDATE ENDDATE)+1

Dipesh Tare · Answer

@Piyush Sir Your case is not fulfilling my query. Case 1 : When StartDate is 7/1/2015 and EndDate is also 7/1/2015 In this case DATEDIFF = 0 means it is satisfying your case 1 to add 1. Case 2 : When StartDate is 7/1/2015and EndDate is 8/1/2015 In case DATEDIFF = 1 where I want it should be 2.Not satisfying else case in your query. So I should use only DATEDIFF(DAY STARTDATE ENDDATE)+1 in every situation. Please give your valuable suggestion that am I right or wrong?

Vikram Agrawal · Answer

Hi Dipesh you can check this in if condition if startDate and endDate are equal then you can set totalDays as 1 other wise you can get totalDays from your DateDiff() function. thanks

Piyush Pansuriya · Answer

Hi Dipesh It means patient comes 11:00 PM and Out 12:01 AM than consider 2 days charges? if I am not wrong than you can do by date only. like Sql Server : DATEDIFF(DAY START_DATE END_DATE) c# : ( EndDate - StartDate ). TotalDays Put if condition as per days charges.

Deepak Verma · Answer

Hi Dipesh You can't Accept your own answer by functionality. Only you can do is to accept the answer provided by other user which is more helpful to you (if any). It'll motivate users providing answers and indirectly help users looking for answers like you.

Dipesh Tare · Answer

@Hemant Sir Please see the picture. I am unable to close this thread? Why I dont know? Please help me.

Hemant Srivastava · Answer

Dipesh only you have the rights to accept the answer You may select any answer as an accepted answer

Dipesh Tare · Answer

Yes that`s I am saying in my Answer @Hemant Sir and @Piyush Sir. Can any one tick my answer as correct answer? Thanks for valuable suggestions.

Hemant Srivastava · Answer

Yes For your problem SELECT DATEDIFF(DAY @STARTDATE @ENDDATE) + 1 is correct in every case For example: DECLARE @STARTDATE DATETIME SET @STARTDATE = '2015-01-06 14:11:44.717' DECLARE @ENDDATE DATETIME SET @ENDDATE = '2015-01-08 00:15:00.717' SELECT DATEDIFF(DAY @STARTDATE @ENDDATE) + 1 gives result as 3 days

Piyush Pansuriya · Answer

Hi Dipesh in this situation use CASE in Sql Server like : SELECT CASE WHEN DATEDIFF(DAYSTART_DATEEND_DATE) = 0 THEN 1 ELSE DATEDIFF(DAYSTART_DATEEND_DATE) END *Like And Accept this answer if helpfull.!!

Dipesh Tare · Answer

@Piyush Sir your answer is helpful to me i.e. Sql Server : DATEDIFF(DAY START_DATE END_DATE) But in this case I have to add +1 in equation(DATEDIFF(DAY START_DATE END_DATE)) because if startdate is 7/1/2015 and enddate also is 7/1/2015 than it will give us 0 that is wrong so I have to add 1. Thanks for giving your precious time for my query.

Dipesh Tare · Answer

@Piyush Sir yes that is 24 hr but i want if a patient comes at 11 PM should be consider for charges applicable for that day. Than After as passed 12 in Night he will be chargeable for next day charges(for 24 hr). Thanks

Piyush Pansuriya · Answer

Hi Dipesh you are doing mistake in AM & PM. 12:01 AM to 11:59 PM means its a 24 Hours. First you clear your and mine confusion and you can do simply using if condition.