Android Question How to code Sql which has the complex condition?

[Theera]

I have sql database which has Rents table. My problem is about Total Cost is not correct ,because [StayDays] can be 3 Hrs or be the number of days which is calculate in the code as follows is:

[Cost]*[StayDays] as Total Cost

if [StayDays] is 3 Hrs then Total Cost is [Cost]

WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL1,"SELECT [RoomNo] As Room No.,[Guest] as Name ,[Phone] as Phone No.,[StayDays] as Hr/Days,[Cost]*[StayDays] as Total Cost  From Rents",Null,0,True))

How do I code the code to be correct?

 

[emexes]

What data type is [StayDays]? String or numeric? How do you do math with it if it is not numeric?

How do you know whether [StayDays] is 3 Hrs or 3 Days?

What if [StayDays] is 2 Hrs?

 

[Theera]

Data type [StayDays] is String ,but I don't know how to use the command in sql to execute them.

 

[Xandoca]

you can do somenthing like:

SELECT [RoomNo] As Room No.,[Guest] as Name ,[Phone] as Phone No.,[StayDays] as Hr/Days,[Cost] * case when [StayDays] = '3 Hrs' then 1 else cast(staydays int) end as Total Cost  From Rents

To give you a better anwser, we need more information about content of StayDays variable.

 

[emexes]

Data type [StayDays] is String ,but I don't know how to use the command in sql to execute them.

So sometimes [StayDays] has the characters "3", " ", "H", "r", "s" ie a five-character string "3 Hrs"

and sometimes it has just digit characters ie a decimal number, being the number of days (nights?) of the booking?

 

[Theera]

Thank you all of you. The Sql is easy alitte bit(, but it is almost hardly.)

 

[Theera]

So sometimes [StayDays] has the characters "3", " ", "H", "r", "s" ie a five-character string "3 Hrs"

and sometimes it has just digit characters ie a decimal number, being the number of days (nights?) of the booking?

Don't worry. I use only Thai language. I have tried to explain in English, but my English is still poorly.

 

[Mahares]

Don't worry. I use only Thai language

I do not know if you solved your problem or not yet.
Here is the approach using the IIF SQL function:

Dim strQuery As String =$"SELECT RoomNo AS Room_No, Guest AS Name, Phone AS Phone_No,
    IIF(INSTR(StayDays, 'Hr') > 0, Cost, StayDays * Cost) AS Total_Cost FROM Rents
    "$

Let me explain the below syntax using the IIF and INSTR functions:

IIF(INSTR(StayDays, 'Hr') > 0, Cost, StayDays * Cost) AS Total_Cost  'use your thai equiv for Hr

If the word Hr (or your Thai symbol equiv) is found in the StayDays column, simply the ToTal_Cost is Cost which is the cost per day. But, if StayDays represents the number of days, then the Total_Cost is the cost per day times StayDays

 

[Theera]

I do not know if you solved your problem or not yet.
Here is the approach using the IIF SQL function:

Dim strQuery As String =$"SELECT RoomNo AS Room_No, Guest AS Name, Phone AS Phone_No,
    IIF(INSTR(StayDays, 'Hr') > 0, Cost, StayDays * Cost) AS Total_Cost FROM Rents
    "$

Let me explain the below syntax using the IIF and INSTR functions:

IIF(INSTR(StayDays, 'Hr') > 0, Cost, StayDays * Cost) AS Total_Cost  'use your thai equiv for Hr

If the word Hr (or your Thai symbol equiv) is found in the StayDays column, simply the ToTal_Cost is Cost which is the cost per day. But, if StayDays represents the number of days, then the Total_Cost is the cost per day times StayDays

Thank you. I like everyone's stratgies.