Hi,
I've been struggling with this the whole day and would like some help.
I have table with daily reports with a start_date_utc and finish_date_utc. The report includes values for distance travelled in each report. The report includes all vessels identified by the their imo numbers. My end report will be sliced by the vessel name or imo number.
I'd like to calculate the total distance travelled for each month. I've created a date table, and calculated column in the main table to get just the date component from the datetime value of finish_date_utc.
the problem is that at the month end, (and month beginning) the report spans over the month.
For the month end report, I'd like to get the value of distance travelled from the next report, which falls in the next month. The distance is to calculated for the report period, and added to the month end reported distance in a prorated value.
For the beginning of the month, thus i'd like to calculate just the proportionate distance that is included in the current month.
for eg, if the report at the month end ended at 1600 hours, the distance travelled for the next 8 hours is to be calculated by first finding the next reported distance, say 200 miles in 15 hours, so the distance to be included in the month is 8*200/15.
