5 previous working days

Transact-SQL
1

Hi

I'm struggling to write a code to get five previous working days. I'm trying to get 'Today', 'Yesterday', 'A day before Yesterday', '3 days prior Today' and '4 days prior Today', but the fields should only be Working days. for Yesterday I would minus 1 from getdate(), but I would also have to test if it's not a weekend. Please help.

2

There are lots of articles on calendar tables and how to use them.
eg:
http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/

3

You could do something like:

select top 5 convert(varchar(8),a.workday,112) as WorkDays
from (VALUES
(dateadd(d,-1,getdate()),CHOOSE(DATEPART(dw, dateadd(d,-1,getdate())),0,1,1,1,1,1,0)),
(dateadd(d,-2,getdate()),CHOOSE(DATEPART(dw, dateadd(d,-2,getdate())),0,1,1,1,1,1,0)),
(dateadd(d,-3,getdate()),CHOOSE(DATEPART(dw, dateadd(d,-3,getdate())),0,1,1,1,1,1,0)),
(dateadd(d,-4,getdate()),CHOOSE(DATEPART(dw, dateadd(d,-4,getdate())),0,1,1,1,1,1,0)),
(dateadd(d,-5,getdate()),CHOOSE(DATEPART(dw, dateadd(d,-5,getdate())),0,1,1,1,1,1,0)),
(dateadd(d,-6,getdate()),CHOOSE(DATEPART(dw, dateadd(d,-6,getdate())),0,1,1,1,1,1,0)),
(dateadd(d,-7,getdate()),CHOOSE(DATEPART(dw, dateadd(d,-7,getdate())),0,1,1,1,1,1,0))
) as a(WorkDay,TrueFalse)
where a.TrueFalse = 1
order by a.WorkDay desc

4

Another one:

;WITH CTE
AS
(
SELECT DATEADD(DD,- 1, GETDATE()) AS DT, 1 AS NUM

	UNION ALL
	
	SELECT DATEADD(DD,- 1, DT) , NUM + 1
	FROM CTE
	WHERE NUM<7
)

SELECT *, DATENAME(DW,DT)
FROM CTE
WHERE DATENAME(DW,DT) NOT IN ('Sunday','Saturday')