How do I get yesterday data in SQL server considering weekends

I created the following test data to demonstrate what you need to do - it's just a series of dates with a number to represent some data

create table #test
(
	datadate date,
	data int
)
declare @startdate date = '2018-05-01'
declare @enddate date = getdate()


;WITH q AS
    (
    SELECT  @startdate AS d1, 1 as d2
    UNION ALL
    SELECT  dateadd(d, 1, d1) , d2 + 1 
    FROM    q
    WHERE dateadd(d, 1, d1) < @enddate
    )
INSERT INTO #test
SELECT  d1, d2
FROM    q

To get "yesterday's" data is simple:

SELECT * FROM #test
WHERE datadate = DATEADD(d, -1, cast(getdate() as date))

The CAST is just to get rid of the time element of getdate() otherwise no data is returned.

To get the week number I'm using the built in DATEPART function with a parameter of week. E.g.

select * FROM #test
WHERE (DATEPART(week,datadate)- DATEPART(week,DATEADD(m, DATEDIFF(m, 0, datadate), 0))) + 1 = 3

To determine what day of the week it is I'm not going to use the word "Monday" etc as that is language dependent. I'm going to use the index or number of the day within the week. But this depends on your setting of @@DATEFIRSTLukeH at this post[^] provides a nice solution:

select *, DATENAME(dw, datadate), DATEPART(dw, datadate) FROM #test 
WHERE ((DATEPART(dw, datadate) + @@DATEFIRST) % 7) NOT IN (0, 1)

Now you need to put it all together. You could use CASE in the WHERE clause, but I'm not a fan. In my example below I use a CTE (Common Table Expression) to first work out how many days back I want to include and then use BETWEEN in a simple WHERE clause. You should substitute getdate() for @testdate in the code below:

declare @testdate date = '2018-06-12' -- '2018-05-7' -- Monday -- monday of 3rd week '2018-06-11' -- tuesday '2018-06-12'
;with q1 as 
(
	select datadate, data
	,daysback = CASE WHEN ((DATEPART(dw, @testdate) + @@DATEFIRST) % 7) = 2 AND (DATEPART(week,@testdate)- DATEPART(week,DATEADD(m, DATEDIFF(m, 0, @testdate), 0))) + 1 = 3 THEN
		-- Monday of 3rd week  so we want Fri, Sat, Sun ... this date minus 3 days
		-3
	WHEN ((DATEPART(dw, @testdate) + @@DATEFIRST) % 7) = 2  THEN
		-- any other monday so we want Sat and Sun
		-2
	ELSE
		-- any other day so just yesterday
		-1 
	END 
	from #test
)
SELECT *
from q1
WHERE datadate BETWEEN dateadd(d, daysback, @testdate) AND dateadd(d, -1, @testdate)

[Edit after OP comment]
(Anyone thinking that the 3rd Monday of the month is the same as the Monday of the 3rd week are the same needs to consider how you define the first week of a month - is it the first whole week starting on a specific day - usually Sunday or Monday, or is it the 1st to 7th of a month regardless of day)

To have the 3rd Monday of the month, rather than the Monday of the 3rd week then use this in the CASE

WHEN ((DATEPART(dw, @testdate) + @@DATEFIRST) % 7) = 2 AND  ceiling(DAY(@testdate) / 7.0) = 3 THEN
		-3

Explanation: We discussed that

((DATEPART(dw, @testdate) + @@DATEFIRST) % 7) = 2

will find "Mondays". The extra bit can be explained by breaking it down:

DAY(@testdate)

is going to return the day of the month, e.g. today 18 for 18-Jun-2018. Dividing that by the number of days in a week gives you an indication of which week it is in. For example 18 / 7.0 gives 2.571428. Note the 7.0, otherwise 18/7 gives the answer 2. You could interpret that 2.571428 as "we've already had 2 whole Mondays this month and now we're part way into the next one". So by adding ceiling we can determine that "the next one" is actually the 3rd Monday (for this month).
Ok, that's probably not the best explanation but hopefully you can see what is happening.

As an aside (and a credit), this was adapted from a post by Lynn Pettis who has lots of handy stuff on her blog SQL Musings from the Desert[^] at SQL Server Central.