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 = JulyWk2 = JulyWk3 = JulyWk4 = JulyWk5 = Augand so on..then i create list of array like below:The counter for insert the week one by oneDECLARE @TotalWeek INT, @counter INTDECLARE @WeekNo varchar, @Month varcharSET @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 onSET @TotalWeek = 53SET @counter = 1WHILE @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'EndSET @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 |
|
|
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. |
|
|
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 BEGINif @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' |
|
|
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 Julyselect MonthName from @months where MonthNum = 7;-- Iterate through month 'array' (that is, table)declare @m int = 1while @m <= 12 begin select MonthName from @months where MonthNum = @m set @m += 1end |
|
|
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 Julyselect MonthName from @months where MonthNum = 7;-- Iterate through month 'array' (that is, table)declare @m int = 1while @m <= 12 begin select MonthName from @months where MonthNum = @m set @m += 1end
Thank you. I would like to get the month by counting the weekno.Weekno 1 start at july. Pls help. |
|
|
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 numberselect 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 1datepart(wk, '2014-07-01') -- Use it to adjust target week Datepart(wk, '2014-07-01')-@wk-- Add result to current datedateadd(wk, datepart(wk, '2014-07-01')-@wk, getdate())-- Extract month number from the resultselect 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. |
|
|
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 INTDECLARE @Year INT, @WorkWeek INTDECLARE @Start_Date DATE, @End_Date DATEDECLARE @Month varchar(100) BEGIN SET NOCOUNT ON; SET @Year = 2015 SET @TotalWeek = 53SET @Start_Date = '2014-06-28'SET @counter = 1SET @WorkWeek = 1declare @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 = @YearIF @@ROWCOUNT =0BEGINWHILE @counter <= @TotalWeek BEGINselect @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 + 1SET @Start_Date = DATEADD(day,1,DATEADD(day,6,@Start_Date)) ENDENDEND |
|
|
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 numberdeclare @Jul1 datetime = dateadd(month, 6, dateadd(year, datediff(year,0, getdate()), 0))select left(datename(month, dateadd(wk, datepart(wk, @jul1)-@wk, getdate())),3) |
|
|
|
|
|