Database
Highlighted
Fan

## IF... THEN... depending on day of week (run different code)

Background: I'm building a rather simple table that is simply a select * from another table that needs to select the last 3 day's worth of data if it is a Monday and the last one day's worth of data if it's Tuesday-Friday.

Problem: I'm not sure I understand how to do this using an IF... THEN statement.

Code attempted:

IF DAY_OF_WEEK = 2

THEN

SELECT * FROM TABLE WHERE DATE BETWEEN CURRENT_DATE-3 AND CURRENT_DATE

ELSE

SELECT * FROM TABLE WHERE DATE BETWEEN CURRENT_DATE-1 AND CURRENT_DATE

END IF

This clearly doesn't work but demonstrates the logic of what I am trying to do. Any thoughts on how I might tackle this?

Tags (3)

Accepted Solutions

## Re: IF... THEN... depending on day of week (run different code)

Hi,

You don't say what happens if the current date is Saturday or Sunday, I've assumed  'yesterday's data:

```SELECT *
FROM vt1
WHERE txn_dt BETWEEN CASE
WHEN td_day_of_week(DATE) = 2 THEN DATE-3
WHEN td_day_of_week(DATE) BETWEEN 3 AND 5 THEN DATE-1
ELSE DATE-1
end
AND CURRENT_DATE
```

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
3 REPLIES 3

## Re: IF... THEN... depending on day of week (run different code)

Hi,

You don't say what happens if the current date is Saturday or Sunday, I've assumed  'yesterday's data:

```SELECT *
FROM vt1
WHERE txn_dt BETWEEN CASE
WHEN td_day_of_week(DATE) = 2 THEN DATE-3
WHEN td_day_of_week(DATE) BETWEEN 3 AND 5 THEN DATE-1
ELSE DATE-1
end
AND CURRENT_DATE
```

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

## Re: IF... THEN... depending on day of week (run different code)

Sorry, I should also have said that in my example column 'txn_dt' replaces the column 'DATE' in your initial pseudo-code.

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Fan

## Re: IF... THEN... depending on day of week (run different code)

Perfect. I need it BETWEEN 3 AND 6 to pick up Friday and have added DATE+1 to the ELSE so that it doesn't pick anything up on Saturday and Sunday but apart from that does exactly what it says on the tin.

Many thanks,

James

Tags (1)