SQL question

[Sdot]

I'm not sure if this is the correct place to ask.

 

My question is: "Solmaris charges $70 for the first hour and $30 for all following hours based on the estimated hours for every service request. List the condo ID, description, and service charge for every service request."

 

Does this look correct?

SELECT CondoID, Description, CASE WHEN EstHours = 1 THEN EstHours * 70 ELSE 70 + 30*EstHours END AS ServiceCharge FROM ServiceRequest

 

 

[gtx1060=value]

select * from

 

also, you have to create a new log with the charge/quote and then display that too

[gtx1060=value]

also, the 70 + 30*esthours part doesn't make sense - so you're already charging 70 for the first hour by esthour includes the first hour too, so you're really charging 100 for the first hour and then 30 for each after

[Sdot]

3 minutes ago, gtx1060=value said:

also, the 70 + 30*esthours part doesn't make sense - so you're already charging 70 for the first hour by esthour includes the first hour too, so you're really charging 100 for the first hour and then 30 for each after

oops, gotta change that now. Gotta make it 70 dollars for the first hour and 30 dollars for the hours after that.

[Sdot]

21 minutes ago, gtx1060=value said:

also, the 70 + 30*esthours part doesn't make sense - so you're already charging 70 for the first hour by esthour includes the first hour too, so you're really charging 100 for the first hour and then 30 for each after

SELECT CondoID, Description, CASE WHEN EstHours = 1 THEN EstHours * 70 ELSE 30 * EstHours END AS ServiceCharge FROM ServiceReq

 

Is this correct if I am trying to charge 70 dollars for the first hour, then 30 dollars after that?

[gtx1060=value]

nope, 

when hr = 1 charge 70

else charge 30*hr

 

i would try this

SELECT CondoID, Description, CASE WHEN EstHours = 1 THEN EstHours * 70 ELSE 70 + 30*EstHours
subtract 30 after your else statement 
END AS ServiceCharge FROM ServiceRequest

 

[Sdot]

13 minutes ago, gtx1060=value said:

nope, 

when hr = 1 charge 70

else charge 30*hr

 

i would try this

SELECT CondoID, Description, CASE WHEN EstHours = 1 THEN EstHours * 70 ELSE 70 + 30*EstHours
subtract 30 after your else statement 
END AS ServiceCharge FROM ServiceRequest

 

SELECT CondoID, Description, CASE WHEN EstHours = 1 THEN EstHours * 70 ELSE 70 + 30*EstHours - 30 END AS ServiceCharge FROM ServiceRequest;

 

Like this?

[gtx1060=value]

that should work - test it out

[Sdot]

SELECT CondoID, Description, CASE WHEN EstHours = 1 THEN EstHours * 70 ELSE 70 + (30 * EstHours) END AS ServiceCharge FROM ServiceRequest;

 

I think I got it. Here is my logic. 1st hour gets multiplied by 70 dollars. anything greater than that has the estimated hours multiplied by 30 dollars then gets added to the 70 from the initial hour.

 

Why were you saying to subtract 30?

[P4ZD4]

3 hours ago, Sdot said:

SELECT CondoID, Description, CASE WHEN EstHours = 1 THEN EstHours * 70 ELSE 70 + (30 * EstHours) END AS ServiceCharge FROM ServiceRequest;

 

I think I got it. Here is my logic. 1st hour gets multiplied by 70 dollars. anything greater than that has the estimated hours multiplied by 30 dollars then gets added to the 70 from the initial hour.

 

Why were you saying to subtract 30?

Since its 30 for every additional hour it should be like this?

CASE WHEN EstHours = 1 THEN EstHours * 70 ELSE 70 + (30 * (EstHours - 1))

Also can just have 70 instead of EstHours * 70

[Sdot]

I see, thank you