Sqlserver SQL join on date(no validto fields)

Question

The priceQuote table, give the category and the price at a date range, however the valid to date is always a very large date.

The other table is Item table, which gives the itemID and its category with a date

The idea is to join these two tables based on its category and the date, for example, ITEMID 0503848220, category 1117, date 2020-07-02 should return $730(amount field).

Below is my attempt, of course it does not work as I wanted it to work

SELECT
Item.ItemID
,Item.Date
,ValidFROM
,ValidTo
,priceQuote.AMOUNT
From
Item
JOIN
priceQuote
on
Item.category=priceQuote.Category
and
Item.Date between ValidFROM and ValidTo

I wonder what is the best way of doing this (considering these two tables are very large and it may have performance issues joining with date range). If there is no good way of doing the join in SQL, I am open to other solutions such as loading these two tables into C# application and do the calculation through C#.

Thanks for your help.

Example of the data( not sure how to attach Excel files here):

+----------+-----------+------------+--------+
| Category | ValidFROM |  ValidTo   | AMOUNT |
+----------+-----------+------------+--------+
|     1113 | 4/1/2020  | 12/31/9999 |    665 |
|     1113 | 7/1/2020  | 12/31/9999 |    925 |
|     1113 | 5/10/2021 | 12/31/9999 |    491 |
|     1117 | 4/1/2020  | 12/31/9999 |    495 |
|     1117 | 7/1/2020  | 12/31/9999 |    730 |
|     1117 | 1/1/2021  | 12/31/9999 |    556 |
|     1117 | 5/10/2021 | 12/31/9999 |    555 |
+----------+-----------+------------+--------+

Item Table:

+-----------+----------+-----------+
|  ItemID   | category |   Date    |
+-----------+----------+-----------+
| 503848220 |     1117 | 7/2/2020  |
| 503848221 |     1117 | 7/2/2020  |
| 503848225 |     1117 | 7/3/2020  |
| 503848227 |     1117 | 7/6/2020  |
| 503848228 |     1117 | 7/6/2020  |
| 503848266 |     1113 | 6/26/2020 |
+-----------+----------+-----------+

Shouldn't ValidTo be the next ValidFrom - 1 day? These data are inconclusive. — Gert Arnold, Aug 16 '21 at 19:56
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) You want either a row-number solution or an `APPLY` — Charlieface, Aug 16 '21 at 23:08
`ValidTo` in the first table seems pretty useless. Do you have the option to update this column to a useful value (i.e. a day before the next `ValidFrom`) — Nick.Mc, Aug 16 '21 at 23:28
@Nick.McDermaid Thanks. I agree. It is completely useless. I cannot modify that table though. — Heisenberg, Aug 17 '21 at 03:35
@Charlieface Thanks. But my question here does not necessary mean the ITEM uses the latest date on the pricequote table. ITEM can be any date. — Heisenberg, Aug 17 '21 at 04:03
It's the same principle: you take the earliest or latest row that is still within the validity — Charlieface, Aug 17 '21 at 04:28

Gert Arnold · Accepted Answer · 2021-08-29T11:48:31.867

As said in the comments, ValidTo column would be meaningful had it contained a date equal to the next ValidFROM date minus 1 day.

Fortunately, it's relatively simple in a Sql Server query to generate the required column on the fly by using window functions. In this case, LEAD.

I use slightly different data to show how it works. I removed the useless ValidFROM column altogether to prove that it isn't used.

DECLARE @PriceQuote table (
    Category int,
    ValidFrom date,
    Amount int
);

DECLARE @Item table (
    ItemID char(10) primary key,
    Category int,
    Date date
);

INSERT INTO @Item (ItemID, Category, Date) VALUES
    ('503848266', 1113, '6/26/2020'),

    ('503848220', 1117, '3/01/2020'),
    ('503848221', 1117, '4/01/2020'),
    ('503848225', 1117, '7/01/2020'),
    ('503848227', 1117, '8/01/2020'),
    ('503848228', 1117, '4/10/2021'),
    ('503848229', 1117, '5/10/2021');


INSERT INTO @PriceQuote (Category, ValidFrom, Amount) VALUES
    (1113, '4/01/2020', 665 ),
    (1113, '7/01/2020', 925 ),
    (1113, '5/10/2021', 491 ),

    (1117, '4/01/2020', 495 ),
    (1117, '7/01/2020', 730 ),
    (1117, '1/01/2021', 556 ),
    (1117, '5/10/2021', 555 );

As a first step, to clarify the process, for each PriceQuote we get the next ValidFrom date by using LEAD:

SELECT Category,
    ValidFrom,
    LEAD(ValidFrom, 1) -- or just LEAD(ValidFrom)
        OVER (PARTITION BY (Category) ORDER BY ValidFrom) As NextValidFrom,
    Amount
FROM @PriceQuote

Result:

Category	ValidFrom	NextValidFrom	Amount
1113	2020-04-01	2020-07-01	665
1113	2020-07-01	2021-05-10	925
1113	2021-05-10	NULL	491
1117	2020-04-01	2020-07-01	495
1117	2020-07-01	2021-01-01	730
1117	2021-01-01	2021-05-10	556
1117	2021-05-10	NULL	555

So within one category (PARTITION BY), ordered by ValidFrom, the next ValidFrom is taken as NextValidFrom, if available.

This NextValidFrom, plus one day and set to a large date value when null, can be used in a subquery you can join with to find the quote amounts:

SELECT item.ItemID, item.Category, quote.ValidFrom, item.Date, quote.ValidTo, quote.Amount
FROM @Item item
INNER JOIN
(
    SELECT Category,
        ValidFrom, 
        Amount,
        COALESCE(DATEADD(d, -1, LEAD(ValidFrom, 1) 
            OVER (PARTITION BY (Category) 
                ORDER BY ValidFrom)), '12/31/9999') [ValidTo]
    FROM @PriceQuote
) AS quote
ON item.Category = quote.Category
AND item.Date BETWEEN quote.ValidFrom AND quote.ValidTo

Which returns the data you're after:

ItemID	Category	ValidFrom	Date	ValidTo	Amount
503848266	1113	2020-04-01	2020-06-26	2020-06-30	665
503848221	1117	2020-04-01	2020-04-01	2020-06-30	495
503848225	1117	2020-07-01	2020-07-01	2020-12-31	730
503848227	1117	2020-07-01	2020-08-01	2020-12-31	730
503848228	1117	2021-01-01	2021-04-10	2021-05-09	556
503848229	1117	2021-05-10	2021-05-10	9999-12-31	555

Item 503848220 doesn't show up in the results because it's not in a quote range.

score 1 · Answer 2 · answered Aug 16 '21 at 20:04

The date management is less than desirable here and, given your existing data structure, I question whether there is a surefire way to accomplish your task, but below is my attempt.

I have made the assumptions you will be filtering based on both the item date and item id.

/* MOCK TABLES AND DATA */

DECLARE @Quote table (
    Category int, ValidFrom date, ValidTo date, Amount decimal(18,2)
);

DECLARE @Item table (
    ItemID varchar(10), Category int, [Date] date
);

INSERT INTO @Item ( ItemID, Category, [Date] ) VALUES
    ( '503848220', 1117, '7/2/2020' ),
    ( '503848221', 1117, '7/2/2020' ),
    ( '503848225', 1117, '7/3/2020' ),
    ( '503848227', 1117, '7/6/2020' ),
    ( '503848228', 1117, '7/6/2020' ),
    ( '503848266', 1113, '6/26/2020' );

INSERT INTO @Quote ( Category, ValidFrom, ValidTo, Amount ) VALUES
    ( 1113, '4/1/2020 ', '12/31/9999', 665 ),
    ( 1113, '7/1/2020 ', '12/31/9999', 925 ),
    ( 1113, '5/10/2021', '12/31/9999', 491 ),
    ( 1117, '4/1/2020 ', '12/31/9999', 495 ),
    ( 1117, '7/1/2020 ', '12/31/9999', 730 ),
    ( 1117, '1/1/2021 ', '12/31/9999', 556 ),
    ( 1117, '5/10/2021', '12/31/9999', 555 );

/*
    Return matching row based on an item's id and date
*/

SELECT
    *
FROM @Item AS i
LEFT JOIN @Quote AS q
    ON i.Category = q.Category
    AND MONTH( q.ValidFrom ) >= MONTH ( i.[Date] )
    AND YEAR ( i.[Date] ) <= YEAR ( q.ValidTo )
WHERE
    i.ItemID = '503848220'
    AND i.[Date] = '07/02/2020'
ORDER BY
    i.Category, i.[Date], q.ValidFrom, q.ValidTo;

Returns

+-----------+----------+------------+----------+------------+------------+--------+
|  ItemID   | Category |    Date    | Category | ValidFrom  |  ValidTo   | Amount |
+-----------+----------+------------+----------+------------+------------+--------+
| 503848220 |     1117 | 2020-07-02 |     1117 | 2020-07-01 | 9999-12-31 | 730.00 |
+-----------+----------+------------+----------+------------+------------+--------+

If you are looking to restrict your data to a specific month you can change

AND MONTH( q.ValidFrom ) >= MONTH ( i.[Date] )

to

AND MONTH( q.ValidFrom ) = MONTH ( i.[Date] )

Sqlserver SQL join on date(no validto fields)

2 Answers2