Calculate Business Day by Year and Month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculate Business Day by Year and Month

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-05-01 : 12:24:35

I have a calendar table which has columns Y, M, D, which would translate into 2012, 5, 1 for May 1, 2012. It has a number of years and months in it. Column dw has the days of the week. I am using a query like this to capture only M-F.

SELECT Y,M,D, MBusDayNum FROM dbo.Calendar WHERE dw BETWEEN 2 AND 6 ORDER BY dt.

The column MBusDayNum is NULL now, but I want it to have a corresponding business day of month in it which numbers the days of the month not counting weekends as business days. I may filter it more later. Most months have between 21 and 24 business days, I suppose. Then I can run something on the 6th business day, for example. I want to populate the whole column MBusDayNum.

I am not sure if a cursor would work, and if so, how would I set it up? I would grab the 1st day of each month from the query and increment it and start over at the next month. Thanks for any suggestions.

Scripts here:

CREATE TABLE [dbo].[Calendar](
	[dt] [smalldatetime] NOT NULL,
	[isWeekday] [bit] NULL,
	[isHoliday] [bit] NULL,
	[BusinessDayOverride] [char](1) NULL,
	[Y] [smallint] NULL,
	[FY] [smallint] NULL,
	[Q] [tinyint] NULL,
	[M] [tinyint] NULL,
	[D] [tinyint] NULL,
	[DW] [tinyint] NULL,
	[MonthName] [varchar](9) NULL,
	[DayName] [varchar](9) NULL,
	[HolidayName] [varchar](25) NULL,
	[W] [tinyint] NULL,
	[UTCOffset] [tinyint] NULL,
	[MBusDayNum] [smallint] NULL,
PRIMARY KEY CLUSTERED 
(
	[dt] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

BEGIN TRANSACTION;
INSERT INTO [dbo].[Calendar]([dt], [isWeekday], [isHoliday], [BusinessDayOverride], [Y], [FY], [Q], [M], [D], [DW], [MonthName], [DayName], [HolidayName], [W], [UTCOffset], [MBusDayNum])
SELECT '20120519 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 5, 19, 7, N'May', N'Saturday', NULL, 20, 4, NULL UNION ALL
SELECT '20120520 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 5, 20, 1, N'May', N'Sunday', NULL, 21, 4, NULL UNION ALL
SELECT '20120521 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 21, 2, N'May', N'Monday', NULL, 21, 4, NULL UNION ALL
SELECT '20120522 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 22, 3, N'May', N'Tuesday', NULL, 21, 4, NULL UNION ALL
SELECT '20120523 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 23, 4, N'May', N'Wednesday', NULL, 21, 4, NULL UNION ALL
SELECT '20120524 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 24, 5, N'May', N'Thursday', NULL, 21, 4, NULL UNION ALL
SELECT '20120525 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 25, 6, N'May', N'Friday', NULL, 21, 4, NULL UNION ALL
SELECT '20120526 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 5, 26, 7, N'May', N'Saturday', NULL, 21, 4, NULL UNION ALL
SELECT '20120527 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 5, 27, 1, N'May', N'Sunday', NULL, 22, 4, NULL UNION ALL
SELECT '20120528 00:00:00.000', 1, 1, NULL, 2012, 2012, 2, 5, 28, 2, N'May', N'Monday', N'Memorial Day', 22, 4, NULL UNION ALL
SELECT '20120529 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 29, 3, N'May', N'Tuesday', NULL, 22, 4, NULL UNION ALL
SELECT '20120530 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 30, 4, N'May', N'Wednesday', NULL, 22, 4, NULL UNION ALL
SELECT '20120531 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 31, 5, N'May', N'Thursday', NULL, 22, 4, NULL UNION ALL
SELECT '20120601 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 1, 6, N'June', N'Friday', NULL, 22, 4, NULL UNION ALL
SELECT '20120602 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 6, 2, 7, N'June', N'Saturday', NULL, 22, 4, NULL UNION ALL
SELECT '20120603 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 6, 3, 1, N'June', N'Sunday', NULL, 23, 4, NULL UNION ALL
SELECT '20120604 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 4, 2, N'June', N'Monday', NULL, 23, 4, NULL UNION ALL
SELECT '20120605 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 5, 3, N'June', N'Tuesday', NULL, 23, 4, NULL UNION ALL
SELECT '20120606 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 6, 4, N'June', N'Wednesday', NULL, 23, 4, NULL UNION ALL
SELECT '20120607 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 7, 5, N'June', N'Thursday', NULL, 23, 4, NULL UNION ALL
SELECT '20120608 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 8, 6, N'June', N'Friday', NULL, 23, 4, NULL UNION ALL
SELECT '20120609 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 6, 9, 7, N'June', N'Saturday', NULL, 23, 4, NULL UNION ALL
SELECT '20120610 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 6, 10, 1, N'June', N'Sunday', NULL, 24, 4, NULL UNION ALL
SELECT '20120611 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 11, 2, N'June', N'Monday', NULL, 24, 4, NULL UNION ALL
SELECT '20120612 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 12, 3, N'June', N'Tuesday', NULL, 24, 4, NULL UNION ALL
SELECT '20120613 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 13, 4, N'June', N'Wednesday', NULL, 24, 4, NULL UNION ALL
SELECT '20120614 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 14, 5, N'June', N'Thursday', NULL, 24, 4, NULL UNION ALL
SELECT '20120615 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 15, 6, N'June', N'Friday', NULL, 24, 4, NULL UNION ALL
SELECT '20120616 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 6, 16, 7, N'June', N'Saturday', NULL, 24, 4, NULL UNION ALL
SELECT '20120617 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 6, 17, 1, N'June', N'Sunday', NULL, 25, 4, NULL UNION ALL
SELECT '20120618 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 18, 2, N'June', N'Monday', NULL, 25, 4, NULL UNION ALL
SELECT '20120619 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 19, 3, N'June', N'Tuesday', NULL, 25, 4, NULL UNION ALL
SELECT '20120620 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 20, 4, N'June', N'Wednesday', NULL, 25, 4, NULL UNION ALL
SELECT '20120621 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 21, 5, N'June', N'Thursday', NULL, 25, 4, NULL UNION ALL
SELECT '20120622 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 22, 6, N'June', N'Friday', NULL, 25, 4, NULL UNION ALL
SELECT '20120623 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 6, 23, 7, N'June', N'Saturday', NULL, 25, 4, NULL UNION ALL
SELECT '20120624 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 6, 24, 1, N'June', N'Sunday', NULL, 26, 4, NULL UNION ALL
SELECT '20120625 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 25, 2, N'June', N'Monday', NULL, 26, 4, NULL UNION ALL
SELECT '20120626 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 26, 3, N'June', N'Tuesday', NULL, 26, 4, NULL UNION ALL
SELECT '20120627 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 27, 4, N'June', N'Wednesday', NULL, 26, 4, NULL UNION ALL
SELECT '20120628 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 28, 5, N'June', N'Thursday', NULL, 26, 4, NULL UNION ALL
SELECT '20120629 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 6, 29, 6, N'June', N'Friday', NULL, 26, 4, NULL UNION ALL
SELECT '20120630 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 6, 30, 7, N'June', N'Saturday', NULL, 26, 4, NULL UNION ALL
SELECT '20120501 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 1, 3, N'May', N'Tuesday', NULL, 18, 4, NULL UNION ALL
SELECT '20120502 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 2, 4, N'May', N'Wednesday', NULL, 18, 4, NULL UNION ALL
SELECT '20120503 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 3, 5, N'May', N'Thursday', NULL, 18, 4, NULL UNION ALL
SELECT '20120504 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 4, 6, N'May', N'Friday', NULL, 18, 4, NULL UNION ALL
SELECT '20120505 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 5, 5, 7, N'May', N'Saturday', NULL, 18, 4, NULL UNION ALL
SELECT '20120506 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 5, 6, 1, N'May', N'Sunday', NULL, 19, 4, NULL UNION ALL
SELECT '20120507 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 7, 2, N'May', N'Monday', NULL, 19, 4, NULL
COMMIT;
RAISERROR (N'[dbo].[Calendar]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

BEGIN TRANSACTION;
INSERT INTO [dbo].[Calendar]([dt], [isWeekday], [isHoliday], [BusinessDayOverride], [Y], [FY], [Q], [M], [D], [DW], [MonthName], [DayName], [HolidayName], [W], [UTCOffset], [MBusDayNum])
SELECT '20120508 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 8, 3, N'May', N'Tuesday', NULL, 19, 4, NULL UNION ALL
SELECT '20120509 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 9, 4, N'May', N'Wednesday', NULL, 19, 4, NULL UNION ALL
SELECT '20120510 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 10, 5, N'May', N'Thursday', NULL, 19, 4, NULL UNION ALL
SELECT '20120511 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 11, 6, N'May', N'Friday', NULL, 19, 4, NULL UNION ALL
SELECT '20120512 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 5, 12, 7, N'May', N'Saturday', NULL, 19, 4, NULL UNION ALL
SELECT '20120513 00:00:00.000', 0, 0, NULL, 2012, 2012, 2, 5, 13, 1, N'May', N'Sunday', NULL, 20, 4, NULL UNION ALL
SELECT '20120514 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 14, 2, N'May', N'Monday', NULL, 20, 4, NULL UNION ALL
SELECT '20120515 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 15, 3, N'May', N'Tuesday', NULL, 20, 4, NULL UNION ALL
SELECT '20120516 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 16, 4, N'May', N'Wednesday', NULL, 20, 4, NULL UNION ALL
SELECT '20120517 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 17, 5, N'May', N'Thursday', NULL, 20, 4, NULL UNION ALL
SELECT '20120518 00:00:00.000', 1, 0, NULL, 2012, 2012, 2, 5, 18, 6, N'May', N'Friday', NULL, 20, 4, NULL
COMMIT;
RAISERROR (N'[dbo].[Calendar]: Insert Batch: 2.....Done!', 10, 1) WITH NOWAIT;
GO

Duane

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 12:27:45

you've to consider only weekend? what about holidays in between? do you've a table storing them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-05-01 : 12:33:52

I am not worried about holidays and such at the moment. I am just trying to get the logic. I will do the holidays later, but I am not sure which ones will count yet or not. I can change the internal query I have listed above to screen out holidays later.

Actually, the holidays are in the table with the isHoliday flag. Ironically, there are no holidays in my sample data, but I don't care at this point. Thank you.

Duane

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 13:30:41

Rather than using a cursor to walk through the table, you can use a query such as this to update the table. My logic may not exactly be right, but should be close.

;WITH cte AS
(
	SELECT a.*,b.BusinessDays
	FROM Calendar a
	CROSS APPLY
	(
		SELECT SUM(CASE WHEN b.dw BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS BusinessDays
		FROM Calendar b 
		WHERE b.Y = a.Y AND b.M = a.M AND b.dt <= a.dt
	) b
)
SELECT * FROM cte;
--UPDATE cte SET MBusDayNum = BusinessDays;

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2012-05-01 : 14:06:50

Thank you very much! That was the solution, and it was fast, too. Actually, I was hoping to avoid the cursor, and I knew something could be done with a CTE, but couldn't figure out how to do it. I changed the update statement to:

UPDATE cte SET MBusDayNum = BusinessDays WHERE DW BETWEEN 2 AND 6 because the Saturdays and Sundays were being populated with Friday's business day. That is the only difference, and maybe I will end up changing it back after thinking about it.

Thanks again.

Duane

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 15:47:09

You are very welcome.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources