0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

DECLARE @Scans TABLE (
    ScanID INT,
    ScanTime DATETIME
)

DECLARE @Responses TABLE (
    ScanID INT, 
    RespA VARCHAR(50),
    RespB VARCHAR(50)
)

INSERT INTO @Scans VALUES (1, '8:00')
INSERT INTO @Scans VALUES (2, '8:15')
INSERT INTO @Scans VALUES (3, '9:00')
INSERT INTO @Scans VALUES (4, '9:30')
INSERT INTO @Scans VALUES (6, '10:00')
INSERT INTO @Scans VALUES (7, '10:45')
INSERT INTO @Scans VALUES (8, '11:00')
INSERT INTO @Scans VALUES (9, '11:10')

INSERT INTO @Responses VALUES (3, 'ABC', 'X')
INSERT INTO @Responses VALUES (7, 'DEF', 'Y')
INSERT INTO @Responses VALUES (9, 'GHI', 'Z')

;WITH ResponsesScan AS
(
    SELECT
        r.*, s.ScanTime
    FROM
        @Responses r
        JOIN @Scans s ON s.ScanID = r.ScanID
)
SELECT
    s.ScanID,
    s.ScanTime,
    rs.ScanID AS RespScanId,
    rs.RespA,
    rs.RespB
FROM
    @Scans s 
    LEFT JOIN ResponsesScan rs
        ON rs.ScanID = (
            SELECT TOP 1 ScanID
            FROM ResponsesScan
            WHERE ScanTime <= s.ScanTime
            ORDER BY ScanTime DESC
        )

fork query

created nov 18 11 jon.doe6827