Setting amounts to make additions based on boolean

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Setting amounts to make additions based on boolean

Author

Topic

mikeyq911
Starting Member

7 Posts

Posted - 2009-02-22 : 11:37:23

I am trying to make a query that makes a column for values of true or false that create sums for the created column amount, but I am just not sure how to do it, I have tried if's cases, where's. I am just totally stumped now. Any help would be great. Here is an example of what I had.

use DB2
Select 'Shows amount billed for contracted mover company'
Select jd.ID, c.LastName, c.FirstName, sum((Mileage*Rate) + (.10*Weight)) as [Amount]
From tblJobDetail jd
Join tblJob jo on jd.JID = jo.JobID
Join tblDrivers d on jd.DriverID = d.DriverID
Join tblCustomers c on jo.CustID = c.CustID
SET Amount =
if Heavy = True and Packing = True)
Amount = Amount + (.15*Weight)
else if Packing = True and Heavy = False
Amount = Amount + (.10*Weight)
else if Heavy = True and Packing = False
Amount = Amount + (.05*Weight)
ELSE Amount
END IF
Group by jd.JID, c.LastName, c.FirstName
ORDER BY jd.JID
GO

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-22 : 12:39:23

Where is this Heavy and Packing comning from? Are you using SQL Server?

You can use :

Case When Heavy = True and Packing = True then Amount+(.15*Weight)
When Heavy = False and Packing = True then Amount+(.10*Weight)
When Heavy = True and Packing = False then Amount+(.05*Weight)
Else Amount End

mikeyq911
Starting Member

7 Posts

Posted - 2009-02-22 : 15:10:35

Yup using sql server 2005

The heavy and packing are bit values in one of the tables that are true or false, used to determine extra cost on the noraml amount, but I will give your example a try right now and let you know if I get it working.

Thx

mikeyq911
Starting Member

7 Posts

Posted - 2009-02-22 : 15:27:47

use db2
Select JobID, c.ContactLast, c.ContactFirst, MoveDate, sum((MileageActual*MileageRate) + (.10*WeightEst)) as Amount,
Case
When Heavy = True and Packing = True then Amount + (.15*WeightEst)
When Heavy = False and Packing = True then Amount + (.10*WeightEst)
When Heavy = True and Packing = False then Amount + (.05*WeightEst)
Else Amount End
From tblJobDetail jd
Join tblJobOrder jo on jd.JobID = jo.JobID
Join tblDrivers d on jd.DriverID = d.DriverID
Join tblCustomers c on jo.CustID = c.CustID
Group by jd.JobID, c.ContactLast, c.ContactFirst, MoveDate
ORDER BY jd.JobID
GO

I am not good with the select case I am not sure how the info. for amount is going to display as it is an agg., any ideas if there is something wrong in my sytax, should sum even be declared to be displayed in the top portion or just during the case?

Thx

mikeyq911
Starting Member

7 Posts

Posted - 2009-02-22 : 15:29:09

it has problems recognizing other things for the output like jobid the way I have it setup.

mikeyq911
Starting Member

7 Posts

Posted - 2009-02-22 : 15:39:13

Have the below now but it keeps telling me that invalid column names for all the true and falses.

Hmm, well I'll keep at it. Also amount would be invalid since it is something I am creating not a current column in my tables.

use db2
SELECT jd.JobID, c.ContactLast, c.ContactFirst, MoveDate, 'Amount' =
CASE
When jo.Heavy = True and jo.Packing = True then sum((MileageActual*MileageRate) + (.25*WeightEst))
When jo.Heavy = False and jo.Packing = True then sum((MileageActual*MileageRate) + (.20*WeightEst))
When jo.Heavy = True and jo.Packing = False then sum((MileageActual*MileageRate) + (.15*WeightEst))
Else sum((MileageActual*MileageRate) + (.10*WeightEst))
END
From tblJobDetail jd
Join tblJobOrder jo on jd.JobID = jo.JobID
Join tblDrivers d on jd.DriverID = d.DriverID
Join tblCustomers c on jo.CustID = c.CustID
Group by jd.JobID, c.ContactLast, c.ContactFirst, MoveDate, Amount
ORDER BY jd.JobID
Go

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-22 : 23:04:58

[code]USE db2
SELECT jd.JobID, c.ContactLast, c.ContactFirst, MoveDate, 'Amount' =
SUM(CASE
When jo.Heavy = 'True' and jo.Packing = 'True' then ((MileageActual*MileageRate) + (.25*WeightEst))
When jo.Heavy = 'False' and jo.Packing = 'True' then ((MileageActual*MileageRate) + (.20*WeightEst))
When jo.Heavy = 'True' and jo.Packing = 'False' then((MileageActual*MileageRate) + (.15*WeightEst))
Else (MileageActual*MileageRate) + (.10*WeightEst)
END)
From tblJobDetail jd
Join tblJobOrder jo on jd.JobID = jo.JobID
Join tblDrivers d on jd.DriverID = d.DriverID
Join tblCustomers c on jo.CustID = c.CustID
Group by jd.JobID, c.ContactLast, c.ContactFirst, MoveDate
ORDER BY jd.JobID[/code]

mikeyq911
Starting Member

7 Posts

Posted - 2009-02-23 : 06:30:12

gonna give this a try right now thx

mikeyq911
Starting Member

7 Posts

Posted - 2009-02-23 : 06:32:00

awesome worked thx a bunch

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 07:14:06