CODEWITHSUNDEEP
sql
Sven van den Boogaart
Sven van den Boogaart

Reputation: 12325

Select price depended on other table

I have two tables 1 is like;

Cost

id taxwork taxworkbuissnes taxmaterials hourrate extrahourrate drivingcost

Billing

id hours materials extrahours buissnes

What i want to do is call the total cost of the bill

If buissnes = 1 it means it is a buissnes billing and it gets discount on tax for hourrate.

If extrahours = 0 it means i need to use hourrate if it is 1 i need to use extrahourrate

Is it possible to do some kind of if statement so i can get the right totalhourcost, and the right tax.

edit : added fiddle

http://www.sqlfiddle.com/#!2/dd8d9/1

Upvotes: 0

Views: 70

Answers (2)

Sparky
Sparky

Reputation: 15105

Not 100% clear on what you are asking, but hope this gets you started

SELECT LaborCost,drivingCost,materials,
       Taxes,TaxMaterials,taxes+taxMaterials as TotalTaxes,
       laborCost+drivingCost+materials+Taxes+TaxMaterials as TotalCost
FROM
(
SELECT 
    CASE extraHours
        WHEN 1 THEN (hours*extrahourRate) 
        ELSE (hours*hourRate) END as LaborCost,
        drivingCost,
        materials,
        CASE Buissnes
        WHEN 1 THEN TaxWorkBuissnes ELSE Taxwork END as Taxes,
        TaxMaterials        

FROM bill 
JOIN cost ON bill.costid = cost.id
) xx

Upvotes: 0

Daniel B
Daniel B

Reputation: 807

Try this to calculate the total hour cost:

SELECT B.id,
 Hours
* (CASE WHEN B.extrahours= 1 then C.extrahourrate else C.hourrate END) 
* (1 + CASE WHEN  buissnes=1 then C.taxworkbuissnes/100 else C.taxwork/100 END)
AS TotalHourCost
FROM bill B JOIN cost C ON B.costid = C.id

Upvotes: 1

Related Questions