0

I have a table with date column (date in string format yyyyMMdd). My requirement is to design a logic to fetch data from the table where "date column value equals to the date of the 15th previous working day" (excluding only Saturdays and Sundays) without using a UDF or a shell script. For example it is 21st Feb 2020 today; the logic should produce an output: 20200203.

Sudhanshu
  • 1
  • 1
  • 3
  • Three questions: 1, If the first previous working day would be Feb 20th, how do you count the 15th previous day as Feb 3rd? 2, What behavior do you want when the input date is a weekend? 3, Please specify if you need to exclude holidays (which would make this much simpler because then we would need a calendar table)? – markwusinich Feb 21 '20 at 15:05
  • @markwusinich 1. I possibly did not explain it properly...sorry, I need to count current date as 1st working day and go back to the 15th. 2. I want to skip the dates which fall on a Saturday or a Sunday. 3. My requirement is to ignore ONLY Saturdays and Sundays, holidays are not needed to be considered. Currently I have implemented {where date =(date_format(date_sub(CURRENT_DATE,15),'yyyyMMdd')} but this calculates based on all calendar dates including the weekends. – Sudhanshu Feb 24 '20 at 05:36

1 Answers1

0

Assuming you actually mean the 14th previous working day based on your example, and you are ignoring holidays, it is just a date_sub function with a case statement for day of the week.

case from_unixtime(unix_timestamp(event_date,'yyyyMMdd'),'u')
  when 1 then regexp_replace(date_sub(from_unixtime(unix_timestamp(event_dt,'yyyymmdd' )),20),'-','')
  when 2 then regexp_replace(date_sub(from_unixtime(unix_timestamp(event_dt,'yyyymmdd' )),20),'-','')
  when 3 then regexp_replace(date_sub(from_unixtime(unix_timestamp(event_dt,'yyyymmdd' )),20),'-','')
  when 4 then regexp_replace(date_sub(from_unixtime(unix_timestamp(event_dt,'yyyymmdd' )),20),'-','')
  when 5 then regexp_replace(date_sub(from_unixtime(unix_timestamp(event_dt,'yyyymmdd' )),18),'-','')
  when 6 then regexp_replace(date_sub(from_unixtime(unix_timestamp(event_dt,'yyyymmdd' )),18),'-','')
  when 7 then regexp_replace(date_sub(from_unixtime(unix_timestamp(event_dt,'yyyymmdd' )),19),'-','')
end as new_date

This assumes Sat/Sun should be treated like Monday, If Sat/Sun should be like Friday then make then use 19, 20.

If you need to account for holidays, then you need to create a calendar table with every day, and note which days are holidays, and then it is a join to the table and a some more logic that could be figured out if this is the case.

markwusinich
  • 108
  • 7
  • My requirement is to ignore ONLY Saturdays and Sundays, holidays are not needed to be considered. Currently I have implemented {where date =(date_format(date_sub(CURRENT_DATE,15),'yyyyMMdd')} but this calculates based on all calendar dates including the weekends. – Sudhanshu Feb 24 '20 at 05:51