英文:
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 ='2022-08-01'
declare @EOM date ='2022-09-01'
Second snapshot will be
declare @BOM date ='2022-09-01'
declare @EOM date ='2022-10-01'
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()) <= 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);
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 >= SM.BOM
-- AND D.your_data_date < 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论