How to get Sysdate -3 when today is Monday and Sysdate-1 on any other weekday

[DarcN8]

I am trying to extract data out of an SQL server using ODBC where i usually need Prior day information, but in case today is monday i would need Friday's information, how can i add this intrecacy in my SQL query so i dont have to pull in huge amount of data to add measures.

Here is what my SQL query looks like right now, im pulling Sysdate-1 and sysdate-3 information, but since the data base has all the historical data it it take significant amount of time.

SELECT SQLServer.Table1.Sequence, SQLServer.Table1.name, SQLServer.Table1.ID, SQLServer.Table2.lastprice, SQLServer.Table3.Currentprice, SQLServer.Table1.statuscode,
FROM (((SQLServer.Table1 LEFT JOIN SQLServer.Table4 ON SQLServer.Table1.Sequence = SQLServer.Table4.Sequence) LEFT JOIN SQLServer.Table2 ON SQLServer.Table1.Sequence = SQLServer.Table2.Sequence) LEFT JOIN SQLServer.Table3 ON SQLServer.Table1.Sequence = SQLServer.Table3.Sequence) LEFT JOIN SQLServer.Table5 ON SQLServer.Table2.spcode = SQLServer.Table5.spcode
WHERE (((SQLServer.Table1.statuscode)='A'and (SQLServer.Table3.Currentprice) = sysdate-1 and (SQLServer.Table3.tpdate) = sysdate-3))

[DarcN8]

Thanks for your response. Unfortunately this did not work because it is subracting the date from tpdate instead of filtering the particular date. i also preferably want to do the filtering in the SQL itself or the M query.

[DarcN8 · Accepted solution]

I found what i was looking for  SQLServer.Table3.tpdate = case when DAYNAME(CURDATE() - 1) = 'Monday' then CURDATE() - 3 else CURDATE() - 1 end

[amitchandak]

@DarcN8 , refer if this can help

Power BI Workday vs Last Workday- https://youtu.be/MkYLT_GYIbM

If you need a calculation

if(weekday([Date],2) =1, [Date],-3, [Date]-1)