How get "last business day" - WHERE on Date Column

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 2000 Forums

Transact-SQL (2000)

How get "last business day" - WHERE on Date Column

Author

Topic

dantesfu
Starting Member

7 Posts

Posted - 2006-07-19 : 22:28:28

I have a table that has a Date column (DateProcessed) where I need to show all the rows that matched by the "previous business day".

I know how to get "yesterday's date" by subtracting today's date by 1 day but this would not work all the time because if today is Monday, it gives me the result for Sunday instead of “Friday” which is the last business day I am really looking to display.

How can I do this?

SELECT * FROM table
WHERE DateProcessed ?????

I thank you for your help.

Dan

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-19 : 22:34:44

You can get the day of week by using the DATEPART function by using the 'w' parameter.

e.g. DATEPART(w, DateProcessed)
it will give you a number b/w 1 and 7 (1=Sunday IIRC).

I would probably create a new function to return the previous business day, then you can use this function in your JOIN clause

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-19 : 22:56:26

select	dte, 		left(datename(weekday, dte), 3), 
	prev_bizday, 	left(datename(weekday, prev_bizday), 3)
from
(
	select 	dte, 
		prev_bizday	= dateadd(day, case when datepart(weekday, dte) = 1 then -2 
					            when datepart(weekday, dte) = 2 then -3 else -1 end, dte)
	from
	(
		select convert(datetime, '2006-07-17') as dte union all
		select convert(datetime, '2006-07-18') as dte union all
		select convert(datetime, '2006-07-19') as dte union all
		select convert(datetime, '2006-07-20') as dte union all
		select convert(datetime, '2006-07-21') as dte union all
		select convert(datetime, '2006-07-22') as dte union all
		select convert(datetime, '2006-07-23') as dte union all
		select convert(datetime, '2006-07-24') as dte union all
		select convert(datetime, '2006-07-25') as dte 
	) d
) dd

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-19 : 23:30:22

Here are two solutions. Both produce the same result.

The first one uses the date table function from this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519


declare @start_date	datetime
declare @end_date	datetime
select @end_date	= dateadd(dd,-1,'20060717')
select @start_date	=@end_date-4

select
	LAST_WEEKDAY_DATE = max([DATE])
from 
	dbo.F_TABLE_DATE ( @start_date,@end_date)
where
	[DAY_OF_WEEK]	 between 2 and 6

The second one is self-contained, and would be easy to make into a function:


declare @date datetime
select @date = '20060717'

select 	LAST_WEEKDAY_DATE = max(a.[DATE])
from
	(
	select
		[DATE] = dateadd(dd,datediff(dd,0,@date)-num,0)
	from
		(
		select num = 1 union all select num = 2 union all
		select num = 3 union all select num = 4 union all
		select num = 5
		) aa
	) a
where
	-- select only weekdays
	 datediff(dd,'17530101',a.[DATE])%7 < 5

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-19 : 23:47:19

quote:

Originally posted by khtan

select	dte, 		left(datename(weekday, dte), 3), 
	prev_bizday, 	left(datename(weekday, prev_bizday), 3)
from
(
	select 	dte, 
		prev_bizday	= dateadd(day, case when datepart(weekday, dte) = 1 then -2 
					            when datepart(weekday, dte) = 2 then -3 else -1 end, dte)
	from
	(
		select convert(datetime, '2006-07-17') as dte union all
		select convert(datetime, '2006-07-18') as dte union all
		select convert(datetime, '2006-07-19') as dte union all
		select convert(datetime, '2006-07-20') as dte union all
		select convert(datetime, '2006-07-21') as dte union all
		select convert(datetime, '2006-07-22') as dte union all
		select convert(datetime, '2006-07-23') as dte union all
		select convert(datetime, '2006-07-24') as dte union all
		select convert(datetime, '2006-07-25') as dte 
	) d
) dd

Here is a variation of your solution that is independent of the setting of DATEFIRST.


select 	dte, 
	prev_bizday	=
		dateadd(dd,
		case datediff(dd,'17530101',dte)%7
		when 0 then -3
		when 6 then -2
		else -1 end,dte)
from
	(
	select convert(datetime, '2006-07-17') as dte union all
	select convert(datetime, '2006-07-18') as dte union all
	select convert(datetime, '2006-07-19') as dte union all
	select convert(datetime, '2006-07-20') as dte union all
	select convert(datetime, '2006-07-21') as dte union all
	select convert(datetime, '2006-07-22') as dte union all
	select convert(datetime, '2006-07-23') as dte union all
	select convert(datetime, '2006-07-24') as dte union all
	select convert(datetime, '2006-07-25') as dte 
	) a

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-20 : 00:00:55

"Here is a variation of your solution that is independent of the setting of DATEFIRST."
Thanks. This is a good one.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-20 : 00:04:41

quote:
Originally posted by khtan

"Here is a variation of your solution that is independent of the setting of DATEFIRST."
Thanks. This is a good one.

KH

I like that one better than the solutions in my first post. I hadn't thought about using a CASE.

CODO ERGO SUM

dantesfu
Starting Member

7 Posts

Posted - 2006-07-20 : 10:33:19

Thank you very much to each and everyone of you. You guys are great! I am learning a lot.

MikeDevenney
Starting Member

20 Posts

Posted - 2008-03-11 : 14:55:11

Not sure if this will help anyone but it's something to think about...

When looking up last business day we have to take bank holidays into account (when the markets are closed). I added an IF EXISTS block to check if the return date was in the list of holidays for the current year (we store them becuase we have a number of apps that require this information).

Mike Devenney

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-11 : 21:44:01

quote:
Originally posted by MikeDevenney

Not sure if this will help anyone but it's something to think about...

When looking up last business day we have to take bank holidays into account (when the markets are closed). I added an IF EXISTS block to check if the return date was in the list of holidays for the current year (we store them becuase we have a number of apps that require this information).

Mike Devenney

If you have the holiday table, this can be done easily using MVJ's method, the one with F_TABLE_DATE and JOIN to the holiday table.

KH
[spoiler]Time is always against us[/spoiler]

ChrisR
Starting Member

2 Posts

Posted - 2009-03-16 : 11:30:50

Hello. I found this thread on a Google search. I'm looking to incorporate something similiar to the above in a query we use. I've never had a programming class, but can usually make small modifications to existing code with success. I'm hoping someone can lay this out for me...I'd like to:

Select * from MoxyAllocation where TradeDate = (((last business day)))

(((last business day))) being a variable of M-F, adjusting for holiday dates found in the MoxyHoliday table.

Also, TradeDate found in MoxyAllocation is formatted as yyyy-mm-dd hh:mm:ss.sss. I would only want to filter on yyyy-mm-dd and not have any information excluded because of time.

We're on SQL2005 - I'm hoping the solution to this is pretty similar to the solution on SQL2000.

I'll keep poking around myself, but I would appreciate any input anyone could provide!

Thanks,
Chris

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 13:34:47

[code]

first create a calendar table like this

SET DATEFIRST 7
;With CTE (Date)
AS
(SELECT '19000101'
UNION ALL
SELECT DATEADD(dd,1,Date)
FROM CTE
WHERE DATEADD(dd,1,Date)<='20200101'
)

INSERT INTO Calendar_Table (Date,Day,BusinessDay)
SELECT Date,DATENAME(dd,Date),CASE WHEN DATEPART(wk,Date) NOT IN (1,7) THEN 1 ELSE 0 END
FROM CTE

SELECT m.*
from MoxyAllocation m
CROSS APPLY (
SELECT TOP 1 Date
FROM Calendar_Table c
LEFT JOIN MoxyHoliday h
ON h.Date=c.Date
WHERE c.Date< DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND c.BusinessDay=1
AND h.Date IS NULL
ORDER BY Date DESC) prev
WHERE m.TradeDate=prev.Date
[/code]

ChrisR
Starting Member

2 Posts

Posted - 2009-03-16 : 15:28:45

Hi,

Thanks for the reply. I'm getting this message:

Msg 240, Level 16, State 1, Line 2
Types don't match between the anchor and the recursive part in column "Date" of recursive query "CTE".

Appreciate any help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources