TSQL - Get value between dates in data warehouse dimension

Question

I have the beginnngs of a data warehouse, that contains (among other tables)

• date table with 200 years worth of dates to join with, so no need to build a "dynamic" date table
• dbo.Dim_Items (see below)

ItemNumber	QOH	EffectiveDate	ExpirationDate	IsCurrentRecord
372620	24	2021-12-11 05:34:09.000	9999-12-31 00:00:00.000	1
372620	11	2021-12-09 05:34:11.000	2021-12-11 05:34:09.000	0
372620	9	2021-12-07 05:34:20.000	2021-12-09 05:34:11.000	0

I would like to find the QOH for each day between 2021-12-07 and today (assume 2021-12-13, for brevity), so that my result looks like this

Date	ItemNumber	QOH
2021-12-07	372620	9
2021-12-08	372620	9
2021-12-09	372620	11
2021-12-10	372620	11
2021-12-11	372620	24
2021-12-12	372620	24
2021-12-13	372620	24

The closest I have come is the query below, but it is not working the way I want it to (note the zeros on dates that don't line up with the Items dimension)

DECLARE @START_DATE date,
        @END_DATE date

SET @START_DATE = '20211207'
SET @END_DATE = GETDATE()   -- '20211213'

SELECT CAL.TheDate,
        I.ItemNumber,
        I.QOH
FROM dbo.Dim_Calendar CAL
    LEFT OUTER JOIN dbo.Dim_Items I ON CAL.TheDate >= CAST(I.EffectiveDate as date)
        AND CAL.TheDate <= CAST(I.EffectiveDate as date)
        AND I.ItemNumber = 372620
WHERE CAL.TheDate >= @START_DATE
    AND CAL.TheDate <= @END_DATE
ORDER BY CAL.TheDate,
            I.ItemNumber
        
        

Date	ItemNumber	QOH
2021-12-07	372620	9
2021-12-08	372620	0
2021-12-09	372620	11
2021-12-10	372620	0
2021-12-11	372620	24
2021-12-12	372620	0
2021-12-13	372620	0

I am new to data warehousing as a whole, so I am not sure how to build this query correctly.

Answer 1

DBFIDDLE

DECLARE @START_DATE date,
        @END_DATE date

SET @START_DATE = '20211207'
SET @END_DATE = GETDATE()   -- '20211213'

SELECT
   TheDate,
   I.Itemnumber,
   I.QOH
FROM dbo.Dim_Calendar CAL
LEFT JOIN dbo.Dim_Items I On CAL.TheDate BETWEEN CAST(I.EffectiveDate as DATE) 
                                             AND I.ExpirationDate
                         AND I.Itemnumber = 372620
WHERE CAL.TheDate >= @START_DATE
    AND CAL.TheDate <= @END_DATE
ORDER BY CAL.TheDate,
            I.ItemNumber