How to join matching Date fields as intervals?

Question

Let say I've a table with a list of id/year/month (two years + 24 months for semplicity here):

Code            Year    Month
MAT000000001    2022    1
MAT000000001    2022    2
MAT000000001    2022    3
MAT000000001    2022    4
MAT000000001    2022    5
MAT000000001    2022    6
MAT000000001    2022    7
MAT000000001    2022    8
MAT000000001    2022    9
MAT000000001    2022    10
MAT000000001    2022    11
MAT000000001    2022    12
MAT000000001    2023    1
MAT000000001    2023    2
MAT000000001    2023    3
MAT000000001    2023    4
MAT000000001    2023    5
MAT000000001    2023    6
MAT000000001    2023    7
MAT000000001    2023    8
MAT000000001    2023    9
MAT000000001    2023    10
MAT000000001    2023    11
MAT000000001    2023    12
MAT000000002    2022    1
MAT000000002    2022    2
MAT000000002    2022    3
MAT000000002    2022    4

Also, I've this kind of History table:

CREATE TABLE [dbo].History(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Code] [varchar](12) NOT NULL,
    [DateStart] [datetime] NOT NULL,
    [DateEnd] [datetime] NOT NULL,
 CONSTRAINT [PK_ReferentiStorico] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

With data such as:

Id  Code            DateStart   DateEnd
1   MAT000000001    1990-01-01  2023-03-31
2   MAT000000002    1990-01-01  9999-12-31
3   MAT000000001    2023-04-01  9999-12-31

Now, what I need to do is join the first table with the History, with the same Code and (and this is the heavy part) year month within the period described with DateStart and DateEnd.

In the example above, this must be the result:

Code            Year    Month   HistoryId
MAT000000001    2022    1       1
MAT000000001    2022    2       1
MAT000000001    2022    3       1
MAT000000001    2022    4       1
MAT000000001    2022    5       1
MAT000000001    2022    6       1
MAT000000001    2022    7       1
MAT000000001    2022    8       1
MAT000000001    2022    9       1
MAT000000001    2022    10      1
MAT000000001    2022    11      1
MAT000000001    2022    12      1
MAT000000001    2023    1       1
MAT000000001    2023    2       1
MAT000000001    2023    3       1
MAT000000001    2023    4       3
MAT000000001    2023    5       3
MAT000000001    2023    6       3
MAT000000001    2023    7       3
MAT000000001    2023    8       3
MAT000000001    2023    9       3
MAT000000001    2023    10      3
MAT000000001    2023    11      3
MAT000000001    2023    12      3
MAT000000002    2022    1       2
MAT000000002    2022    2       2
MAT000000002    2022    3       2
MAT000000002    2022    4       2

Which the correct way to do this with SQL Server?

Not sure about the performance of this (i.e. create Date for every row):

inner join History as hist on ana.Code = hist.Code and datefromparts(initTable.Year, initTable.Month, 1) between hist.DateStart and hist.DateEnd

Answer 1

So I would say your approach is fine. Logically it makes sense and runs fine for me. I set up your situation this way -

CREATE TABLE #table1
(
    code VARCHAR(12)
  , Year INT
  , month INT
);

CREATE TABLE #History
(
    Id BIGINT IDENTITY(1, 1) NOT NULL
  , Code VARCHAR(12) NOT NULL
  , DateStart DATETIME NOT NULL
  , DateEnd DATETIME NOT NULL
  , CONSTRAINT PK_ReferentiStorico
        PRIMARY KEY CLUSTERED (Id ASC)
);

WITH x
AS
(
    SELECT 1 AS m
    UNION ALL
    SELECT x.m + 1 AS m
    FROM x
    WHERE m < 12
)
   , y
AS
(
    SELECT 2022 AS y
         , x.m AS m
    FROM x
    UNION ALL
    SELECT y.y + 1 AS y
         , y.m
    FROM y
    WHERE y < 2023
)
   , z
AS
(
    SELECT TOP 4
           y.y
         , y.m
    FROM y
)
INSERT INTO #table1
(
    Year
  , month
)
SELECT y.y
     , y.m
FROM y
UNION ALL
SELECT *
FROM z
ORDER BY y.y
       , y.m;

UPDATE #table1
SET code = 'MAT000000001';

WITH up
AS
(
    SELECT t.Year
         , t.month
         , t.code
         , ROW_NUMBER() OVER (PARTITION BY t.Year, t.month ORDER BY t.Year, t.month) AS rn
    FROM #table1 AS t
)
UPDATE up
SET up.code = 'MAT000000002'
WHERE up.rn = 2;

INSERT INTO #History
(
    Code
  , DateStart
  , DateEnd
)
VALUES
('MAT000000001', '1990-01-01', '2023-03-31')
, ('MAT000000002', '1990-01-01', '9999-12-31')
, ('MAT000000001', '2023-04-01', '9999-12-31');

That gave me the values in the tables you suggested. I would run the query below -

SELECT t.code
     , t.Year
     , t.month
     , h.Id
FROM #table1 AS t
    INNER JOIN #History AS h
        ON t.code = h.Code
WHERE DATEFROMPARTS(t.Year, t.month, 1)
BETWEEN h.DateStart AND h.DateEnd
ORDER BY t.code
       , t.Year
       , t.month;

The only thing I would do for performance is to add a nonclustered index on the base table around the code column since I assume you already have a clustered index and pk, as well as one on the history table.

CREATE NONCLUSTERED INDEX idx ON #table1 (code) INCLUDE (Year, month);
GO
CREATE NONCLUSTERED INDEX idx
ON #History (Code)
INCLUDE (
            DateStart
          , DateEnd
        );
GO

---