My sql below does not return the correct records. I need to get all current records (where last_date = null) from a table based on the condition:
first_date < (previous business day from the add_date)
first_date add_date and last_date are columns in the table
first_date is the date the record was first added to the table
add_date is the date the user updated/deleted the record.
By default, first_date = (previous business day from the add_date)
example if add_date = Feb 25 2004
previous business day = Feb 24 2004
first_date = Feb 24 2004
According to my requirement first_date should be less than Feb 24 2004
create table #prev_bus_date(prev_bus_date datetime)
insert into #prev_bus_date
select add_date from estimate_value
where last_date = null
WHEN DATEPART(dw, getdate()) IN (1,7)
THEN "It's not a business day"
ELSE 'The current business day is ' + DATENAME(dw, getdate())
+ char(10) + 'The previous business day was '
+ CASE DATEPART(dw, getdate())
The above takes care of weekends. But I am not applying it correctly. For US HOLIDAYS, I have a history table I can look at that stores HOLIDAY dates for a calendar year.