CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Teeto Munther
Teeto Munther

Reputation: 13

Select rows 2 days old except weekend

I'm having an issue retrieving some transactions from my table, the idea is i wanna delete all transaction the users made since 2 days but i wanna exclude the weekends from counts (weekend here is Friday and Saturday), so far i can get the 2 days old records but no weekend exclude

SELECT c1
FROM my_table
WHERE TRANS_DATE <= (TRUNC (SYSDATE) - 3)..

Example: a user made his request on Tuesday i have to delete the request on Thursday,, but if he made it on Thursday i have to delete it on Monday..

any ideas? thanks all

Upvotes: 1

Views: 117

Answers (2)

Jayvee
Jayvee

Reputation: 10875

Try determining the day of the week with a case expression and subtracting accordingly, for example:

SELECT c1
FROM my_table
WHERE 
TRANS_DATE <= (
CASE WHEN TO_CHAR(SYSDATE, 'fmDay', 'NLS_DATE_LANGUAGE = American' ) = 'Monday' then TRUNC (SYSDATE) - 5
     ELSE TRUNC (SYSDATE) - 3 END
);

Upvotes: 0

Rzbel
Rzbel

Reputation: 11

SELECT c1
FROM my_table
WHERE TRANS_DATE >= sysdate - decode(to_number(to_char(sysdate -1,'D')), 1,4, 6,3, 7,4, 2)

First day is Sunday If previous day is Sunday or Saturday then we have two days off If previous day is Friday then we have one day off plus two days

Upvotes: 1

Related Questions