## 3 replies

Last post Jan 28, 2017 11:46 PM by RichardY

Member

106 Points

460 Posts

### SUM Rate for the given m3 and the client

```Rates [Table]
SupplierID int
City varchar(50)
Rate money

Suppliers [Table]
ID int
Name varchar(50)

Clients [Table]
ID int
Name varchar(50)
City varchar(50)

Orders [Table]
ID int
SupplierID int
ClientID int
Status int

OrderItems [Table]
ID int
OrderID int
Quantity int
Cubics decimal(18, 3)
TotalCubics decimal(18, 3)```

How do i get for example the following?
[Pseudo Code]
GET A TOTAL RATE FOR ALL ORDERS WITH STATUS 2 AND SUPPLIER 1

NOTE: the Rate differs for each of the cities

Contributor

6864 Points

1981 Posts

### Re: SUM Rate for the given m3 and the client

```select sum(r.Rate)
from Rates r join Suppliers s
on r.SupplierID = s.ID
and s.ID = 1
join Orders o
on s.ID = o.SuppliersID
and o.Status = 2
join Clients c
on o.ClientID = c.ID
and r.City = c.City```

Member

106 Points

460 Posts

### Re: SUM Rate for the given m3 and the client

Haven't tested your code yet but it makes sense indeed. However i need the rate to be applied to the TotalCubics column so if say i have 12 m3 and the Rate is \$2.00 per m3 then the select should give me 24 dollars. Also i have missed to add two more fields in the tables Rates and Suppliers which are RatePerCategory and SupplierRateType respectively.

Meaning if the given Supplier's rate type is e.g. per category instead of per cubic then the other one should be considered and vica versa.

Thank you so much for any further help. It's much appreciated

Contributor

6864 Points

1981 Posts

### Re: SUM Rate for the given m3 and the client

This will evaluate the suppliersRateType to get either the Rate or RatePerCategory and multiply it by the TotalCubics FOR EACH ORDER and then RETURN A SUM of that for all orders.

```select SUM(case when s.SupplierRateType = 'Category' then r.RatePerCategory else r.Rate end * oi.TotalCubics)
from @Rates r join @Suppliers s
on r.SupplierID = s.ID
and s.ID = 1
join @Orders o
on s.ID = o.SupplierID
and o.Status = 2
join @OrderItems oi
on o.ID = oi.OrderID
join @Clients c
on o.ClientID = c.ID
and r.City = c.City;
```