SQL Query Date time Suggestion

Question

I have a SQL which gets data until yesterday by using where clause as " Where tpd."RELATIVE_GLOBAL_DAY" = -1 " The db is defined as such that when I want to get todays data, I will have use 0 and likewise for yesterdays data, I can use -1 and for day before -2.

My query works fine except on monday. As per the query it gives me data of Sunday which I don't want. Instead I need data from last monday to friday all together but when its Tuesday I only need data for previous day which in this case should be Monday.

How do I put this condition in the where clause.

Thanks

tpd."RELATIVE_GLOBAL_DAY" = -1

Answer 1

As long as you have a column which has the current weekday or the current date, you can use OR to take care of these two situations. For example:

WHERE (
    (day = 1 AND tpd."RELATIVE_GLOBAL_DAY" IN (-3,-4,-5,-6,-7)) OR
    (day > 1 AND tpd."RELATIVE_GLOBAL_DAY" = -1)
)

Here I am assuming you have a column day which indicates the current day of the week. If you do not, but you have a column date which indicates the current date, replace day above with DATEPART('weekday',date).