Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 for loop list of Array in stored procedure

Author  Topic 

jimmy2090
Starting Member

26 Posts

Posted - 2014-08-19 : 03:54:09
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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-19 : 04:41:56
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

26 Posts

Posted - 2014-08-19 : 04:44:03
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

26 Posts

Posted - 2014-08-19 : 05:02:22
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'
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-19 : 07:49:33
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

26 Posts

Posted - 2014-08-19 : 12:43:51
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-19 : 13:57:22
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

26 Posts

Posted - 2014-08-19 : 22:29:22
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


Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-20 : 07:58:55
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
   

- Advertisement -