How to count the occurences of weekdays in a year

• kam7395

  Old Hand

  Points: 395

  More actions

  June 12, 2009 at 9:52 am

  #210548

  Hi Everyone,

  I am new to SQL Server 2000, Please somebody let me know how to "count the occurences of weekdays in a year" or else please provide me the code.

  Please help me out.

  Thanks in advance!!

• J-F Bergeron

  SSCrazy Eights

  Points: 8593

  More actions

  June 12, 2009 at 10:19 am

  #1008786

  kam7395 (6/12/2009)

  ---

  Hi Everyone,

  I am new to SQL Server 2000, Please somebody let me know how to "count the occurences of weekdays in a year" or else please provide me the code.

  Please help me out.

  Thanks in advance!!

  Here I build this for you, hope that helps!

  /*SELECT top 1000000 identity(INT,1,1) AS N

  INTO dbo.Tally

  FROM master.sys.syscolumns sc1

  CROSS JOIN master.sys.syscolumns sc2

  CREATE UNIQUE CLUSTERED INDEX Clus_Tally_N ON dbo.Tally

  (

  N

  ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  GO*/

  DECLARE @inputDate1 DATETIME,

  @InputDate2 DATETIME

  SET @InputDate1 = '2009/06/11'

  SET @InputDate2 = '2009/06/16'

  SELECT datediff(d,@inputDate1,@InputDate2);

  WITH Days(N)

  AS (SELECT TOP 1000 N

  FROM dbo.Tally),

  Dates

  AS (SELECT dateadd(d,N

  - 1,@InputDate1) AS DATE,

  datepart(dw,dateadd(d,N

  - 1,@InputDate1)) AS DayOfWeek

  FROM Days

  WHERE N

  - 1 <= datediff(d,@inputDate1,@InputDate2))

  SELECT sum(CASE

  WHEN DayOfWeek BETWEEN 2 AND 6

  THEN 1

  END) AS TotalDaysInWeek

  FROM Dates

  Cheers,

  J-F

• J-F Bergeron

  SSCrazy Eights

  Points: 8593

  More actions

  June 12, 2009 at 10:20 am

  #1008787

  Oh Geez... SQL 2000, Sorry, my solution will not work, it's for 2005.

  Sorry about that... I should've read the entire post..

  Cheers,

  J-F

• LutzM

  SSC Guru

  Points: 107049

  More actions

  June 12, 2009 at 10:24 am

  #1008793

  Please don't cross post!

  for reference see http://www.sqlservercentral.com/Forums/FindPost733984.aspx

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• kam7395

  Old Hand

  Points: 395

  More actions

  June 12, 2009 at 3:18 pm

  #1008916

  Thank You!