Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 195

# How do i use function to calculate the month period to weeks

for instance
Feb 2009 weeks equal week1 = 2 Feb to 6 Feb, Week2 = 9 Feb to 13 Feb  week3 = 16 to 20 and week4 =  23 Feb to 27 Feb

March 2009 weeks equal week1 = 2 Mar to 6 Mar week2 = 9 Mar to 14 Mar week3 = 16 Mar to 20 Mar week4 = 23 Mar to 27 Mar and week5 30 Mar to 3 Apr

when i give a month parameter. the function should return weeks with date period.
0
wanlijun
• 5
• 4
1 Solution

OK, often resort to a Calendar table for this type of work,

unless it follows a pattern like 5,4,4

or, there is some other rule like 'first monday' to 'last monday'

If you can give some insight into possible rules, can give you some answers... and write the funtions...
0

Commented:
create proc getweeks(@Mon int, @Year int)
as
begin

declare @Date datetime
set @Date = convert(datetime,CONVERT(varchar(2),@Mon)+'/1/'+ CONVERT(varchar(4),@Year))

declare @i int
set @i = 0
while @i < 5
begin
if datepart(m,DATEADD(wk, DATEDIFF(wk,0,@Date), 0)+@i*7) = 2
select 'Week '+ convert(char(1),@i+1),convert(varchar(12),DATEADD(wk, DATEDIFF(wk,0,@Date), 0)+@i*7),
set @i = @i +1
end

end

getweeks 2,2009
0

I was thinking more of a function returning multi-weeks, or, a calendar table if being used for joins and in queries.
0

Author Commented:
is that possible to have just 4 column with details of period in the column?
0

Yes...

But what about my questions above ? and one more for you, how do you want to use the results of getting weeks from providing a month ?
0

Author Commented:
the function only works for 02,2009 what about other date?
0

Author Commented:
I will use this function to create a chart in Crystal Report. the chart will show different catalogue of stocks sells in each week for give a month parameter.
0

Well, still not quite sure of how you want to use it, but now understand where you want to use it, just not how.

Do you plan to use it as part of a query ? do you plan to use it to join to a data source to get the week ? Or are you just needing a query to give you the weeks ?

e.g. Using a CTE query (and does not have to be, it is just an example) we can dynamically generate a while pile of dates - that is not the problem. The problem is more realistically what mechanism or delivery method would you prefer to use from crystal.  Examples could be a calendar table, a view, a dynamic query, a function (as in return the week commencing date with the datarows), or, are you saying you want to create a function or expression in Crystal itself ?

See below an example of a query with excrutiatingly long hand method for w/c dates regardless of first daate setting in the database, and that can be significantly tidied as well...

To run it, open a query window (say in SSMS) and paste the code then run.

``````;with cte_weeks as
(select 0 as weekno, 2008 as [wc_year], 01 as [wc_month], 0 as wc, case when 1 + 7 - (@@datefirst + datepart(dw,'20080101') - 1) < 0 then dateadd(ww,1,(dateadd(dd,1 + 7 - (@@datefirst + datepart(dw,'20080101') - 1),'20080101'))) else dateadd(dd,1 + 7 - (@@datefirst + datepart(dw,'20080101') - 1),'20080101') end - 7 as Monday_Week_Commencing_Date, getdate() as Monday_Week_Ending_Date
union all
from cte_weeks where wc_year in (2008,2009,2010) )  -- or maybe = year(dateadd(dd,7,Monday_Week_Commencing_Date)) )
select * from cte_weeks
where weekno > 0
--AND WC_YEAR = 2009 and WC_MONTH = 2
option (maxrecursion 0)
``````
0

Author Commented:
I just need a query(store procedure) to give me the weeks when i give a month parameter. example 03 2009. the return will be week1 = 2 Mar to 6 Mar week2 = 9 Mar to 14 Mar week3 = 16 Mar to 20 Mar week4 = 23 Mar to 27 Mar and week5 30 Mar to 3 Apr. thanks for your help
0

Well, that is very close to what emes already gave you....  you just call it with what ever period you want e.g. getweeks 3,2009  or getweeks 1,2009

But, anyway, here is one for you
``````
-- step 1 -- a once-off task to create the stored procedure. Once there use as often as you like. two parameters first month then year

create proc usp_week_labels(@mth INT,@yr INT)
as
begin

declare @dt datetime
declare @wk1 varchar(20)
declare @wk2 varchar(20)
declare @wk3 varchar(20)
declare @wk4 varchar(20)
declare @wk5 varchar(20)

if @mth between 1 and 13 and @yr > 2000
begin

set @dt = convert(datetime, (convert(varchar,@yr) + right('00'+convert(varchar,@mth),2) + '01'), 112)
set @dt = case when 1 + (7 - (@@datefirst + datepart(dw,@dt) - 1)) < 0 then dateadd(ww,1,(dateadd(dd,1 + (7 - (@@datefirst + datepart(dw,@dt) - 1)),@dt))) else dateadd(dd,1 + (7 - (@@datefirst + datepart(dw,@dt) - 1)),@dt) end

set @wk1 = convert(varchar(6),@dt,106) + ' to ' + convert(varchar(6),dateadd(dd,5,@dt),106)
end

select @wk1 as Week1,@wk2 as Week2,@wk3 as Week3,@wk4 as Week4,@wk5 as Week5

end;
go

-- step 2 is to simply use it two examples below, one for feb and a more formal syntax of running for march, and the most formal syntax for April (all the same thing just different months)

usp_week_labels 2,2009
go

-- slightly more formal syntax

exec ..dbo.usp_week_labels 3,2009
go

-- absolute most formal syntax for running a stored procedure

execute <dbname>.dbo.usp_week_labels @mth=4,@yr=2009                            -- replace <dbname> with your actual database name
go
``````
0

## Featured Post

• 5
• 4
Tackle projects and never again get stuck behind a technical roadblock.