August 13, 2021 at 11:04 am
Hi,
I have the following code which will give me a list of every fortnight between two dates however I want to be able to make the type part of datadd (so WEEK, MONTH, YEAR) etc dynamic. Is this possible? I have tried creating a user defined variable for it but it didn't work.
Basically if the rental period is weekly, this datepart would be WEEK, if its monthly it would be MONTH etc
DECLARE @OFFSET int
SET @OFFSET = 2
DECLARE @PERIOD VARCHAR
declare @startdate date
set @startdate = '2020-08-13'
declare @enddate date
set @enddate = '2021-12-31'
declare @rentalperiod int
set @rentalperiod = 2
--RENTAL_PERIOD_ID
--1Weekly
--2Fortnightly
--34 Weekly
--4Monthly
--5Yearly
--6Once Off
SET @PERIOD =
CASE @rentalperiod
WHEN 1 THEN 'WEEK'
WHEN 2 THEN 'WEEK'
WHEN 3 THEN 'WEEK'
WHEN 4 THEN 'MONTH'
WHEN 5 THEN 'YEAR'
END
SET @OFFSET =
CASE @rentalperiod
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 4
WHEN 4 THEN 1
WHEN 5 THEN 1
END
;WITH WeeklyCTE AS
(
SELECT MIN(@startdate) AS weekstart_date
FROM COMMON_DATA.DATE_TABLE
UNION ALL
SELECT DATEADD(WEEK, @OFFSET, weekstart_date)
FROM WeeklyCTE
WHERE DATEADD(WEEK, @OFFSET, weekstart_date) >= @startdate AND DATEADD(WEEK, @OFFSET, weekstart_date) <=@enddate
)
SELECT w.weekstart_date
FROM WeeklyCTE w
LEFT JOIN COMMON_DATA.DATE_TABLE t ON w.weekstart_date = t.calendar_date
option (maxrecursion 0)
August 13, 2021 at 4:29 pm
Pretty sure for this to work you are either going to need to have your stored procedure call a second stored procedure (likely the approach I would go with) OR you are going to need to use dynamic SQL (which is a bit more risky) OR you will have a more complex query.
You could have a bunch of UNIONs and have a WHERE clause on each UNION that handles each specific use case. What I mean is have "WHERE DATEADD(WEEK" like you do now, but also add in "AND @rentalPeriod = 'WEEK'" and then repeat for MONTH and YEAR.
Or, yet another option, you could calculate out the number of days it would be for each of the possible values and use a DATEADD using days instead of weeks, months, or years.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 14, 2021 at 3:25 pm
Thanks for your reply.
I played around with it and managed to get it working by using the following. Just added some IF clauses to it and set my offset before it
DECLARE @OFFSET int
SET @OFFSET = 2
DECLARE @PERIOD VARCHAR
declare @startdate date
set @startdate = '2020-08-13'
declare @enddate date
set @enddate = '2021-12-31'
declare @RENTALPERIOD int
set @RENTALPERIOD = 5
--RENTAL_PERIOD_ID
--1Weekly
--2Fortnightly
--34 Weekly
--4Monthly
--5Yearly
--6Once Off
SET @PERIOD =
CASE @rentalperiod
WHEN 1 THEN 'WEEK'
WHEN 2 THEN 'WEEK'
WHEN 3 THEN 'WEEK'
WHEN 4 THEN 'MONTH'
WHEN 5 THEN 'YEAR'
END
SET @OFFSET =
CASE @RENTALPERIOD
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 4
WHEN 4 THEN 1
WHEN 5 THEN 1
END
IF @RENTALPERIOD = 1 OR @RENTALPERIOD = 2 OR @RENTALPERIOD = 3
WITH WeeklyCTE AS
(
SELECT MIN(@startdate) AS weekstart_date
FROM COMMON_DATA.DATE_TABLE
UNION ALL
SELECT DATEADD(WEEK, @OFFSET, weekstart_date)
FROM WeeklyCTE
WHERE DATEADD(WEEK, @OFFSET, weekstart_date) >= @startdate AND DATEADD(WEEK, @OFFSET, weekstart_date) <=@enddate
)
SELECT w.weekstart_date
FROM WeeklyCTE w
LEFT JOIN COMMON_DATA.DATE_TABLE t ON w.weekstart_date = t.calendar_date
option (maxrecursion 0)
ELSE
IF @RENTALPERIOD = 4
WITH WeeklyCTE AS
(
SELECT MIN(@startdate) AS weekstart_date
FROM COMMON_DATA.DATE_TABLE
UNION ALL
SELECT DATEADD(MONTH, @OFFSET, weekstart_date)
FROM WeeklyCTE
WHERE DATEADD(MONTH, @OFFSET, weekstart_date) >= @startdate AND DATEADD(MONTH, @OFFSET, weekstart_date) <=@enddate
)
SELECT w.weekstart_date
FROM WeeklyCTE w
LEFT JOIN COMMON_DATA.DATE_TABLE t ON w.weekstart_date = t.calendar_date
option (maxrecursion 0)
ELSE
IF @RENTALPERIOD = 5
WITH WeeklyCTE AS
(
SELECT MIN(@startdate) AS weekstart_date
FROM COMMON_DATA.DATE_TABLE
UNION ALL
SELECT DATEADD(YEAR, @OFFSET, weekstart_date)
FROM WeeklyCTE
WHERE DATEADD(YEAR, @OFFSET, weekstart_date) >= @startdate AND DATEADD(YEAR, @OFFSET, weekstart_date) <=@enddate
)
SELECT w.weekstart_date
FROM WeeklyCTE w
LEFT JOIN COMMON_DATA.DATE_TABLE t ON w.weekstart_date = t.calendar_date
option (maxrecursion 0)
August 15, 2021 at 3:47 pm
Could you post the CREATE TABLE statement for your date table? I'd like to try a couple of things.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2021 at 1:24 am
Could you post the CREATE TABLE statement for your date table? I'd like to try a couple of things.
To start with maybe the OP could use a rental periods table. Something like this
drop table if exists dbo.rental_periods;
go
create table dbo.rental_periods(
rp_id int identity(1,1) constraint pk_rental_periods primary key not null,
rental_period nvarchar(40) unique not null,
time_units nvarchar(20) not null,
time_periods int not null,
constraint unq_rp_units_periods unique(time_units, time_periods));
insert dbo.rental_periods(rental_period, time_units, time_periods) values
('1 Week', 'Week', 1),
('Fortnightly', 'Week', 2),
('3 Weekly', 'Week', 3),
('Monthly', 'Month', 1),
('Yearly', 'Year', 1);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 16, 2021 at 1:33 am
Here is the create table script for the date table
USE [proppaty_config]
GO
/****** Object: StoredProcedure [COMMON_DATA].[POPULATE_DATE_TABLE] Script Date: 16/08/2021 11:32:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [COMMON_DATA].[POPULATE_DATE_TABLE]
@START_DATE DATE = N'2020-01-01T08:00:00',
@END_DATE DATE = N'2030-12-31T08:00:00'
AS
BEGIN
SET NOCOUNT ON;
IF @START_DATE IS NULL OR @END_DATE IS NULL
BEGIN
SELECT '
Start and end dates MUST be provided in order for this stored procedure to work.';
RETURN;
END
IF @START_DATE > @END_DATE
BEGIN
SELECT 'Start date must be less than or equal to the end date.';
RETURN;
END
TRUNCATE TABLE COMMON_DATA.DATE_TABLE
--WHERE DATE_TABLE.CALENDAR_DATE BETWEEN @START_DATE AND @END_DATE;
DECLARE @DATE_COUNTER DATE = @START_DATE;
DECLARE @CALENDAR_DATE_STRING VARCHAR(10);
DECLARE @END_DATE_STRING VARCHAR(10);
DECLARE @CALENDAR_MONTH TINYINT;
DECLARE @CALENDAR_DAY TINYINT;
DECLARE @CALENDAR_YEAR SMALLINT;
DECLARE @CALENDAR_QUARTER TINYINT;
DECLARE @DAY_NAME VARCHAR(9);
DECLARE @DAY_OF_WEEK TINYINT;
DECLARE @DAY_OF_WEEK_IN_MONTH TINYINT;
DECLARE @DAY_OF_WEEK_IN_YEAR TINYINT;
DECLARE @DAY_OF_WEEK_IN_QUARTER TINYINT;
DECLARE @DAY_OF_QUARTER TINYINT;
DECLARE @DAY_OF_YEAR SMALLINT;
DECLARE @WEEK_OF_MONTH TINYINT;
DECLARE @WEEK_OF_QUARTER TINYINT;
DECLARE @WEEK_OF_YEAR TINYINT;
DECLARE @MONTH_NAME VARCHAR(9);
DECLARE @FIRST_DATE_OF_WEEK DATE;
DECLARE @LAST_DATE_OF_WEEK DATE;
DECLARE @FIRST_DATE_OF_MONTH DATE;
DECLARE @LAST_DATE_OF_MONTH DATE;
DECLARE @FIRST_DATE_OF_QUARTER DATE;
DECLARE @LAST_DATE_OF_QUARTER DATE;
DECLARE @FIRST_DATE_OF_YEAR DATE;
DECLARE @LAST_DATE_OF_YEAR DATE;
DECLARE @IS_HOLIDAY BIT;
DECLARE @IS_HOLIDAY_SEASON BIT;
DECLARE @HOLIDAY_NAME VARCHAR(50);
DECLARE @HOLIDAY_SEASON_NAME VARCHAR(50);
DECLARE @IS_WEEKDAY BIT;
DECLARE @IS_BUSINESS_DAY BIT;
DECLARE @IS_LEAP_YEAR BIT;
DECLARE @Days_IN_MONTH TINYINT;
--LOOP THROUGH EVERY DAY AND ADD A NEW ROW
WHILE @DATE_COUNTER <= @END_DATE
BEGIN
SELECT @CALENDAR_MONTH = DATEPART(MONTH, @DATE_COUNTER);
SELECT @CALENDAR_DAY = DATEPART(DAY, @DATE_COUNTER);
SELECT @CALENDAR_YEAR = DATEPART(YEAR, @DATE_COUNTER);
SELECT @CALENDAR_QUARTER = DATEPART(QUARTER, @DATE_COUNTER);
SELECT @DAY_OF_WEEK = DATEPART(WEEKDAY, @DATE_COUNTER);
SELECT @DAY_OF_YEAR = DATEPART(DAYOFYEAR, @DATE_COUNTER);
SELECT @WEEK_OF_YEAR = DATEPART(WEEK, @DATE_COUNTER);
--SELECT @CALENDAR_DATE_String = CAST(@CALENDAR_MONTH AS VARCHAR(10)) + '/' + CAST(@CALENDAR_DAY AS VARCHAR(10)) + '/' + CAST(@CALENDAR_YEAR AS VARCHAR(10));
SELECT @CALENDAR_DATE_String = CONVERT(VARCHAR(25), @DATE_COUNTER, 103);
SELECT @IS_WEEKDAY = CASE
WHEN @DAY_OF_WEEK IN (1, 7)
THEN 0
ELSE 1
END;
SELECT @IS_BUSINESS_DAY =@IS_WEEKDAY;
SELECT @DAY_NAME = CASE @DAY_OF_WEEK
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END;
SELECT @MONTH_NAME = CASE @CALENDAR_MONTH
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END;
SELECT @DAY_OF_QUARTER = DATEDIFF(DAY, DATEADD(QUARTER, DATEDIFF(QUARTER, 0 , @DATE_COUNTER), 0), @DATE_COUNTER) + 1;
SELECT @DAY_OF_YEAR = DATEPART(DAYOFYEAR, @DATE_COUNTER);
SELECT @WEEK_OF_MONTH = DATEDIFF(WEEK, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE_COUNTER), 0)), 0), @DATE_COUNTER ) + 1;
SELECT @WEEK_OF_QUARTER = DATEDIFF(DAY, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @DATE_COUNTER), 0), @DATE_COUNTER)/7 + 1;
SELECT @FIRST_DATE_OF_WEEK = DATEADD(DAY, -1 * @DAY_OF_WEEK + 1, @DATE_COUNTER);
SELECT @LAST_DATE_OF_WEEK = DATEADD(DAY, 1 * (7 - @DAY_OF_WEEK), @DATE_COUNTER);
SELECT @FIRST_DATE_OF_MONTH = DATEADD(DAY, -1 * DATEPART(DAY, @DATE_COUNTER) + 1, @DATE_COUNTER);
SELECT @LAST_DATE_OF_MONTH = EOMONTH(@DATE_COUNTER);
SELECT @FIRST_DATE_OF_QUARTER = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @DATE_COUNTER), 0);
SELECT @LAST_DATE_OF_QUARTER = DATEADD (DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @DATE_COUNTER) + 1, 0));
SELECT @FIRST_DATE_OF_YEAR = DATEADD(YEAR, DATEDIFF(YEAR, 0, @DATE_COUNTER), 0);
SELECT @LAST_DATE_OF_YEAR = DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, @DATE_COUNTER) + 1, 0));
SELECT @DAY_OF_WEEK_IN_MONTH = (@CALENDAR_DAY + 6) / 7;
SELECT @DAY_OF_WEEK_IN_YEAR = (@DAY_OF_YEAR + 6) / 7;
SELECT @DAY_OF_WEEK_IN_QUARTER = (@DAY_OF_QUARTER + 6) / 7;
SELECT @IS_LEAP_YEAR = CASE
WHEN @CALENDAR_YEAR % 4 <> 0 THEN 0
WHEN @CALENDAR_YEAR % 100 <> 0 THEN 1
WHEN @CALENDAR_YEAR % 400 <> 0 THEN 0
ELSE 1
END;
SELECT @Days_IN_MONTH = CASE
WHEN @CALENDAR_MONTH IN (4, 6, 9, 11) THEN 30
WHEN @CALENDAR_MONTH IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN @CALENDAR_MONTH = 2 AND @IS_LEAP_YEAR = 1 THEN 29
ELSE 28
END;
INSERT INTO COMMON_DATA.DATE_TABLE
(
CALENDAR_DATE
,CALENDAR_DATE_String
,Calendar_Month
,Calendar_Day
,Calendar_Year
,Calendar_Quarter
,Day_Name
,Day_of_Week
,Day_of_Week_IN_MONTH
,Day_of_Week_IN_YEAR
,Day_of_Week_IN_QUARTER
,DAY_OF_QUARTER
,DAY_OF_YEAR
,WEEK_OF_MONTH
,WEEK_OF_QUARTER
,WEEK_OF_YEAR
,MONTH_NAME
,FIRST_DATE_OF_WEEK
,LAST_DATE_OF_WEEK
,FIRST_DATE_OF_MONTH
,LAST_DATE_OF_MONTH
,FIRST_DATE_OF_QUARTER
,LAST_DATE_OF_QUARTER
,FIRST_DATE_OF_YEAR
,LAST_DATE_OF_YEAR
,IS_HOLIDAY
,IS_HOLIDAY_SEASON
,HOLIDAY_NAME
,HOLIDAY_SEASON_NAME
,IS_WEEKDAY
,IS_BUSINESS_DAY
,PREVIOUS_BUSINESS_DAY
,NEXT_BUSINESS_DAY
,IS_LEAP_YEAR
,Days_IN_MONTH
)
SELECT
@DATE_COUNTER AS CALENDAR_DATE
,@CALENDAR_DATE_String AS CALENDAR_DATE_String
,@CALENDAR_MONTH AS Calendar_Month
,@CALENDAR_DAY AS Calendar_Day
,@CALENDAR_YEAR AS Calendar_Year
,@CALENDAR_QUARTER AS Calendar_Quarter
,@DAY_NAME AS Day_Name
,@DAY_OF_WEEK AS Day_of_Week
,@DAY_OF_WEEK_IN_MONTH AS Day_of_Week_IN_MONTH
,@DAY_OF_WEEK_IN_YEAR AS Day_of_Week_IN_YEAR
,@DAY_OF_WEEK_IN_QUARTER AS Day_of_Week_IN_QUARTER
,@DAY_OF_QUARTER AS DAY_OF_QUARTER
,@DAY_OF_YEAR AS DAY_OF_YEAR
,@WEEK_OF_MONTH AS WEEK_OF_MONTH
,@WEEK_OF_QUARTER AS WEEK_OF_QUARTER
,@WEEK_OF_YEAR AS WEEK_OF_YEAR
,@MONTH_NAME AS MONTH_NAME
,@FIRST_DATE_OF_WEEK AS FIRST_DATE_OF_WEEK
,@LAST_DATE_OF_WEEK AS LAST_DATE_OF_WEEK
,@FIRST_DATE_OF_MONTH AS FIRST_DATE_OF_MONTH
,@LAST_DATE_OF_MONTH AS LAST_DATE_OF_MONTH
,@FIRST_DATE_OF_QUARTER AS FIRST_DATE_OF_QUARTER
,@LAST_DATE_OF_QUARTER AS LAST_DATE_OF_QUARTER
,@FIRST_DATE_OF_YEAR AS FIRST_DATE_OF_YEAR
,@LAST_DATE_OF_YEAR AS LAST_DATE_OF_YEAR
,0 AS IS_HOLIDAY
,0 AS IS_HOLIDAY_SEASON
,NULL AS HOLIDAY_NAME
,NULL AS HOLIDAY_SEASON_NAME
,@IS_WEEKDAY AS IS_WEEKDAY
,@IS_BUSINESS_DAY AS IS_BUSINESS_DAY -- Will be populated with weekends to start.
,NULL AS PREVIOUS_BUSINESS_DAY
,NULL AS NEXT_BUSINESS_DAY
,@IS_LEAP_YEAR AS IS_LEAP_YEAR
,@Days_IN_MONTH AS Days_IN_MONTH
SELECT @DATE_COUNTER = DATEADD(DAY, 1, @DATE_COUNTER);
END;
-- UPDATE THE CALENDAR TABLE WITH BUSINESS DAY DATA
WITH CTE_BUSINESS_DAYS AS
(
SELECT BUSINESS_DAYS.CALENDAR_DATE
FROM COMMON_DATA.DATE_TABLE BUSINESS_DAYS
WHERE BUSINESS_DAYS.IS_BUSINESS_DAY = 1
)
UPDATE DATE_TABLE_CURRENT
SET PREVIOUS_BUSINESS_DAY = CTE_BUSINESS_DAYS.CALENDAR_DATE
FROM COMMON_DATA.DATE_TABLE DATE_TABLE_CURRENT
INNER JOIN CTE_BUSINESS_DAYS
ON CTE_BUSINESS_DAYS.CALENDAR_DATE = ( SELECT MAX(PREVIOUS_BUSINESS_DAY.CALENDAR_DATE) FROM CTE_BUSINESS_DAYS PREVIOUS_BUSINESS_DAY
WHERE PREVIOUS_BUSINESS_DAY.CALENDAR_DATE < DATE_TABLE_CURRENT.CALENDAR_DATE)
WHERE DATE_TABLE_CURRENT.CALENDAR_DATE BETWEEN @START_DATE AND @END_DATE;
WITH CTE_BUSINESS_DAYS AS
(
SELECT BUSINESS_DAYS.CALENDAR_DATE
FROM COMMON_DATA.DATE_TABLE BUSINESS_DAYS
WHERE BUSINESS_DAYS.IS_BUSINESS_DAY = 1
)
UPDATE DATE_TABLE_CURRENT
SET NEXT_BUSINESS_DAY = CTE_BUSINESS_DAYS.CALENDAR_DATE
FROM COMMON_DATA.DATE_TABLE DATE_TABLE_CURRENT
INNER JOIN CTE_BUSINESS_DAYS
ON CTE_BUSINESS_DAYS.CALENDAR_DATE = (SELECT MIN(NEXT_BUSINESS_DAY.CALENDAR_DATE) FROM CTE_BUSINESS_DAYS NEXT_BUSINESS_DAY
WHERE NEXT_BUSINESS_DAY.CALENDAR_DATE > DATE_TABLE_CURRENT.CALENDAR_DATE)
WHERE DATE_TABLE_CURRENT.CALENDAR_DATE BETWEEN @START_DATE AND @END_DATE;
END
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply