How to get record based on nearest date in SQL

Question

How to get record based on nearest date in SQL

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

kaj SSCommitted Points: 1605 More actions · Answer 1

Okay, I'll give it a shot...

DECLARE
    @tableA TABLE (
        Id          INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,
        CustNo      INT         NOT NULL,
        PurDate     DATETIME    NOT NULL
        )
DECLARE
    @tableB TABLE (
        Id          INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,
        CustNo      INT         NOT NULL,
        ScoreDate   DATETIME    NOT NULL,
        Amount      DEC(13,2)   NOT NULL
        )
DECLARE
    @tableC TABLE (
        Id          INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,
        CustNo      INT         NOT NULL,
        PurDate     DATETIME    NOT NULL,
        ScoreDate   DATETIME    NOT NULL,
        Amount      DEC(13,2)   NOT NULL
        )

INSERT INTO @tableA (CustNo, PurDate)
    VALUES
        (1, '2020-10-12 12:45:21.136'),
        (2, '2018-09-18 17:32:16.145'),
        (2, '2017-01-01 06:16:29.147'),
        (3, '2019-09-27 16:32:45.752'),
        (3, '2021-02-18 12:47:56.547')

INSERT INTO @tableB (CustNo, ScoreDate, Amount)
    VALUES
        (1, '2020-11-28 16:47:19.544', 10000),
        (1, '2020-01-05 06:47:14.652', 12000),
        (2, '2020-03-08 13:19:02.715', 6000),
        (2, '2018-12-01 19:48:32.624', 8000),
        (3, '2019-10-28 09:17:58.002', 14000),
        (3, '2019-02-18 15:19:38.458', 16000);

WITH
    CustLatestPur AS (
        SELECT
            a.CustNo,
            MAX(a.PurDate) AS PurDate
        FROM
            @tableA AS a
        GROUP BY
            a.CustNo
        )
INSERT INTO @tableC (CustNo, PurDate, ScoreDate, Amount)
    SELECT
        clp.CustNo,
        clp.PurDate,
        ba.ScoreDate,
        ba.Amount
    FROM
        CustLatestPur AS clp
    CROSS APPLY (
        SELECT TOP(1)
            b.ScoreDate,
            b.Amount
        FROM
            @tableB AS b
        WHERE
            b.CustNo = clp.CustNo
        ORDER BY
            ABS(DATEDIFF(DAY,CAST(clp.PurDate AS DATE), CAST(b.ScoreDate AS DATE))) ASC,
            ABS(DATEDIFF(MILLISECOND, CAST(clp.PurDate AS TIME(3)), CAST(b.ScoreDate AS TIME(3)))) ASC,
            b.ScoreDate DESC
        ) AS ba

SELECT * FROM @tableC

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

In a set-oriented language you return the whole set of values that are temporally equal toMight wan the min(date diff) over(patition by account) .

Look up Dr. Codd's T-Join for something like this. I did an article on this https://www.sqlservercentral.com/articles/celkos-sql-stumper-the-class-scheduling-problem

Please post DDL and follow ANSI/ISO standards when asking for help.

kaj SSCommitted Points: 1605 More actions · Answer 3

jcelko212 32090 wrote:

In a set-oriented language you return the whole set of values that are temporally equal toMight wan the min(date diff) over(patition by account) .
Look up Dr. Codd's T-Join for something like this. I did an article on this https://www.sqlservercentral.com/articles/celkos-sql-stumper-the-class-scheduling-problem

Hmmm... though interesting, I'm not sure how this relates to the task set before us by the OP. Might just be me who isn't bright enough to see it though. 🙂

This reply was modified 2 years, 1 month ago by kaj.