gpt4 book ai didi

SQL Query for Archival Table with Multiple As of Dates(多个截止日期档案表的SQL查询)

转载 作者:bug小助手 更新时间:2023-10-24 22:20:12 26 4
gpt4 key购买 nike



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表中有每月的第一天,如下所示:
















































Date DateTimeUTC
2023-01-01 2023-01-01 10:30:00.000
2023-02-01 2023-02-01 10:30:00.000
2023-03-01 2023-03-01 10:30:00.000
2023-04-01 2023-04-01 10:30:00.000
2023-05-01 2023-05-01 10:30:00.000
2023-06-01 2023-06-01 10:30:00.000
2023-07-01 2023-07-01 10:30:00.000
2023-08-01 2023-08-01 10:30:00.000
2023-09-01 2023-09-01 10:30:00.000


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表上的每个日期并返回类似以下内容的结果:




































































Date Item Quantity
2023-01-01 Apple 2
2023-01-01 Banana 4
2023-02-01 Apple 2
2023-02-01 Banana 5
2023-03-01 Apple 3
2023-03-01 Banana 1
2023-04-01 Apple 8
2023-04-01 Banana 1
2023-05-01 Apple 4
2023-05-01 Banana 3
etc...


Any suggestions on how to make this happen?

对于如何实现这一点,有什么建议吗?


更多回答

Do you mean temporal tables? learn.microsoft.com/en-us/sql/relational-databases/tables/… (you probably want contained in or all from that manual page, then a join on your dates)

你是说时态表吗?Learn.microsoft.com/en-us/sql/relational-databases/tables/…(您可能希望包含在手册页面中或全部包含在该手册页面中,然后加入您的日期)

优秀答案推荐

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

更多回答

Thank you, this is exactly what I needed!

谢谢,这正是我需要的!

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com