Determine the business day

If I have a calendar table, that has the business days of the entire year ie 2007.--How do I assign it a business day number for eg JUNE 2007 has 21 business days.. Hence on my calendar tbl, I have 21 records for June.. My question is:

How do i assign a business day number eg 6-1-07 is BUSDAY1, 6-4-07 Is Busday2, and so on..

Basicallly, I need to run an update on my calendar table, to indicate, what BUSDAY number it is ie 6-4-07 is BUSDAY 2, 6-5-07 IS Busday 3 and so on, for the entire year 2007.

Pl advise.

if you use sql server 2005 then you can use the Row_Number() ..

Code Snippet

Update MyCalendar

Set

BusinessDayNumber = 'BUSDAY' + Cast(data.Number as varchar)

From

(Select Date,Row_Number() OVER(Partition By Year,Month Order By Date) From MyCalendar) as Data

Where

Data.Date = MyCalendar.Date

if you use SQL Server 2000,

Code Snippet

Update MyCalendar

Set

BusinessDayNumber = (

Select 'BUSDAY' + Cast(Count(*) as Varchar) From MyCalendar Sub

Where

MyCalendar.Year = Sub.Year

and MyCalendar.Month = Sub.Month

and Sub.Date <= MyCalendar.Date

)

|||

Hi Tarana,

Which version of SS are you using?

-- 2005

;with cte

as

(

select

[date], BSnumber,

row_number() over(partition by year([date]), month([date]) order by [date]) as rn

from

dbo.calendar

where

IsBusinessDay = 1

)

update cte

set BDnumber = rn

-- 2000 / 2005

update dbo.calendar

set BDnumber = (

select

count(*)

from

dbo.calendar as c

where

year(c.[date]) = year(dbo.calendar.[date])

and month(c.[date]) = month(dbo.calendar.[date])

and c.[date] <= dbo.calendar.[date]

and c.IsBusinessDay = 1

)

where IsBusinessDay = 1

go

AMB

|||

You can determine the Day of the Week very easily:

selectdatepart(weekday,) from

Depending on the settings this will return 1 for sunday, 2 for Monday, 3 for Wednesday etc.

So:

Code Snippet

selectdatepart(weekday,<DateField>)-1 as [WorkDay]

from<TableName>

wheredatepart(weekday,<DateField>)between 2 and 6

|||THis code worked beautiful.sql