Only join to latest entry in subselect in a given range determined by the main select I have one table Haul and one table ChargeCarrier_Storage. The latter contains movements of charge carriers between storage locations. Haul has an arrival and a departure time. I want to join together these two tables to get a list of all charge carriers which have been loaded onto a haul (=trailer).

My select works fine in most cases. There are some rare cases, though, when a worker books a piece onto the trailer, then back into the storage and then back into the trailer again (in between that time span between arrival and departure). In such a case, the same charge carrier will appear multiple times in the list with all data identical except for the StoredOn date.

This is not wrong per se, but the view we're talking about is used to create loading lists. And on there, only the latest entry should appear. So if you load it on, off and on again, it should be in the list once. If you load it on and off again, it should not be in the list at all.

Here's my current statement:

SELECT
    h.ID AS ID_Haul,
    h.DescriptiveID,
    ROW_NUMBER() OVER(PARTITION BY h.ID ORDER BY ccs.StoredOn ASC) AS Pos,
    cc.PickingNo,
    pin.PickingStorage,
    pin.TargetCountry,
    cc.[ObjID],
    cc.Customer,
    cc.Article,
    cc.Quantity,
    cc.QuantityUoM,
    cc.PackageLength,
    cc.PackageWidth,
    cc.PackageHeight,
    cc.PackageWeight,
    ccs.StoredOn,
    CAST(CASE WHEN h.Depature IS NULL THEN 0 ELSE 1 END AS BIT) AS Departured,
    ROW_NUMBER() OVER (PARTITION BY ccs.ID_ChargeCarrier ORDER BY ccs.StoredOn DESC) AS ORD -- TEST
FROM dbo.Haul h
INNER JOIN dbo.ChargeCarrier_Storage ccs ON
    ccs.ID_Storage = h.ID_Storage
    AND (
        h.Depature IS NULL AND ccs.StoredOn &gt;= h.Arrival
        OR ccs.StoredOn BETWEEN h.Arrival AND h.Depature
    )
INNER JOIN dbo.ChargeCarrier cc ON cc.ID = ccs.ID_ChargeCarrier
INNER JOIN dbo.PickingInfo pin ON pin.ID_ChargeCarrier = ccs.ID_ChargeCarrier
WHERE
    h.ID = 1167
order by PickingNo


How can I make it work properly? Right now, it displays the same charge carrier multiple times, and I also don't know how it responds, when you unload the piece again.

The tables look like this:

CREATE TABLE dbo.Haul
(
    ID INT NOT NULL PRIMARY KEY IDENTITY, 
    ID_Trailer INT NOT NULL, 
    ID_LoadingBay INT NOT NULL,
    ID_Storage INT NOT NULL,
    ID_DestinationAddress INT NOT NULL DEFAULT 4,
    DescriptiveID CHAR(18) NULL,
    Arrival DATETIME2(2) NOT NULL DEFAULT SYSDATETIME(), 
    Depature DATETIME2(2) NULL,

    CONSTRAINT FK_Haul_to_Trailer FOREIGN KEY (ID_Trailer) REFERENCES Trailer(ID), 
    CONSTRAINT FK_Haul_to_LoadingBay FOREIGN KEY (ID_LoadingBay) REFERENCES LoadingBay(ID),
    CONSTRAINT FK_Haul_to_Storage FOREIGN KEY (ID_Storage) REFERENCES Storage(ID),
    CONSTRAINT FK_Haul_to_DestinationAddress FOREIGN KEY (ID_DestinationAddress) REFERENCES [Address](ID)
)
GO

CREATE TABLE dbo.ChargeCarrier_Storage
(
    ID_ChargeCarrier INT NOT NULL,
    ID_Storage INT NOT NULL,
    ID_User INT NULL,
    StoredOn DATETIME2(2) NOT NULL DEFAULT GetDate(),

    CONSTRAINT PK_ChargeCarrier_Storage PRIMARY KEY (ID_ChargeCarrier, ID_Storage, StoredOn),
    CONSTRAINT FK_ChargeCarrier_Storage_to_Storage FOREIGN KEY (ID_Storage) REFERENCES dbo.Storage(ID), 
    CONSTRAINT FK_ChargeCarrier_Storage_to_ChargeCarrier FOREIGN KEY (ID_ChargeCarrier) REFERENCES dbo.ChargeCarrier(ID) ON DELETE CASCADE,
    CONSTRAINT FK_ChargeCarrier_Storage_to_User FOREIGN KEY (ID_User) REFERENCES dbo.[User](ID)
)
GO
As a possible answer, I've added a table value function to my view. But there might be much better solutions. I would be happy, if you could post your alternative ideas.

CREATE VIEW rpt.LoadingList WITH SCHEMABINDING AS
SELECT
    h.ID AS ID_Haul,
    h.DescriptiveID,
    ROW_NUMBER() OVER(PARTITION BY h.ID ORDER BY ccs.StoredOn ASC) AS Pos,
    cc.PickingNo,
    pin.PickingStorage,
    pin.TargetCountry,
    cc.[ObjID],
    cc.Customer,
    cc.Article,
    cc.Quantity,
    cc.QuantityUoM,
    cc.PackageLength,
    cc.PackageWidth,
    cc.PackageHeight,
    cc.PackageWeight,
    ccs.StoredOn,
    CAST(CASE WHEN h.Depature IS NULL THEN 0 ELSE 1 END AS BIT) AS Departured
FROM dbo.Haul h
CROSS APPLY dbo.GetAllChargeCarriersOnHaul (h.ID) ccs
INNER JOIN dbo.ChargeCarrier cc ON cc.ID = ccs.ID_ChargeCarrier
INNER JOIN dbo.PickingInfo pin ON pin.ID_ChargeCarrier = ccs.ID_ChargeCarrier

----------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[GetAllChargeCarriersOnHaul]
(
    @ID_Haul INT
)
RETURNS @returntable TABLE
(
    ID_ChargeCarrier INT NOT NULL,
    StoredOn DATETIME2(2) NOT NULL
)
WITH SCHEMABINDING
AS
BEGIN
    -- Declare local variables
    DECLARE @ID_Storage INT;
    DECLARE @Arrival DATETIME2(2);
    DECLARE @Depature DATETIME2(2);

    -- Load haul data
    SELECT
        @ID_Storage = ID_Storage,
        @Arrival = Arrival,
        @Depature = Depature
    FROM dbo.Haul
    WHERE ID = @ID_Haul;

    -- Exit, if no data was found
    IF @ID_Storage IS NULL
    BEGIN;
        RETURN;
    END;

    -- Select actual data
    INSERT INTO @returntable
    SELECT
        ID_ChargeCarrier,
        MAX(StoredOn)
    FROM dbo.ChargeCarrier_Storage
    WHERE
        ID_Storage = @ID_Storage
        AND (
            @Depature IS NULL AND StoredOn &gt;= @Depature OR
            StoredOn BETWEEN @Arrival AND @Depature
        )
        AND dbo.GetLatestStorageLocationInDateRange (ID_ChargeCarrier, @Arrival, @Depature) = @ID_Storage
    GROUP BY ID_ChargeCarrier;

    RETURN;
END;

----------------------------------------------------------------------------------

CREATE FUNCTION dbo.GetLatestStorageLocationInDateRange
(
    @ID_ChargeCarrier INT,
    @FromDate DATETIME2(2),
    @TillDate DATETIME2(2)
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN;
    RETURN (
        SELECT TOP 1 ID_Storage
        FROM dbo.ChargeCarrier_Storage
        WHERE
            ID_ChargeCarrier = @ID_ChargeCarrier
            AND (
                @TillDate IS NULL AND StoredOn &gt;= @FromDate OR
                StoredOn BETWEEN @FromDate AND @TillDate
            )
        ORDER BY StoredOn DESC
    );
END;