question

Here's a way:

SELECT TOP 365
   IDENTITY(INT, 1, 1) AS n
INTO
   #nums
FROM
   sys.all_objects
   SELECT 
       -- this gets the first day of the month so we have something to group on
       DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, n, '1/1/2008')), 0) AS month_Start, 
       COUNT(*)
   FROM
       #nums
   WHERE
        -- this gets only weekdays.
       DATEPART(weekday, DATEADD(DAY, n, '1/1/2008')) BETWEEN 
              CASE @@DateFirst
                   WHEN 7 THEN 2
                   WHEN 6 THEN 3
                   WHEN 5 THEN 4
                   WHEN 4 THEN 5
                   WHEN 3 THEN 6
                   WHEN 2 THEN 7
                   ELSE 1
              END AND  CASE @@DateFirst
                   WHEN 7 THEN 6
                   WHEN 6 THEN 7
                   WHEN 5 THEN 1
                   WHEN 4 THEN 2
                   WHEN 3 THEN 3
                   WHEN 2 THEN 4
                   ELSE 5
              END
    GROUP BY
        DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, n, '1/1/2008')), 0)
    ORDER BY
        month_start 
DROP TABLE #nums

You can read this post on my blog to see why I am using @@DateFirst. Obviously this does not take holidays into account. You'd need a calendar table for that.

Another way is to use a numbers table and a little date math.

DECLARE @dt DATETIME
SET @dt = '2009-09-22' 
SELECT 
    DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0) AS Mnth,
    SUM(CASE WHEN DATENAME(WEEKDAY,DATEADD(DAY,Number,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)))
    		IN('Monday','Tuesday','Wednesday','Thursday','Friday') THEN 1
    	  ELSE 0 
    	  END) AS Cnt
FROM [master].dbo.[spt_values]
WHERE 
    type = 'P'
    AND DATEADD(DAY,Number,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0))
    	< DATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0))

**SELECT to_char(Mdate, 'dy'), To_char(Mdate, 'DD-Mon-YYYY') FROM (select To_date (Level || '/' || '&month' || '/' || &year,'DD-Mon-YYYY') Mdate from dual connect by level <= last_day(to_date('01-' || '&month' || '-' || &year,'DD-Mon-YYYY')) - to_date('01-' || '&month' || '-' || &year, 'DD-Mon-YYYY') + 1) WHERE to_char(Mdate, 'dy') <> 'sun' AND to_char(Mdate, 'dy') <> 'sat';** *In this Query The Month input should be in mon Format like : jan,Feb,Mar,Apr And Year like YYYY : 2012,2011* *it may ask for input 3 times depends on SQL operator* ***ALL D BEST.......***

I know this is an old thread but what I thought was an obvious solution did not rate a mention. You'll be coming back to this kind of thing time and time again, so why not do all the calculations in advance and store the results in a permanent calendar table (a table of dates - one date per row). Need local public holidays? Add a column that flags them (and even a column that names them if you like). Need phases of the moon? Again, throw in another column. A flag for weekends? First and last day of the month? Week starting date? Financial quarter? Anything that relates to dates will benefit from this approach and make your code much easier to read.