cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question
cshora
Observer
Member since: ‎10-30-2023
‎11-08-2023
Community Statistics
Posts 1
Solutions 0
Karma Given 0
Karma Received 0
Member Since ‎10-30-2023
Activity Feed
Topics I've Started
No posts to display.
View All

Re: Check current date against lookup

by in Splunk Search
‎10-30-2023 07:19 AM
‎10-30-2023 07:19 AM
For those who don't like the idea of a lookup file, due to maintenance,  I created a SQL query, which will calculate the date values (see below). NOTE:  This SQL is written for Oracle, and may need to be modified for your purposes.   select Holiday /* CALCULATE HOLIDAY VALUES ... RAW *********************************** */ ,to_char(date_val,'dd-Mon-yyyy') date_val ,to_char(date_val,'Dy') day_val /* CALCULATE FEDERAL HOLIDAY VALUES (IF SHIFTED TO ANOTHER DAY) ******* */ ,case when to_char(date_val, 'Dy') in ('Sat') then 'prev day' when to_char(date_val, 'Dy') in ('Sun') then 'next day' else '' end as fed_calc ,case when to_char(date_val, 'Dy') in ('Sat') then date_val-1 when to_char(date_val, 'Dy') in ('Sun') then date_val+1 else date_val end as fed_recog_date ,case when to_char(date_val, 'Dy') in ('Sat') then to_char(date_val-1,'Dy') when to_char(date_val, 'Dy') in ('Sun') then to_char(date_val+1,'Dy') else to_char(date_val, 'Dy') end as fed_recog_day from (/* CURRENT YEAR -2 ******************************************************************************************************************** */ SELECT 'New Years' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 0) + 0 as date_val FROM DUAL union SELECT 'M.L.K Jr.' as Holiday, NEXT_DAY( TRUNC(sysdate+(365*-2),'YYYY') - 1,'MONDAY') +14 as date_val FROM DUAL union SELECT 'President''s' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 1)- 1,'MONDAY') +14 as date_val FROM DUAL union SELECT 'Memorial' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 5)- 1,'MONDAY') - 7 as date_val FROM DUAL union SELECT 'Juneteenth' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 5)+18 as date_val FROM DUAL where sysdate+(365*-2) > '01-JAN-2022' union SELECT 'Independence' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 6) + 3 as date_val FROM DUAL union SELECT 'Labor' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 8)- 1,'MONDAY') as date_val FROM DUAL union SELECT 'Columbus' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 9)- 1,'MONDAY') + 7 as date_val FROM DUAL union SELECT 'Veterans' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'),10) +10 as date_val FROM DUAL union SELECT 'Thanksgiving' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'),10)- 1,'THURSDAY') +21 as date_val FROM DUAL union SELECT 'Christmas' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'),11) +24 as date_val FROM DUAL union /* CURRENT YEAR -1 ******************************************************************************************************************** */ SELECT 'New Years' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 0) + 0 as date_val FROM DUAL union SELECT 'M.L.K Jr.' as Holiday, NEXT_DAY( TRUNC(sysdate+(365*-1),'YYYY') - 1,'MONDAY') +14 as date_val FROM DUAL union SELECT 'President''s' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 1)- 1,'MONDAY') +14 as date_val FROM DUAL union SELECT 'Memorial' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 5)- 1,'MONDAY') - 7 as date_val FROM DUAL union SELECT 'Juneteenth' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 5)+18 as date_val FROM DUAL where sysdate+(365*-1) > '01-JAN-2022' union SELECT 'Independence' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 6) + 3 as date_val FROM DUAL union SELECT 'Labor' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 8)- 1,'MONDAY') as date_val FROM DUAL union SELECT 'Columbus' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 9)- 1,'MONDAY') + 7 as date_val FROM DUAL union SELECT 'Veterans' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'),10) +10 as date_val FROM DUAL union SELECT 'Thanksgiving' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'),10)- 1,'THURSDAY') +21 as date_val FROM DUAL union SELECT 'Christmas' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'),11) +24 as date_val FROM DUAL union /* CURRENT YEAR -0 ******************************************************************************************************************** */ SELECT 'New Years' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 0) + 0 as date_val FROM DUAL union SELECT 'M.L.K Jr.' as Holiday, NEXT_DAY( TRUNC(sysdate+(365* 0),'YYYY') - 1,'MONDAY') +14 as date_val FROM DUAL union SELECT 'President''s' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 1)- 1,'MONDAY') +14 as date_val FROM DUAL union SELECT 'Memorial' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 5)- 1,'MONDAY') - 7 as date_val FROM DUAL union SELECT 'Juneteenth' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 5)+18 as date_val FROM DUAL where sysdate+(365* 0) > '01-JAN-2022' union SELECT 'Independence' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 6) + 3 as date_val FROM DUAL union SELECT 'Labor' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 8)- 1,'MONDAY') as date_val FROM DUAL union SELECT 'Columbus' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 9)- 1,'MONDAY') + 7 as date_val FROM DUAL union SELECT 'Veterans' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'),10) +10 as date_val FROM DUAL union SELECT 'Thanksgiving' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'),10)- 1,'THURSDAY') +21 as date_val FROM DUAL union SELECT 'Christmas' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'),11) +24 as date_val FROM DUAL union /* CURRENT YEAR +1 ******************************************************************************************************************** */ SELECT 'New Years' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 0) + 0 as date_val FROM DUAL union SELECT 'M.L.K Jr.' as Holiday, NEXT_DAY( TRUNC(sysdate+(365*+1),'YYYY') - 1,'MONDAY') +14 as date_val FROM DUAL union SELECT 'President''s' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 1)- 1,'MONDAY') +14 as date_val FROM DUAL union SELECT 'Memorial' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 5)- 1,'MONDAY') - 7 as date_val FROM DUAL union SELECT 'Juneteenth' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 5)+18 as date_val FROM DUAL where sysdate+(365*+1) > '01-JAN-2022' union SELECT 'Independence' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 6) + 3 as date_val FROM DUAL union SELECT 'Labor' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 8)- 1,'MONDAY') as date_val FROM DUAL union SELECT 'Columbus' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 9)- 1,'MONDAY') + 7 as date_val FROM DUAL union SELECT 'Veterans' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'),10) +10 as date_val FROM DUAL union SELECT 'Thanksgiving' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'),10)- 1,'THURSDAY') +21 as date_val FROM DUAL union SELECT 'Christmas' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'),11) +24 as date_val FROM DUAL union /* CURRENT YEAR +2 ******************************************************************************************************************** */ SELECT 'New Years' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 0) + 0 as date_val FROM DUAL union SELECT 'M.L.K Jr.' as Holiday, NEXT_DAY( TRUNC(sysdate+(365*+2),'YYYY') - 1,'MONDAY') +14 as date_val FROM DUAL union SELECT 'President''s' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 1)- 1,'MONDAY') +14 as date_val FROM DUAL union SELECT 'Memorial' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 5)- 1,'MONDAY') - 7 as date_val FROM DUAL union SELECT 'Juneteenth' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 5)+18 as date_val FROM DUAL where sysdate+(365*+2) > '01-JAN-2022' union SELECT 'Independence' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 6) + 3 as date_val FROM DUAL union SELECT 'Labor' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 8)- 1,'MONDAY') as date_val FROM DUAL union SELECT 'Columbus' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 9)- 1,'MONDAY') + 7 as date_val FROM DUAL union SELECT 'Veterans' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'),10) +10 as date_val FROM DUAL union SELECT 'Thanksgiving' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'),10)- 1,'THURSDAY') +21 as date_val FROM DUAL union SELECT 'Christmas' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'),11) +24 as date_val FROM DUAL ) calc order by date_val ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎11-08-2023 10:14 AM