Hi all,

     New to this forum...My client is asking me to create filter "Last 30 Business Days" so that they will use them in Business Objects Reports.When they use this filter,they should get last 30 business days data in the report.It should skip weekends...Means excluding weekends it should be 30 days...
         I am wondering how to create this query which recognizes weekends and skips them and take last 30 days...They may even ask for 90 days and so on...

Can someone help me to get this query in SQL Server ...

Thanks,
Nath

Try this. this uses a table as an example from the Northwind data base:

SELECT orderdate from orders where DATEDIFF(day, orderdate, getdate()) <=30 and datepart(dw, orderdate) not in (1,7) order by orderdate desc

getdate() = current date
1= sunday, 7 =saturday

Just modify to use your tables.

Hi ,

  I tried with your query but it is seeing only weekends in last 30 days and skips them but that is not with my case.I need to get data for Last 30 business days.

Thanks,
Nath

This UDF (User Defined Function) might come to your rescue...(Not my work..original author Jeff Smith I found it in one of the online forums)

Create function WorkDay(@CurrDate datetime, @DaysToAdd int)
returns datetime
as
begin
    declare @WeeksToAdd int;
    declare @dw int;

    set @WeeksToAdd = (@DaysToAdd / 5) * 7
    set @DaysToAdd = @DaysToAdd % 5

    set @CurrDate = DateAdd(dd,@WeeksToAdd + @DaysToAdd, @CurrDate)
    set @dw = datepart(dw,@CurrDate)

    if sign(@DaysToAdd) < 0
        begin
        if @dw = 1 set @CurrDate = @CurrDate - 2
        if @dw = 7 set @CurrDate = @CurrDate - 1
        end
    else
        begin
        if @dw = 1 set @CurrDate = @CurrDate + 1
        if @dw = 7 set @CurrDate = @CurrDate + 2
        end

return @CurrDAte
end

Note: The function assumes that the starting date you specify will be a week day.


DECLARE @LastBusinessDay Datetime
SELECT @LastBusinessDay = dbo.WorkDay(GetDATE(),-30)

It should return Feb 7 2005 12:00AM

Now modify your query, you might have to use stored procedure for this