Learn how to a build a cloud-first strategyRegister Now

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

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
Asked:
wanlijun
  • 5
  • 4
1 Solution
 
Mark WillsTopic AdvisorCommented:
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
 
EmesCommented:
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),
CONVERT(varchar(12),DATEADD(wk, DATEDIFF(wk,0,@Date), 0)+@i*7+4)
set @i = @i +1
end

end

getweeks 2,2009
0
 
Mark WillsTopic AdvisorCommented:
I was thinking more of a function returning multi-weeks, or, a calendar table if being used for joins and in queries.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
wanlijunAuthor Commented:
is that possible to have just 4 column with details of period in the column?
0
 
Mark WillsTopic AdvisorCommented:
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
 
wanlijunAuthor Commented:
the function only works for 02,2009 what about other date?
0
 
wanlijunAuthor 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
 
Mark WillsTopic AdvisorCommented:
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
 select case when wc_year = year(dateadd(dd,7,Monday_Week_Commencing_Date)) then weekno + 1 else 1 end , year(dateadd(dd,7,Monday_Week_Commencing_Date)), month(dateadd(dd,7,Monday_Week_Commencing_Date)), case when wc_month = month(dateadd(dd,7,Monday_Week_Commencing_Date)) then wc + 1 else 1 end, dateadd(dd,7,Monday_Week_Commencing_Date), dateadd(dd,14,dateadd(ms,-10,Monday_Week_Commencing_Date))
 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) 

Open in new window

0
 
wanlijunAuthor 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
 
Mark WillsTopic AdvisorCommented:
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)
   set @wk2 = convert(varchar(6),dateadd(dd,07,@dt),106) + ' to ' + convert(varchar(6),dateadd(dd,5,dateadd(dd,07,@dt)),106)
   set @wk3 = convert(varchar(6),dateadd(dd,14,@dt),106) + ' to ' + convert(varchar(6),dateadd(dd,5,dateadd(dd,14,@dt)),106)
   set @wk4 = convert(varchar(6),dateadd(dd,21,@dt),106) + ' to ' + convert(varchar(6),dateadd(dd,5,dateadd(dd,21,@dt)),106)
   if month(dateadd(dd,28,@dt)) = @mth set @wk5 = convert(varchar(6),dateadd(dd,28,@dt),106) + ' to ' + convert(varchar(6),dateadd(dd,5,dateadd(dd,28,@dt)),106) else set @wk5 = ''
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

Open in new window

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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