April 27, 2005 at 1:00 pm
I need to make a shipping cost calculation based on number of days when shipment took place.
The thing is: a company needs to pay us for every day they we ship something for them, it doesn't matter if they want us to ship one or ten parcels, they only need to pay for the days.
So I want to find on how many days a month we shipped parcels for a company and use this number in the same query to calculate what they need to pay us.
April 27, 2005 at 1:07 pm
You'll need to post the table definition along with some sample data so we can help you with your query.
April 27, 2005 at 2:45 pm
In the table I have this data for example:
date shipment client nr of collo Destiny
14-01-05 1254 012 1 France
14-01-05 1264 012 2 France
14-01-05 1259 012 1 Italy
18-01-05 1301 016 2 France
18-01-05 1315 012 5 France
18-01-05 1400 012 1 France
18-01-05 1388 012 1 USA
18-01-05 1333 029 1 USA
Now I want to calculate for each client what they need to pay per month on shipment, they only have to pay per day, so client 012 has got 2 shipments to France on 14-01-05 but they only have to pay once for that day. Another thing is; each country of destiny has got another shipment fee, for instance ; France= $ 25 , Italy = $ 30 and USA = Free
Thank you
April 27, 2005 at 3:34 pm
--create table dates([date] datetime, shipment char(4), client char(3), nrofcollo int, Destiny varchar(20))
-- set DATEFORMAT dmy
-- insert into dates ([date], shipment, client, nrofcollo, Destiny) values ('14-01-05', '1254', '012', 1 ,'France')
-- insert into dates ([date], shipment, client, nrofcollo, Destiny) values ('14-01-05', '1264', '012', 2 ,'France')
-- insert into dates ([date], shipment, client, nrofcollo, Destiny) values ('14-01-05', '1259', '012', 1 ,'Italy')
-- insert into dates ([date], shipment, client, nrofcollo, Destiny) values ('18-01-05', '1301', '016', 2 ,'France')
-- insert into dates ([date], shipment, client, nrofcollo, Destiny) values ('18-01-05', '1315', '012', 5 ,'France')
-- insert into dates ([date], shipment, client, nrofcollo, Destiny) values ('18-01-05', '1400', '012', 1 ,'France')
-- insert into dates ([date], shipment, client, nrofcollo, Destiny) values ('18-01-05', '1388', '012', 1 ,'USA')
-- insert into dates ([date], shipment, client, nrofcollo, Destiny) values ('18-01-05', '1333', '029', 1 ,'USA')
--select * from dates
Select client, MonthNo, Sum (TotalFees) Fee
from
(
select client
, month([date]) as MonthNo
, destiny
, count(distinct [date]) As ShipmentDays
, TotalFees = (case when destiny = 'France' then 25
when destiny = 'Italy' then 30
when destiny = 'USA' then 0
else 0 end) * (count(distinct [date]))
from dates
group by client , month([date]), destiny
) subq
group by subq.client, subq.MonthNo
hth
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply