Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Unanswered: previous business date

    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)

    where :

    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())
    WHEN 2
    THEN 'Friday'
    ELSE DATENAME(dw,DATEADD(dd,-1,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.

    Thanks in advance !

  2. #2
    Join Date
    Oct 2003
    I suggest that you:
    • SELECT DISTINCT first_date WHERE last_date IS NULL
    • Loop through this result-set calculating the last_business_date based on first_date.
    • Loop through this set once again, making any corrections for holidays.
    • Use (or join to) this [temporary...] table to prepare your final report.

    In this way, you will be able to make the calculations only one time, for each date-value, no matter how many input-records share a particular date.
    ChimneySweep(R): fast, automatic
    table repair at a click of the

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts