Query With Problems

SET NOCOUNT ON

DECLARE @port1 NVARCHAR(100) = 'port1'
DECLARE @port2 NVARCHAR(100) = 'port2'

DECLARE @ship_id INT = 1

CREATE TABLE #temp_voyage
(
    voyage_id int,
    departure_port nvarchar(100),
    arrival_port nvarchar(100),
    begin_time datetime,
    end_time datetime
)


CREATE TABLE #temp_speed_profile
(
    temp_id int identity,
    voyage_id int,
    departure_port nvarchar(100),
    arrival_port nvarchar(100),
    entry_time datetime,
    begin_time datetime,
    end_time datetime,
    speed_prev decimal(18, 3),
    speed_new decimal(18, 3),
    distance decimal(18, 3)
)

INSERT INTO #temp_voyage
    (
        voyage_id,
        begin_time,
        end_time
    )

    SELECT
        id,
        begin_time,
        end_time

    FROM
        ReportVoyage voyage

    WHERE
        ship_id = @ship_id AND
        end_time IS NOT NULL

    ORDER BY
        id DESC


DECLARE voyage_id_cursor CURSOR FOR
    SELECT
         voyage_id

    FROM
        #temp_voyage


DECLARE @voyage_id INT
OPEN voyage_id_cursor
FETCH NEXT FROM voyage_id_cursor INTO @voyage_id

WHILE @@FETCH_STATUS=0
BEGIN
    UPDATE
        #temp_voyage

    SET
        departure_port = (
            SELECT TOP 1
                departure_port

            FROM
                ReportLeg leg

            JOIN
                ReportValue value
            ON
                value.leg_id = leg.id

            WHERE
                voyage_id = @voyage_id

            ORDER BY
                leg_id ASC
        ),

        arrival_port = (
            SELECT TOP 1
                arrival_port

            FROM
                ReportLeg leg

            JOIN
                ReportValue value
            ON
                value.leg_id = leg.id

            WHERE
                voyage_id = @voyage_id

            ORDER BY
                leg_id DESC
        )

    WHERE
        voyage_id = @voyage_id

    FETCH NEXT FROM voyage_id_cursor INTO @voyage_id
END

CLOSE voyage_id_cursor
DEALLOCATE voyage_id_cursor


DELETE FROM
    #temp_voyage

WHERE
    ((arrival_port   NOT IN (@port1, @port2)) OR
     (departure_port NOT IN (@port1, @port2)))

    OR

    (@port1 != @port2 AND
        ((arrival_port = @port1 AND
         departure_port = @port1)

         OR

         (arrival_port = @port2 AND
         departure_port = @port2))
    )


SELECT
    *
FROM
    #temp_voyage


-------------------^^^^^ filter out voyages that aren't between ports @port1 and @port2 ^^^^^^---------------------
DECLARE @temp_id INT

DECLARE @distance DECIMAL(18, 3)
DECLARE @speed DECIMAL(18, 3)



DECLARE voyage_id_cursor CURSOR FOR
    SELECT TOP 3
         voyage_id

    FROM
        #temp_voyage

OPEN voyage_id_cursor
FETCH NEXT FROM voyage_id_cursor INTO @voyage_id

WHILE @@FETCH_STATUS=0
BEGIN
    INSERT INTO #temp_speed_profile
            (voyage_id,
             entry_time,
             speed_prev
            )

        SELECT
             voyage_id,
             entry_time,
             value.value

        FROM
            ReportValue value

        JOIN
            ReportValueType type
        ON
            value.type_id = type.id

        JOIN
            ReportLeg leg
        ON
            value.leg_id = leg.id

        JOIN
            ReportLegPhase phase
        ON
            value.leg_phase_id = phase.id

        JOIN
            ReportTime rtime
        ON
            value.time_id = rtime.id

        WHERE
            value.voyage_id = @voyage_id AND
            category_code = '-1' AND
            value_code = 'SOG' AND
            category_name = 'AUTO: Time Series' AND
            phase.phase = 'SEA'

        ORDER BY
            entry_time ASC

    FETCH NEXT FROM voyage_id_cursor INTO @voyage_id
END

CLOSE voyage_id_cursor
DEALLOCATE voyage_id_cursor


DECLARE voyage_id_cursor CURSOR FOR
    SELECT
         voyage_id

    FROM
        #temp_speed_profile

OPEN voyage_id_cursor
FETCH NEXT FROM voyage_id_cursor INTO @voyage_id

WHILE @@FETCH_STATUS=0
BEGIN
    DECLARE temp_id_cursor CURSOR FOR
        SELECT
             temp_id,
             speed_prev

        FROM
            #temp_speed_profile

        WHERE
            voyage_id = @voyage_id

    OPEN temp_id_cursor
    FETCH NEXT FROM temp_id_cursor INTO @temp_id, @speed


    SELECT @distance = 0
    WHILE @@FETCH_STATUS=0
    BEGIN
        SELECT @distance = @distance + @speed * 600 -- in meters -------------------------------------------------------------

        UPDATE
            #temp_speed_profile

        SET
            distance = @distance

        WHERE
            temp_id = @temp_id AND
            voyage_id = @voyage_id


        FETCH NEXT FROM temp_id_cursor INTO @temp_id, @speed
    END

    CLOSE temp_id_cursor
    DEALLOCATE temp_id_cursor

    FETCH NEXT FROM voyage_id_cursor INTO @voyage_id
END

CLOSE voyage_id_cursor
DEALLOCATE voyage_id_cursor


-------------------^^^^^ get the data for the last n voyages in speed_prev ^^^^^^---------------------

DECLARE voyage_id_cursor CURSOR FOR
    SELECT TOP 1
         voyage_id

    FROM
        #temp_voyage


OPEN voyage_id_cursor
FETCH NEXT FROM voyage_id_cursor INTO @voyage_id


WHILE @@FETCH_STATUS=0
BEGIN
    INSERT INTO #temp_speed_profile
            (voyage_id,
             entry_time,
             speed_new
            )

        SELECT
             voyage_id,
             entry_time,
             value.value

        FROM
            ReportValue value

        JOIN
            ReportValueType type
        ON
            value.type_id = type.id

        JOIN
            ReportLeg leg
        ON
            value.leg_id = leg.id

        JOIN
            ReportLegPhase phase
        ON
            value.leg_phase_id = phase.id

        JOIN
            ReportTime rtime
        ON
            value.time_id = rtime.id

        WHERE
            value.voyage_id = @voyage_id AND
            category_code = '-1' AND
            value_code = 'SOG' AND
            category_name = 'AUTO: Time Series' AND
            phase.phase = 'SEA'

        ORDER BY
            entry_time ASC

    FETCH NEXT FROM voyage_id_cursor INTO @voyage_id
END

CLOSE voyage_id_cursor
DEALLOCATE voyage_id_cursor

GO


DECLARE @voyage_id int
DECLARE @distance decimal(18, 3)
DECLARE @speed decimal(18, 3)
DECLARE @temp_id int

DECLARE voyage_id_cursor CURSOR FOR
    SELECT
         voyage_id

    FROM
        #temp_speed_profile


OPEN voyage_id_cursor
FETCH NEXT FROM voyage_id_cursor INTO @voyage_id


WHILE @@FETCH_STATUS=0
BEGIN
    DECLARE temp_id_cursor CURSOR FOR
        SELECT
             temp_id,
             speed_new

        FROM
            #temp_speed_profile

        WHERE
            voyage_id = @voyage_id

    OPEN temp_id_cursor
    FETCH NEXT FROM temp_id_cursor INTO @temp_id, @speed


    SELECT @distance = 0
    WHILE @@FETCH_STATUS=0
    BEGIN
        SELECT @distance = @distance + @speed * 600 -- in meters   -----------------------------------------------------------

        UPDATE
            #temp_speed_profile

        SET
            distance = @distance

        WHERE
            temp_id = @temp_id AND
            voyage_id = @voyage_id


        FETCH NEXT FROM temp_id_cursor INTO @temp_id, @speed
    END


    CLOSE temp_id_cursor
    DEALLOCATE temp_id_cursor

    FETCH NEXT FROM voyage_id_cursor INTO @voyage_id
END

CLOSE voyage_id_cursor
DEALLOCATE voyage_id_cursor


-------------------^^^^^ get the data for the last voyage in speed_prev ^^^^^^---------------------


SELECT
    temp_id,
    voyage_id,
    entry_time,
    speed_prev * 1.943844 as "speed_prev", -- from m/s to knots
    speed_new * 1.943844 as "speed_new", -- from m/s to knots
    distance / 1852 as "distance" -- from meters to nautical miles

FROM
    #temp_speed_profile


DROP TABLE #temp_speed_profile
DROP TABLE #temp_voyage