I am trying to write a query to return data from an archival table from multiple as of dates. I know how to return data for a single point in time:

我正在尝试编写一个查询，以从一个档案表中返回多个截止日期的数据。我知道如何返回单个时间点的数据：

declare @Date date = '2023-09-01'; 
declare @DateTime DATETIME = concat(@Date,' 10:30:00.000');

select 
    @Date,
    [Item],
    [Quantity]
from 
    Fruit for SYSTEM_TIME as of @DateTime;

I have the first day of the month in its own FirstDateMonth table that looks like this:

我在它自己的FirstDateMonth表中有每月的第一天，如下所示：

I want to write a query like the one above, but that loops through each date on the FirstDateMonth table and returns results similar to this:

我想要编写一个类似上面的查询，但是该查询遍历FirstDateMonth表上的每个日期并返回类似以下内容的结果：

Any suggestions on how to make this happen?

对于如何实现这一点，有什么建议吗？

This is from memory as I don't have anything to test with at the moment..

这是我的记忆，因为我目前没有任何东西可以用来测试。

The basic principle is to create a table valued function that returns data for a single point in time and the cross apply that to your start of month dates.

基本原则是创建一个表值函数，该函数返回单个时间点的数据，并将其应用于月份的开始日期。

It's something like

好像是这样的

CREATE FUNCTION getFruitAsOf (@AsOdDate datetime)
    RETURNS TABLE 
    AS 
        (SELECT * FROM dbo.Fruit FRO SYSTEM_TIME AS OF @AsOfDate)

Then cross apply this to your dates table

然后将此代码交叉应用到DATES表

SELECT 
    d.StartDateTime, 
    f.*
FROM myDatesTable d 
    CROSS APPLY getFruitAsOf(d.StartDateTime) f