Demurrage Calculation using a sliding scale

[namliam]

Sleeping on things a night can help a lot, this is actually quite simple to do

What you do is make a table: tblDay, this table has one column only DayNR
Which contains number 0 thru 366
366 obviously for one year, if you can have periods longer than one year obviously make it 700 or 1000 or 10000

Now go to qryElapsedDays, change that to

SELECT tblContainerMaster.Container
     , tblImportVoyage.VesselDepartureDate
     , tblContainerDetails.MTatWharf
     , IIf(IsNull([tblContainerDetails]![MTatWharf]),(Date()-[tblImportVoyage]![VesselDepartureDate]),([tblContainerDetails]![MTatWharf]-[tblImportVoyage]![VesselDepartureDate])) AS ElapsedDays
     , tblContainerType.ContainerType
     , tblDay.DayNr
     , [VesselDepartureDate]+[daynr] AS ChargeDay
     , tblDemurrageData.DemurrageRate
FROM tblDay
,   (tblImportVoyage 
INNER JOIN (tblContainerType 
INNER JOIN (tblContainerMaster 
INNER JOIN tblContainerDetails ON tblContainerMaster.ContainerID   = tblContainerDetails.Container) 
                               ON tblContainerType.ContainerTypeID = tblContainerMaster.ContainerType) 
                               ON tblImportVoyage.ImportVoyageID   = tblContainerDetails.ImportVoyage) 
INNER JOIN tblDemurrageData    ON tblContainerType.ContainerType   = tblDemurrageData.ContainerType
WHERE (    (    (tblDay.DayNr)>=[LowerRange] 
            And (tblDay.DayNr)<=[upperrange]) 
       AND (([VesselDepartureDate]+[daynr])<=Nz([mtatwharf],Date()))
      )
ORDER BY tblContainerMaster.Container, [VesselDepartureDate]+[daynr];

Now all you need do is make a relatively simple query on top of that....

SELECT qryElapsedDays.Container
     , qryElapsedDays.VesselDepartureDate
     , Format([ChargeDay],"yyyy/mm") AS [Month]
     , Sum(qryElapsedDays.DemurrageRate) AS SomVanDemurrageRate
FROM qryElapsedDays
GROUP BY qryElapsedDays.Container
       , qryElapsedDays.VesselDepartureDate
       , Format([ChargeDay],"yyyy/mm")
ORDER BY qryElapsedDays.Container, Format([ChargeDay],"yyyy/mm");

This you can easily query or make a parameter to only show a certain month...

Hope you get this working

P.S. One thing to take into account in all this, do you charge the exact first and last day?
In your sample database 01-01-15 to 07-02-15 is 37 days, 37 - 30 = 7 * 1.6 = 11.20
Using this new method it will count 30 thru 37 = 8 days * 1.6 = 12.80

Also just notice an additional problem in your tblDemurrageData table.
Your lower and upper range are equal this cant be.

Lower upper
0     30
30    60

Day 30 will be counted in the first range and the second.... instead (depending on the requirements it should be

Lower upper
0     29
30    60

Or

Lower upper
0     30
31    60

P.P.S. It may indeed be smart to add a "valid from" and "Valid to" dates to your tblDemarregeData, so you can work with any changes in the future wihtout affecting historic prices.

 

[gemma-the-husky]

I would still use a function. I am sure there are complications that make this very difficult to do in an "inline" query, even if the mailman's sql works exactly right.

eg. what if the rate changed at a certain point within the storage period

'for container ID we want to calculate the demurrage charge from dfrom to dto

function demurrage(ID as long, dfrom as date, dto as date) as currency

lookup the start of the demurrage period
lookup the end of the demurrage period

if dfrom is after the end of the storage period then demurrage is nil
if dto is before the start of the storage period then demurrage is nil

otherwise calculate which days of storage which the charging period occupies

eg, for a storage starting on 15th Jan to 17th April
the charging period of Jan represents days 1 to 17 (15-31st Jan)
the charging period of Feb represents days 18 to 45 (1-28th Feb)
the charging period of Mar represents days 46 to 76 (1-31st Mar)
the charging period of Apr represents days 77 to 93 (1-17th Apr)

you can use the dates, the day counters etc, to slice the charge anyway you want. If the demurrage rate changes at some point, you can take this into account as well

I am sure the rate charged also depends on who owns the item, how big it is, and so on.

That's the way I would do it anyway. 100%. Far easier to maintain a complex calculation tucked away in a nice black-box function.

 

[namliam]

My query isnt much of a black box, it after all is considered is a "simple" lookup query.

If you consider a query joining 6 tables in different JOIN types (left, right, cross) a black box, then yes... but queries are the database man's bread and butter.
I wouldnt advice using a function for most stuff, if only because of the considerable performance loss using a function instead of a query functionality.... particularly dealing with bigger numbers of records.

 

[gemma-the-husky]

No disrespect intended - I am sure the inline SQL works, although I don't really understand it.

I meant the function would be a discrete black box unit.

I thought what the OP was saying was that the demurrage charges are raised a month at a time - so the port authority? raises an invoice by container - which he wants to check.

so my understanding was given a charge for a container for April say, the charge may be for part of April, or for the whole of April, depending on when the storage started/finished. In either case, it may be part of a continuing hire, and the charge depends on how long the container has already been in storage.

That's why I thought it would be very difficult to code within a query, and it might be easier to maintain within a function. given the amount of demurrage cost being monitored, I shouldn't think speed of evaluation is the critical thing.

 

[Zuzu]

Namilam

You are a genius..... that query works exactly how i need it to!!!!

Only had to change one thing slightly (removed a > from the formula as it was adding an extra day payment at the end of each demurrage range).... once i did this the query worked perfectly.

Can't really express how grateful i am for your assistance on this. This issue has been bugging me for months, and you have managed to solve it in a day.!


Dave - appreciate your input as well. Will now do a bit of research on functions to try and understand what you have said and how it could apply to my DB.


Once again, i think that this is a fantastic forum, especially for someone learning the basics of DB design.

Thanks again

Zuzu

 

[namliam]

@ Dave
Technicaly it isnt even "inline" the Iif is "inline" but isnt really used in splitting the amount(s) into months in fact the IIF could be replaced by a (more) simple NZ but I desided against changing that

True inline sql is worse than a function, somehing along the lines of
Select (select ... from ... where ... ) , other fields
from Anytable

Only had to change one thing slightly (removed a > from the formula as it was adding an extra day payment at the end of each demurrage range).... once i did this the query worked perfectly.

You mean =, though I did mention this, this basicaly is a problem in your table not in the query IMHO.... though it can be handled in the query as well

One general thing has been nagging me, wondering how 2 people have the same problem very much simultatiously AND have a very simular if not identical setup or Zuzu = PNG?

 

[Zuzu]

Hi Namilam

PNGPorts is actually a work friend who helps me on these sort of projects from time to time.

I fly back to work on Monday and can't wait to show her the solution you have come up with.

I put the = back in and changed the table as suggested.... still works perfectly.

Cheers mate!

 

[namliam]

Well that explains

I fly back to work on Monday

:banghead:Killer commute or do you avoid the traffic jams by taking your private helicopter

 

[LofungMick]

Hello,

Just joined this afternoon and my first post so here goes. I am a beginner.

By sheer coincidence I have been building a fictitious database on this same subject as a self-teaching exercise. The management of containers has a few twists and turns which this thread suddenly answered for me in a welcome rush.

A couple of questions do remain.

First Observation. The table "tblDemurrageData" in the example is a stand alone table not linked to any other table at table level. Question. Is this permissible? Though I don't understand what other table and field it could be linked to (ShippingLine and ContainerType does appear in another table). Certainly I can see that it is linked at query level with the "qryElapsed Days" and I can see what it is doing.

Second Observation. The demurrage calculations are fine when there is only one Shipping Line's worth of rates entered into the table "tblDemurrageData", as per the example database provided. However, as soon as you enter more than one Shipping Line's worth of demurrage rates in the "tblDemurrageData", the demurrage calculation for each container goes wonky on me - it pulls in the rates for every container of that type within the upper/lower ranges regardless of the Shipping Line (the rates for each type of container vary between each Shipping Line that owns the container).

For ease of reference I am uploading the same database with the extra data entered. See the table "tblDemurrageData" where I have added two Shipping Lines - ANL and MSK. And the result in the two demurrage queries. I also de-hired a couple of containers (MTatWharf).

Any help or direction I can study further on the underlying concept would be appreciated.

Mike