Demurrage Calculation using a sliding scale (1 Viewer)

[PNGPort]

I’m trying to build a database to calculate the demurrage on containers. The demurrage is determined by the container owner, the container type and the number of days that it is in use. I’ve calculated the number of days that it is in use in ElapsedDays query as well as collated the other required information. I can’t work out how to use this information in a second query to calculate the demurrage. An example is:
Owner = CCS
Container Type = 20DY
Elapsed Days = 40
Demurrage = (30 *0) + (10*2) = 20

I’m sure that it can be done through VBA or SQL but that is beyond my skill level. The calculation was originally in Excel using VLOOKUP and SUMPRODUCT.

Attached is a scaled down version of the database.

 

[bob fitz]

Can you post a copy of the db in A2003 mdb format.

 

[PNGPort]

Sorry Bob. Here's the 2003 version of the database.

 

[namliam]

Some changes needed to your tables
1) Upper and lower range should be the same for the calculations to work
i.e. one range end at 30, next range start at 30 not 31
2) I changed MtyAtWarfDate for TGHU1529090 to June 1st, 2014 to "force" usable test data.

This is what I came up with:

SELECT qryElapsedDays.ContainerID
     , qryElapsedDays.ElapsedDays
     , tblDemurrage.LowerRange
     , tblDemurrage.UpperRange
     , tblDemurrage.Rate
     , IIf([upperrange]<[elapseddays],[Upperrange]
                                     ,[Elapseddays]-[lowerrange])*[rate] AS Demurrage
FROM qryElapsedDays 
INNER JOIN tblDemurrage ON qryElapsedDays.ContainerType = tblDemurrage.ContainerType
WHERE      qryElapsedDays.ElapsedDays>=[LowerRange]
GROUP BY   qryElapsedDays.ContainerID
         , qryElapsedDays.ElapsedDays
         , tblDemurrage.LowerRange
         , tblDemurrage.UpperRange
         , tblDemurrage.Rate
         , IIf([upperrange]<[elapseddays],[Upperrange],[Elapseddays]-[lowerrange])*[rate];

This will show you the detail calculation per "day range"

Use this, if you are not intrested in details but only want to see the totals:

SELECT qryElapsedDays.ContainerID
     , qryElapsedDays.ElapsedDays
     , Sum(IIf([upperrange]<[elapseddays],[Upperrange],[Elapseddays]-[lowerrange])*[rate]) AS Demurrage
FROM       qryElapsedDays 
INNER JOIN tblDemurrage ON qryElapsedDays.ContainerType = tblDemurrage.ContainerType
WHERE qryElapsedDays.ElapsedDays)>=[LowerRange]
GROUP BY qryElapsedDays.ContainerID, qryElapsedDays.ElapsedDays;

Good luck !

 

[PNGPort]

Thanks Namliam! It works brilliantly.

 

[Zuzu]

First off - as my first post i want to say how good a forum this is. I have learnt so much from this forum, and there are quite a lot of knowledgeable posters!!!

Anyway, resurrecting an old thread.

Following the advice from Namliam, i have been able to create a query to calculate my demurrage (hire) costs, but it is not calculating correctly.

For example, if i have a container on site for 95 days, the total demurrage cost should be:

0 - 30 days - $0.00 / day = 30 x $0.00 = $0.00
31 - 60 days - $1.60 / day = 30 x $1.60 = $48.00
61 - 90 days - $4.00 / day = 30 x $4.00 = $120.00
90+ days - $8.00 / day = 5 x $8.00 = $40.00
Total demurrage Cost = $208.00

However the query i have returns a figure of $456.00 for 95 days?

Anyone able to help fix my query?

Cheers

Zuzu

 

[TimW]

Hi
I have had a quick look. In your qryDemurrageCalcDetails you need to take ElapesedDays minus LowerRange as your demurrage time for each range. you THEN need to sum the totals of each range for each container. (Ignore any minuses)


HTH

Tim

 

[Zuzu]

Hi Tim

Thanks for your reply.

I think i understand what you mean.... but still having trouble in working out how to express this in the query.

This can still be done using the one single query?

Cheers

 

[namliam]

1) there is an issue in your tblDemurrageData, your "last" upper range (>90) should have a huge value as upperrange (something like 9999) otherwize anything will break unless you make a special exception.

2) It would seem like there is an issue with my query which I hope PNGPort caught and fixed on his own

IIf([upperrange]<[elapseddays],[Upperrange],[Elapseddays]-[lowerrange])*[rate]

Should read

IIf([upperrange]<[elapseddays],[Upperrange][U][B]-[lowerrange][/B][/U],[Elapseddays]-[lowerrange])*[rate]

Or in your case

 IIf([upperrange]<[ElapsedDays];[Upperrange]-[lowerrange];[ElapsedDays]-[lowerrange])*[Demurragerate]

Though I guess it may depend on your requirements, but I believe both your's and PNG's are the same....

(Ignore any minuses)

Since the query has a where ElapsedDays >= LowerRange, minuses will not happen

 

[gemma-the-husky]

just a general point.

when you get a pretty complex calculation it can be easier to pass the values to a function to evaluate the result.

sort of

function demurrage(owner as long, container as long, days as long) as currency
end function

then you can do much more complicated things within the function body.

 

[TimW]

Very true Dave..
AND easier to read/follow

 

[namliam]

True, though IMHO not very valid in this case...

 

[Zuzu]

Namilam

Thank you very much for your help..... the query works perfectly!

Also to Dave and Tim, appreciate your responses.... some more things for me to learn and research.

Not coming from a back ground of programming and IT, i have to say that this forum has been a fantastic learning tool for me. What i have found frustrating is knowing in my head what i want to achieve with my database, but actually implementing it can be at times quite difficult - therefore i am extremely appreciative of the time you guys have taken to help out with my questions.

Now, i do feel bad for asking, but there is one final piece of the puzzle i need help with.


From the solution that Namilam has given, i can now successfully determine the correct demurrage costs for a container for the elapsed day period. However, i need to be able to run a parameter query to work out the costs for certain time periods.

For example:

What was the total demurrage cost for containers for a month (user then enters start date and end date...)

Once again, i am having difficulty on how to express this in the query. What would be the easiest way to achieve this. Would i have to write a few different queries to get this result?

I promise that this will be my last question!!! the rest of the database i am working on is fairly simple.

Cheers

Zuzu

 

[gemma-the-husky]

what do you use the demurrage total for?

although you CAN calculate it on the fly every time, if this is a chargeable cost, I would be inclined to store the calculated total somewhere on your invoice record.

in which case, you just have a query based on your invoices which includes the demurrage. Either turn this into a form or report, and you can add a total at the bottom. Or do a "totals" query, to get at the total. If you need both detail AND total at the same time, then you do need a form/report, I think.


if it is a "temporary" calculation, then you should be able to use mailman's idea for all your orders, and then devise another query to summarise that query.

that's the way to go with access. Build up logically.

{if you like the idea read the amusing SF story, "Build Up Logically - Howard Schoenfield")

 

[Zuzu]

Hi Dave

That was quick.

A bit of back ground..... i work at a gold mine looking after logistics on an island in the South Pacific region. All of the cargo we receive comes via ship and is stored in shipping containers.

These containers are owned by the shipping lines, and we pay a demurrage fee (essentially a hire fee) for them as per the sliding scale in the previous posts.

I have been given the task of reducing the demurrage costs (as they are extremely high), and am designing a database to be able to track the movement of the containers on site and also the demurrage costs associated.
At the moment, all of the information is captured on spreadsheets, but it is very hard to pull information of this quickly and accurately.

What happens now is that i receive a demurrage invoice from the shipping line for a month period (usually a month later than the invoice month which helps aid the confusion), which has all of the containers listed, the elapsed days, and then charges for the invoice period.

I then have to do a reconciliation of all of the moves and charges to make sure we are not being over charged. This process can take up to 2-3 days currently as i have to dig through quite a few different spreadsheets to get the information.

What i would like to be able to do is run a parameter query in my database, and pull this information within 1 minute!

I know that the database i have started to make has all of the necessary information in it. My biggest hurdle though has been the following:

1. Writing a query to work out total demurrage cost for a container (Solved!)
2. Write a parameter query to then work out demurrage charges for a user requested period (need help with this)

Once i have this, i can then either generate a report to help with the reconciliation, or export the result to excel and finish the reconciliation.

Hope this makes my issue a bit clearer.

Cheers

 

[namliam]

How does the period work???

Lets say a container is sitting iddle Jan thru March,
Now you want to calculate per Februari.... do you pick Feb 1st or 28th?
Do you calculate 31 days, 59 days or 89 days (all the way thru march?)

Give us/me some sample calculations and I am sure the query can be adjusted to suite your needs.

 

[Zuzu]

G'day Namliam

The demurrage is calculated for a calendar month period.

So, lets use the example of a container that has been on site for 100 days, with demurrage charges commencing on 1st January.

January invoice:

elapsed days on site = 31

30 days x 0.00
1 day x $1.60
(so invoice total is $1.60 for January)

February invoice

elapsed days on site = 59 (31 days Jan + 28 days Feb)

28 days x $1.60
(so total of invoice is $44.80 for February)

March invoice

elapsed days on site = 90 (31 days Jan + 28 days Feb + 31 days March)

1 day x $1.60
30 days x $4.00
(so total of invoice is $121.60 for March)

April Invoice

elapsed days on site = 100 (31 days Jan + 28 days Feb + 31 days March + 10 days April)

10 days x $8.00
(so total of invoice is $80.00 for April)


So, the total charge for the 100 days is $248.00 (which our current query works out correctly)- however, i need to be able to run a query to determine the calendar month (or user defined period) as well so i can reconcile against the invoice details.

Hope this clears it up a bit.

Cheers

 

[namliam]

Wow that I need to think about for a bit, it is an actual billing system you are looking for...

Do you check the invoices only, or does the DB also need to generate them?

so basicaly you dont want to sum the total amount, however you want to total it only for the one single month....

 

[Zuzu]

Hi Namilam

My DB is not used to raise any invoices.... i only need it to generate the figures so i can check them against the invoice that the shipping line provides. I will also use the DB so that i can watch and track my demurrage budget.

The DB will also be linked to my other DB which tracks job allocations and logistical information at work.

As i said in a previous post, i currently do this monthly reconciliation using spreadsheets.... and it takes quite a bit of time to do so.

At any one stage we can have up to 1000 containers incurring demurrage charges - ranging from elapsed days of 1 to 5000...... so you can probably see why i want to put this into a DB and be able to generate a monthly report.

Creating this DB is one of the last (but probably the biggest step) in my project. At the moment i have seen savings of over USD 2 million for the last year alone on demurrage charges by increasing my focus on it.

Once again.... greatly appreciate any help that can be given!

Cheers

Zuzu

 

[TimW]

Hi Again
I would write a function for this... You can use a User Defined function in a query.
The parameters for the function would be date first arrived on site and reporting calendar month/year and perhaps if valid the date the demurrage ended. The costs would be constants in the function - or pulled in from a table. If you can express this mathematically then you can write a function for it with a figure at the end.
Use a query to fire the function and you can do the whole lot at once. Or you can call the function from a single record and get the value form that. - You could always store the results in a table so you have a record of what all the values where - handy if any of the costs change and you need to revisit old data....

Something to think about....

T