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.
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.