function or procedure for dynamic generation of weeks for given month period.

Question

Post reply

function or procedure for dynamic generation of weeks for given month period.

purushotham.k9

SSCommitted

Points: 1992
More actions
May 11, 2009 at 4:10 am

#133858

function or procedure for dynamic generation of weeks for given month period. lets take if i give range march to may i have to get the number of weeks like mar1week(01/03-07/09)....May4wee(23/05-30/05) like that

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What have you tried so far?

Did you look into DATEADD/DATEPART functions in BOL?

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]

purushotham.k9 SSCommitted Points: 1992 More actions · Answer 2

alter function [dbo].ReturnWeek() returns @testtable table (week int)

as

begin

declare @test-2 int

declare @i int

select @test-2= datediff(wk,'2009-03-01',getdate()+1)

set @i=1

while(@i<=@test)

begin

insert into @testtable values (@i)

set @i=@i+1

end

return

end

this is what i have done so far

LutzM SSC Guru Points: 107049 More actions · Answer 3

Hi,

you should consider using a calendar table.

A start point would be the code posted by GSquared in http://www.sqlservercentral.com/Forums/FindPost513748.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]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I agree with the calendar table idea, but I still think you should answer the questions I posted on the other thread you started with the same question, http://www.sqlservercentral.com/Forums/Topic714216-9-1.aspx. Just post the answers here as everyone will be watching this thread now.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question