List Month End Dates Between Two Dates
-
Hi everyone
I am working on a query where I need to list all business month end dates between two dates. The two dates would define the start and end periods but these two dates are dynamically created:
DECLARE @START_DATE DATE = DATEADD(YEAR,-1,GETDATE())
DECLARE @END_DATE DATE = GETDATE()I am not sure how to generate the business month end dates between @START_DATE and @END_DATE. Is someone able to help me with this? I do not want to create a separate calendar table b/c I am trying to keep the number of tables in my DB to a minimum. I am ok ignoring stat holidays b/c the markets are closed those days so there would be no trading records for that date.
If business month end is too complicated then regular calendar month end is ok too.
Here is expected outcome (the query should only return Last Business Day column):
Thank you
-
As you have not provided the logic for your 'business month end' calculation, I'm not sure how you are expecting anyone to calculate it.
EOMONTH() gives you end of month for a given date.
What is your technical justification for not wanting to create a calendar table? It's the easiest (and probably fastest) way of solving this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
It would appear that a simple definition of what you want is as follows:
Return the last weekday of the month (regardless of any holidays).
To help others understand that, it means return the last day of the month unless it occurs during the weekend (defined as Saturday or Sunday, just to be clear). If it occurs during the weekend, return the Friday before.
But that also leads us to the question of what you really NEED. You say you want to generate all such month ending dates in a table but what would you use that for? To convert non-month-ending dates to the proper month ending date?
Would it be better for you to be able to easily calculate the proper month ending date for any given date?
And, please, stop posting data only as a graphic. It's ok to post graphics to make explaining things but always post it as "Readily Consumable Data", as well. That means creating a test table and populating it with the example data.
Let us know because either way can be done quite easily if the definition I provided above is correct.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Phil Parkin wrote:
As you have not provided the logic for your 'business month end' calculation, I'm not sure how you are expecting anyone to calculate it.
EOMONTH() gives you end of month for a given date.
What is your technical justification for not wanting to create a calendar table? It's the easiest (and probably fastest) way of solving this.
I did give an example of it. It would be the last day of the month. If the last day is a Sat/Sun then use Friday. If Friday is a stat then ignore the fact that it is a stat and still use Friday.
I just don't want another table so I was hoping to have a SQL query to dynamically create the dates. One less table.
- Jeff Moden wrote:
It would appear that a simple definition of what you want is as follows:
Return the last weekday of the month (regardless of any holidays).
To help others understand that, it means return the last day of the month unless it occurs during the weekend (defined as Saturday or Sunday, just to be clear). If it occurs during the weekend, return the Friday before.
But that also leads us to the question of what you really NEED. You say you want to generate all such month ending dates in a table but what would you use that for? To convert non-month-ending dates to the proper month ending date?
Would it be better for you to be able to easily calculate the proper month ending date for any given date?
And, please, stop posting data only as a graphic. It's ok to post graphics to make explaining things but always post it as "Readily Consumable Data", as well. That means creating a test table and populating it with the example data.
Let us know because either way can be done quite easily if the definition I provided above is correct.
Yes that is the definition I had in mind. Thanks for clarification.
I am doing a calculation so I need to partition the data by time and I need to have a column that has the dates I need.
-
Ok. Here's an iTVF that will do the trick for you in which ever way you might want to solve your problem. As with most things, the details are in the flower box including a note to your DBA if they balk at using a function. Not all functions present the same problems.
The usage examples cover both calculating the proper (follows the method you asked for) Month End Date (and returns the name of the day, as well, just in case) and the idea of generating an on-the-fly micro-calendar table if that's the way you need to go.
If you go with the second usage example, you're not using SQL Server 2022 and so you can't take advantage of the new GENERATE_SERIES() function and you'll need to cop a copy of the dbo.fnTally() function from the link provided.
Here's the code for the function. I've tested the bejeezus out of it but I'm only human. If it ever produces a bad answer, come back to this post and tell me about it, please.
CREATE FUNCTION dbo.LastWeekdayOfMonth
/**********************************************************************************************************************
Purpose:
Given any date, return the last weekday of the month.
To my fellow DBAs:
This is a high performance iTVF (Inline Table Valued Function) and will NOT cause queries to go single threaded like
either a Scalar UDF or mTVF (Mult-statement Table Valued Function) can.
-----------------------------------------------------------------------------------------------------------------------
Usage Examples:
--===== For use on a column of dates
SELECT st.SomeDateCol
,lwdm.LastWeekdayOfMonth --Note that the return value has the same name as the function
,lwdm.LastWeekDayOfMonthName
FROM dbo.SomeTable st
CROSS APPLY dbo.LastWeekdayOfMonth(st.someDateCol)lwdm
;
--===== Usage to generate a range of LastWeekdayOfMonth month values
-- If you don't have SQL Server 2022, you'll need a "sequence generator". The dbo.fnTally function is such a
-- generator (although only integers starting at either 0 or 1) and can be found at the following link.
-- https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
DECLARE @StartDate DATE = DATEADD(yy,-1,SYSDATETIME()) --GETDATE() or date literal will also work here.
,@EndDate DATE = SYSDATETIME() --GETDATE() or date literal will also work here.
;
SELECT --===== These 3 columns provide something similar to a Calendar Table.
MonthStartDate = DATEADD(mm,t.N,DATETRUNC(mm,@StartDate))
,MonthEndDate = EOMONTH(DATEADD(mm,t.N,@StartDate))
,EOMonthDowName = DATENAME(dw,EOMONTH(DATEADD(mm,t.N,@StartDate)))
--===== These two columns provide the LastWeekdayOfMonth according to the Design Notes and the name of the day.
,lwdm.LastWeekdayOfMonth
,lwdm.LastWeekdayOfMonthName
FROM dbo.fnTally(0,DATEDIFF(mm,@StartDate,@EndDate)) t
CROSS APPLY dbo.LastWeekdayOfMonth(DATEADD(mm,t.N,@StartDate))lwdm
;
-----------------------------------------------------------------------------------------------------------------------
Design Notes:
1. If the EOMONTH occurs on a weekday, use that date.
2. If the EOMONTH occurs on a Saturday or a Sunday, return the Friday before.
3. The code must be DATEFIRST agnostic.
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 09 Jul 2023 - Jeff Moden
Initial creation an unit test.
Original Request:
https://www.sqlservercentral.com/forums/topic/list-month-end-dates-between-two-dates
**********************************************************************************************************************/
--===== Declare the I/0
(
@Date DATE
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== Calculate the last weekday of the month using a Friday (0001-01-05) as the "0" day of the week.
-- For the "Offset", Fridays will return a "0", Saturday will return a "1", Sunday will return a 2, etc.
-- If that value is > 2, then don't use the offset. Use the EOMONTH day.
-- If it's NOT > 2, then the EOMonth date occured on Friday, Saturday, or Sunday. We simply subtract the
-- 0, 1, or 2 from the EOMonth date to return the Friday. Obviously, subtracting 0 does nothing so
-- Friday continues to be Friday. This removes the need to do a +1 for every day.
SELECT v2.LastWeekdayOfMonth
,LastWeekDayOfMonthName = DATENAME(dw,v2.LastWeekdayOfMonth)
FROM (VALUES(DATEDIFF(dd,'00010105',EOMONTH(@Date))%7)) v1 (Offset)
CROSS APPLY (VALUES(IIF(v1.Offset>2, EOMONTH(@Date), DATEADD(dd,-v1.Offset,EOMONTH(@Date)))))v2(LastWeekdayOfMonth)
;
GO--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - water490 wrote:Phil Parkin wrote:
As you have not provided the logic for your 'business month end' calculation, I'm not sure how you are expecting anyone to calculate it.
EOMONTH() gives you end of month for a given date.
What is your technical justification for not wanting to create a calendar table? It's the easiest (and probably fastest) way of solving this.
I did give an example of it. It would be the last day of the month. If the last day is a Sat/Sun then use Friday. If Friday is a stat then ignore the fact that it is a stat and still use Friday.
I just don't want another table so I was hoping to have a SQL query to dynamically create the dates. One less table.
water490 wrote:Phil Parkin wrote:As you have not provided the logic for your 'business month end' calculation, I'm not sure how you are expecting anyone to calculate it.
EOMONTH() gives you end of month for a given date.
What is your technical justification for not wanting to create a calendar table? It's the easiest (and probably fastest) way of solving this.
I did give an example of it. It would be the last day of the month. If the last day is a Sat/Sun then use Friday. If Friday is a stat then ignore the fact that it is a stat and still use Friday.
I just don't want another table so I was hoping to have a SQL query to dynamically create the dates. One less table.
I understand that calculating the dates you need will likely be faster than creating a calendar table. But "one less table" is really not a reason. That makes little sense. If there is a need for a new table to support the application and business rules, you would create one, correct? What is the reasoning for this because your explanation makes little sense. Remember that there are many people who use these threads as a resource. An explanation would be beneficial.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Here's an alternative, an all-in-one function that directly produces the last_business_days from start_date and end_date params.
If for some reason you needed more than 100 months of results, naturally you'd have to increase the tally table size.
Example usage: SELECT * FROM dbo.gen_last_business_day_of_each_month('20220213', GETDATE())
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER FUNCTION dbo.gen_last_business_day_of_each_month (
@start_date date,
@end_date date
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH
cte_tally10 AS (
SELECT number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
),
cte_month_end_dates AS (
SELECT EOMONTH(DATEADD(MONTH, t.number, @start_date)) AS month_end
FROM cte_tally100 t
WHERE t.number BETWEEN 0 AND (DATEDIFF(MONTH, 0, @end_date) - DATEDIFF(MONTH, 0, @start_date))
)
SELECT CASE WHEN ca1.days_past_Friday > 2 THEN cm.month_end ELSE DATEADD(DAY, -ca1.days_past_Friday, cm.month_end) END AS Last_Business_Day
FROM cte_month_end_dates cm
CROSS APPLY ( SELECT DATEDIFF(DAY, '19000105', cm.month_end) % 7 AS days_past_Friday ) AS ca1
GOSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply