Hi all, attached file is a sample file that has few linkage of the table.
An overview of the industry, this dataset is a transportation hub where we move boxes from vessel to another vessel once it arrived at our location. When a vessel reach to our shore, the vessel will either discharge boxes to us or load boxes to the vessel as known as activity_code in our database.
An overview of the table structure , Data table contains each individual record of boxes being moving around either in Load or Disc. Each vessel will have multiple records in this table, this tabls is where we are able to capture the volume
Vessel is the details of the vessel capacity, the maximum volume it can handle
service and calandar is a dimension table
TX is the data table where the volume of boxes that are left on the vessel.
An example : a vessel that has a capacity of 600 will be discharging 200 TEU from the veseel and load 100 TEU to the vessel. However, there is 300 TEU still on board of the vessel remain untouch by us.
in that case, data table will have Discharge data of 200, Load data of 100 and TX table will have 300
The problem is we would want to find out how utilize the vessel is , in the above example. we would like to come out a formula to find out how fill up of boxes is on the vessel.
Formula is = ((Load TEU + Disc TEU) + (TX TEU *2)) / (Capacity *2) <-- can be found on sample Vessel Utilization (Help) Table
On vessel details table, these are the records of the vessels, note that each vessel name comes in multiple time within a month. The way we differentiate each individual call by the vessel is to DISTINCT Vessel Name, ATB and Voyage No.
The problem now is that I would want to eliminate vessels that comes in has LOAD TEU or DISC TEU that is less than 50 from the equation.Both side must be above 50 TEU in order to take in the vessel records to find the utilization.
On vessel utilization(help) in which I need codes to factor in this condition. In this table I have already came up with the formula for the formula, preferably not to touch the formula. is there any way I can do this. much appreciated as i have stuck this report for 6 months. Note that I am not able to edit anything from the script. Thanks much!
