Last working day of the previous month

[ashab02]

Hello
I have a query which relies on a criteria, this criteria is the last working day of the previous month, so for example throughout this month I want to pickup the value 30/11/2007 as it is the last working day of the previous month.

I currently use a dateadd function but its reliant on me changing the criteria every month to reflect the last working day of the previous month.

I was wondering if anyone had a solution to this?

Shab

 

[PDreyer]

What do you consider as non working days (Saturdays and Sundays)? Do you have a table for public holidays?

 

[ashab02]

Hello

Yes I only consider working days from Monday to Friday. No I dont have a table with public holidays.
Do you have a solution?

 

[PDreyer]

If you have a calendar table that indicate whether a day is a Non working or Working day then you can e.g.

select max(caldt) from calendar
where caldt between dateadd(mm,datediff(mm,0,getdate())-1,0)
            and  dateadd(dd,-1,dateadd(mm,datediff(mm,0,getdate()),0))
  and daytype='W'

 

[ashab02]

so this table, would it need an entry for everyday of the year?

 

[PDreyer]

Without a calendar or holiday table, try this

select max(dt) from(
select dateadd(dd,-number-1, dateadd(mm,datediff(mm,0,getdate()),0)) dt
from master..spt_values numbers
where type='P' and number<7
)cal1
where datepart(dw,dt) not in (1,7)

 

[ashab02]

thanks mate, looks like what I need but will only be able to test fully next month but looking at it all seems ok with this code
you truly are a coding king
Tnaks for your help
Shab