SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 for loop list of Array in stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jimmy2090
Starting Member

Malaysia
20 Posts

Posted - 08/19/2014 :  03:54:09  Show Profile  Reply with Quote
Dear all,

i would like to write a store prodecure to return a month:
my output:
Wk1 = July
Wk2 = July
Wk3 = July
Wk4 = July
Wk5 = Aug

and so on..

then i create list of array like below:
The counter for insert the week one by one


DECLARE @TotalWeek INT, @counter INT
DECLARE @WeekNo varchar, @Month varchar

SET @WeekNo = '4,9,14,18,22,27,31,35,40,44,48,53' 
--this is weekno,if less than 4, month is july, lf less than 9, month is august and so on
SET @TotalWeek = 53

SET @counter = 1

WHILE @counter <= @TotalWeek 
BEGIN

--create a for loop for the weekno (How to create the for loop?)
if @counter <= @weekNo(1)
@Month = 'July'
else  @counter <= @weekNo(2)
@Month = 'August'
End


SET @counter = @counter + 1;
End


Edited by - jimmy2090 on 08/19/2014 03:56:23

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/19/2014 :  04:41:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What constitutes the beginning of a week for you?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jimmy2090
Starting Member

Malaysia
20 Posts

Posted - 08/19/2014 :  04:44:03  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

What constitutes the beginning of a week for you?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



our company have different week and calendar.
Go to Top of Page

jimmy2090
Starting Member

Malaysia
20 Posts

Posted - 08/19/2014 :  05:02:22  Show Profile  Reply with Quote
Below is my coding:
i would like to change it to for loop the array list.
WHILE @counter <= @TotalWeek 
BEGIN


if @counter <= '4'
set @Month = 'Jul'

if @counter > '4' and @counter <= '9'
set @Month = 'Aug'

if @counter > '9' and @counter <= '14'
set @Month = 'Sep'

if @counter > '14' and @counter <= '18'
set @Month = 'Oct'

if @counter > '18' and @counter <= '22'
set @Month = 'Nov'

if @counter > '22' and @counter <= '27'
set @Month = 'Dec'

if @counter > '27' and @counter <= '31'
set @Month = 'Jan'

if @counter > '31' and @counter <= '35'
set @Month = 'Feb'

if @counter > '35' and @counter <= '40'
set @Month = 'Mar'

if @counter > '40' and @counter <= '44'
set @Month = 'Apr'

if @counter > '44' and @counter <= '48'
set @Month = 'May'

if @counter > '48' and @counter <= '53'
set @Month = 'Jun'

Edited by - jimmy2090 on 08/19/2014 05:02:53
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1731 Posts

Posted - 08/19/2014 :  07:49:33  Show Profile  Reply with Quote
Recall that, in SQL, arrays can be simulated with tables. e.g. a table of months:


declare @months (MonthNum tinyint, MonthName char(3));
insert into @months(MonthNum, MonthName) values
(1, 'Jan'), (2,'Feb'), (3', 'Mar'), ... , (12, 'Dec');

-- get July

select MonthName from @months where MonthNum = 7;

-- Iterate through month 'array' (that is, table)

declare @m int = 1
while @m <= 12 begin
    select MonthName from @months where MonthNum = @m
    set @m += 1
end
Go to Top of Page

jimmy2090
Starting Member

Malaysia
20 Posts

Posted - 08/19/2014 :  12:43:51  Show Profile  Reply with Quote
quote:
Originally posted by gbritton

Recall that, in SQL, arrays can be simulated with tables. e.g. a table of months:


declare @months (MonthNum tinyint, MonthName char(3));
insert into @months(MonthNum, MonthName) values
(1, 'Jan'), (2,'Feb'), (3', 'Mar'), ... , (12, 'Dec');

-- get July

select MonthName from @months where MonthNum = 7;

-- Iterate through month 'array' (that is, table)

declare @m int = 1
while @m <= 12 begin
    select MonthName from @months where MonthNum = @m
    set @m += 1
end




Thank you. I would like to get the month by counting the weekno.

Weekno 1 start at july. Pls help.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1731 Posts

Posted - 08/19/2014 :  13:57:22  Show Profile  Reply with Quote
OK -- using datepart and dateadd we can get there:


declare @wk int = 34 -- input week number
select datepart(month, dateadd(wk, datepart(wk, '2014-07-01')-@wk, getdate()))


This works as follows (from the inside out):


-- calculate standard week number of Jul 1
datepart(wk, '2014-07-01')     

-- Use it to adjust target week         
Datepart(wk, '2014-07-01')-@wk

-- Add result to current date
dateadd(wk, datepart(wk, '2014-07-01')-@wk, getdate())

-- Extract month number from the result
select datepart(month, dateadd(wk, datepart(wk, '2014-07-01')-@wk, getdate()))


This gives you the month number given a week number (relative to Jul 1) for a given date (I used getdate() but you probably will get it from your data)

With the month number, you can lookup the month name as I showed you earlier.
Go to Top of Page

jimmy2090
Starting Member

Malaysia
20 Posts

Posted - 08/19/2014 :  22:29:22  Show Profile  Reply with Quote
thank so much.
finally i do as below:
any idea to improve it?
because, every year are different, i want to minimize the hardcode.


DECLARE @TotalWeek INT, @counter INT
DECLARE @Year INT, @WorkWeek INT
DECLARE @Start_Date DATE, @End_Date DATE
DECLARE @Month varchar(100)
	
BEGIN
	SET NOCOUNT ON;
	
SET @Year = 2015	
SET @TotalWeek = 53
SET @Start_Date = '2014-06-28'

SET @counter = 1
SET @WorkWeek = 1



declare @tbl_months TABLE
( 
WeekNo  int, 
MonthName varchar(100)
)


insert into @tbl_months(WeekNo, MonthName) values
(1, 'Jul'), (2,'Jul'), (3, 'Jul'),  (4, 'Jul'),
(5, 'Aug'), (6,'Aug'), (7, 'Aug'),  (8, 'Aug'), (9, 'Aug'),
(10, 'Sep'), (11,'Sep'), (12, 'Sep'),  (13, 'Sep'), (14, 'Sep'),
(15, 'Oct'), (16,'Oct'), (17, 'Oct'),  (18, 'Oct'),
(19, 'Nov'), (20,'Nov'), (21, 'Nov'),  (22, 'Nov'),
(23, 'Dec'), (24,'Dec'), (25, 'Dec'),  (26, 'Dec'), (27, 'Dec'),
(28, 'Jan'), (29,'Jan'), (30, 'Jan'),  (31, 'Jan'),
(32, 'Feb'), (33,'Feb'), (34, 'Feb'),  (35, 'Feb'),
(36, 'Mar'), (37,'Mar'), (38, 'Mar'),  (39, 'Mar'), (40, 'Mar'),
(41, 'Apr'), (42,'Apr'), (43, 'Apr'),  (44, 'Apr'),
(45, 'May'), (46,'May'), (47, 'May'),  (48, 'May'),
(49, 'Jun'), (50,'Jun'), (51, 'Jun'),  (52, 'Jun'), (53, 'Jun')


SELECT DISTINCT Year from TABLE where Year = @Year
IF @@ROWCOUNT =0
BEGIN

WHILE @counter <= @TotalWeek 
BEGIN


select @Month = MonthName from @tbl_months where WeekNo = @counter

	
INSERT INTO TABLE
(Year, WorkWeek, Start_Date, End_Date, Month) 
Values (@Year, @WorkWeek, @Start_Date, DATEADD(day,6,@Start_Date), @Month
)


SET @counter = @counter + 1;
SET @WorkWeek = @WorkWeek + 1
SET @Start_Date = DATEADD(day,1,DATEADD(day,6,@Start_Date))
	
END

END

END



Edited by - jimmy2090 on 08/19/2014 22:31:12
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1731 Posts

Posted - 08/20/2014 :  07:58:55  Show Profile  Reply with Quote
Here's a method that doesn't use tables at all:


declare @wk int = 34 -- input week number
declare @Jul1 datetime = dateadd(month, 6, dateadd(year, datediff(year,0, getdate()), 0))
select left(datename(month, dateadd(wk, datepart(wk, @jul1)-@wk, getdate())),3)

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000