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

Database

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?


Accepted Solutions
Highlighted
Senior Apprentice

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
Highlighted
Senior Apprentice

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
Senior Apprentice

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

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)