如何创建过去6个月的每月快照?

huangapple go评论61阅读模式
英文:

How to create monthly snapshots for the last 6 months?

问题

我试图在过去的6个月中,每个月的Business Day=1获取详细数据(快照),并需要通过两个日期变量传递6个不同的日期(仅BD1)。

两个变量将分别是BOM,它将是过去6个月的BD1,和EOM,它将是BD1+1。

例如:

第一个快照将是:

declare @BOM date = '2022-08-01'
declare @EOM date = '2022-09-01'

第二个快照将是:

declare @BOM date = '2022-09-01'
declare @EOM date = '2022-10-01'

依此类推,持续6个月从当前月开始。

以下是我尝试的内容:

declare @BOM date  
set @BOM = 
(
select top 6 cast(date_datetime as date) date_datetime
from date_dim 
where
datediff(month, date_datetime, getdate()) <= 6
and bd=1 
order by date_datetime asc
);

declare @EOM date  
set @EOM = 
(
select top 6 date_datetime
from date_dim 
where
datediff(month, date_datetime, getdate()) <= 5
and bd=1 
order by date_datetime asc
);

但我的查询不能处理它,因为我在我的主查询的WHERE子句中通过BOM和EOM变量传递了多个值。我需要一些关于在查询中定义和使用这些变量的帮助,以便它们可以获取不同的快照并将其存储在表中。

英文:

I'm trying to get detailed data (snapshot) for each month on Business Day=1 for the last 6 months and need to pass 6 different dates (BD1's only) through two date variables.
Two variables will be BOM which will be BD1 for the last 6 months and EOM which will be BD1+1.

For e.g
First snapshot will be

declare @BOM  date =&#39;2022-08-01&#39;
declare @EOM  date =&#39;2022-09-01&#39;

Second snapshot will be

declare @BOM  date =&#39;2022-09-01&#39;
declare @EOM  date =&#39;2022-10-01&#39;

and so on for the last 6 months from the current month

Here is what I'm trying to do:

declare @BOM date  
set @BOM= 
(
select top 6 cast(date_datetime as date) date_datetime
from date_dim 
where
datediff(month, date_datetime, getdate()) &lt;= 6
and bd=1 
order by date_datetime asc);

declare @EOM date  
set @EOM= 
(
select top 6 date_datetime
from date_dim 
where
datediff(month, date_datetime, getdate()) &lt;= 5
and bd=1 
order by date_datetime asc);

But my query does not process it as I'm passing more than 1 value through my BOM & EOM variables in my main query WHERE clause.
I need some help with defining and using these variables in my query so that they can take different snapshots and store it in a table.

答案1

得分: 1

以下是翻译的内容:

正如您所发现的,您不能在标量变量中存储多个值。您可能需要使用一个表变量(类似于临时表)来存储这些值。表变量可以包含多行(每个选定的月份一行)和多列(BOM和EOM)。

以下代码定义了这样一个表变量,并使用来自date_dim表的最近6个完整月的BOM和EOM填充它。我使用了LEAD()窗口函数来选择每个BOM对应的EOM。

由于没有提供实际查询所需的示例数据,我在最后添加了一个简单的查询,只列出了选定的日期范围和每个日期范围内的工作日数。

-- 表变量用于保存选定月份信息
DECLARE @selected_months TABLE (BOM DATE, EOM DATE)

-- 选择最近的6个完整月
INSERT @selected_months
SELECT *
FROM (
SELECT
date_datetime AS BOM,
LEAD(date_datetime) OVER(ORDER BY date_datetime) AS EOM
FROM date_dim
) D
WHERE DATEDIFF(month, BOM, GETDATE()) BETWEEN 1 AND 6
ORDER BY BOM

-- 示例用法
SELECT M.*, DATEDIFF(day, M.BOM, M.EOM) business_days
FROM @selected_months M
-- 连接您的数据 D
-- ON D.your_data_date >= SM.BOM
-- AND D.your_data_date < SM.EOM
GROUP BY M.BOM, M.EOM
ORDER BY M.BOM

示例结果:

BOM EOM 工作日数
2022-08-01 2022-09-05 35
2022-09-05 2022-10-03 28
2022-10-03 2022-11-07 35
2022-11-07 2022-12-05 28
2022-12-05 2023-01-02 28
2023-01-02 2023-02-06 35

此db<>fiddle链接中查看演示。

英文:

As you discovered, you cannot store multiple values in a scalar variable. What you possibly need is to use a table variable (which behaves similarly to a temp table). The table variable can have multiple rows (one for each selected month) and multiple columns (BOM and EOM).

The following code defines such a table variable and populates it with BOM and EOM of the most recent 6 full months from the date_dim table. I used the LEAD() window function to select the corresponding EOM for each BOM.

Lacking any provided sample data to actually query, I added a simple query at the end to just list the selected date ranges and calculated number of business days in each.

-- Table variable to hold selected month information
DECLARE @selected_months TABLE (BOM DATE, EOM DATE)

-- Select last 6 full months 
INSERT @selected_months
SELECT *
FROM (
    SELECT 
        date_datetime AS BOM,
        LEAD(date_datetime) OVER(ORDER BY date_datetime) AS EOM
    FROM date_dim
) D
WHERE DATEDIFF(month, BOM, GETDATE()) BETWEEN 1 AND 6
ORDER BY BOM

-- Sample usage
SELECT M.*, DATEDIFF(day, M.BOM, M.EOM) business_days
FROM @selected_months M
-- JOIN your_data D
--     ON D.your_data_date &gt;= SM.BOM
--     AND D.your_data_date &lt; SM.EOM
GROUP BY M.BOM, M.EOM 
ORDER BY M.BOM

Sample results:

BOM EOM business_days
2022-08-01 2022-09-05 35
2022-09-05 2022-10-03 28
2022-10-03 2022-11-07 35
2022-11-07 2022-12-05 28
2022-12-05 2023-01-02 28
2023-01-02 2023-02-06 35

See this db<>fiddle for a working demo.

huangapple
  • 本文由 发表于 2023年2月10日 07:24:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/75405500.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定