Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Sequential YYYYMM calc problem Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, January 6, 2013 11:12 AM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, October 28, 2014 12:28 PM Points: 421, Visits: 564
 First the code...` DECLARE @Current INT DECLARE @Prev1 INT DECLARE @Prev2 INT DECLARE @Prev3 INT DECLARE @Prev4 INT DECLARE @Prev5 INT DECLARE @Prev6 INT DECLARE @Prev7 INT DECLARE @Prev8 INT DECLARE @Prev9 INT DECLARE @Prev10 INT DECLARE @Prev11 INT SET @Current = YEAR(getUTCDate()) * 100 + DATEPART(week,getUTCDate()) SET @Prev1 = YEAR(DATEADD(d, -7,getUTCDate())) * 100 + DATEPART(week,DATEADD(d, -7,getUTCDate())) SET @Prev2 = YEAR(DATEADD(d, -14,getUTCDate())) * 100 + DATEPART(week,DATEADD(d, -14,getUTCDate())) SET @Prev3 = YEAR(DATEADD(d, -21,getUTCDate())) * 100 + DATEPART(week,DATEADD(d, -21,getUTCDate())) SELECT @Current, @Prev1, @Prev2, @Prev3 SELECT YEAR(getUTCDate())*100 + DATEPART(week,getUTCDate()) AS 'WeekNo'`The result from the 1st SELECT is this:201302 201253 201252 201251As you can see I am missing 201301QUESTION: how can I fix this? I need YYYYMM in order.PS: I dont have a Calender table with YYYYMM, unles you know where I can get one ?Any ideas
Post #1403343
 Posted Sunday, January 6, 2013 11:46 AM
 Hall of Fame Group: General Forum Members Last Login: Today @ 12:08 PM Points: 3,321, Visits: 32,657
 `select CONVERT(CHAR(6), getdate(), 112)` ______________________________________________________________you can lead a user to data....but you cannot make them think and remember....every day is a school day
Post #1403345
 Posted Sunday, January 6, 2013 12:33 PM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, October 28, 2014 12:28 PM Points: 421, Visits: 564
 Thanks for your interest in my problem.But that does not produce a sequential YYYYMM sequence!Any other idea
Post #1403347
 Posted Sunday, January 6, 2013 1:24 PM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, December 7, 2016 5:04 AM Points: 269, Visits: 2,394
 Digs (1/6/2013)First the code.......The result from the 1st SELECT is this:201302 201253 201252 201251As you can see I am missing 201301QUESTION: how can I fix this? I need YYYYMM in order.PS: I dont have a Calender table with YYYYMM, unles you know where I can get one ?Any ideasYou ask for a date in format YYYYMM but your query generate the date in strange format YYYYWW where WW mean week number.Your query will return values that you are expecting day after tomorrow Today we have 06 january 2013 if you substract 7 days, then we have?
Post #1403348
 Posted Sunday, January 6, 2013 1:29 PM
 Hall of Fame Group: General Forum Members Last Login: Today @ 12:08 PM Points: 3,321, Visits: 32,657
 Digs (1/6/2013)First the code...PS: I dont have a Calender table with YYYYMM, unles you know where I can get one ?Any ideasplenty of calendar scripts available if you search...heres a snippet....see last columnE&OEregards`--==== Create a Tally table and a Calendar tableSELECT TOP 60001 IDENTITY(INT, 1, 1) AS N --=== will have start as 1...alter to IDENTITY(INT, 0, 1) for zero as startINTO dbo.TallyFROM sys.all_columns ac1CROSS JOIN sys.all_columns ac2CROSS JOIN sys.all_columns ac3ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100SET DATEFORMAT DMYDECLARE @Date_Start AS DATETIMEDECLARE @Date_End AS DATETIMESET @Date_Start = '01/01/2010'SET @Date_End = '31/12/2020'CREATE TABLE dbo.Calendar ( calendar_date_ID INT IDENTITY(1, 1) NOT NULL, calendar_week_ID INT, calendar_month_ID INT, calendar_date DATETIME PRIMARY KEY CLUSTERED, calendar_year SMALLINT, calendar_month TINYINT, calendar_day TINYINT, calendar_quarter TINYINT, first_day_in_month DATETIME, last_day_in_month DATETIME, day_of_week TINYINT, week_of_year TINYINT, days_in_month TINYINT, day_of_year SMALLINT, is_weekday INT, day_name VARCHAR (10), month_name VARCHAR (10), iso_date CHAR (8), fiscal_year SMALLINT, fiscal_month TINYINT, DIGS_MTH INT );INSERT INTO dbo.Calendar (calendar_date)SELECT t.N - 1 + @Date_StartFROM dbo.Tally tWHERE t.N - 1 + @Date_Start <= @Date_EndUPDATE dbo.CalendarSET calendar_week_ID = calendar_date_id / 7 + 1, calendar_year = Datepart (YEAR, calendar_date), fiscal_year = CASE WHEN Datepart(M, calendar_date) >= 10 THEN Datepart (YEAR, calendar_date) + 1 ELSE Datepart (YEAR, calendar_date) END, calendar_month = Datepart (MONTH, calendar_date), fiscal_month = CASE WHEN Datepart(M, calendar_date) >= 10 THEN Datepart(M, calendar_date) - 9 ELSE Datepart(M, calendar_date) + 3 END, calendar_day = Datepart (DAY, calendar_date), calendar_quarter = Datepart (QUARTER, calendar_date), first_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date), 0), last_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1, day_of_week = Datepart (WEEKDAY, calendar_date), week_of_year = Datepart (WEEK, calendar_date), day_of_year = Datepart (DAYOFYEAR, calendar_date), is_weekday = Isnull (( CASE WHEN ( ( @@DATEFIRST - 1 ) + ( Datepart (WEEKDAY, calendar_date) - 1 ) )%7 NOT IN ( 5, 6 ) THEN 1 END ), 0), day_name = Datename (WEEKDAY, calendar_date), month_name = Datename (MONTH, calendar_date), iso_date = CONVERT(CHAR(8), calendar_date, 112), days_in_month = Datepart(dd, ( Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1 )), DIGS_MTH = CONVERT(CHAR(6), calendar_date, 112)` ______________________________________________________________you can lead a user to data....but you cannot make them think and remember....every day is a school day
Post #1403349
 Posted Sunday, January 6, 2013 1:51 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 4:15 PM Points: 42,082, Visits: 39,476
 Digs (1/6/2013)First the code...` DECLARE @Current INT DECLARE @Prev1 INT DECLARE @Prev2 INT DECLARE @Prev3 INT DECLARE @Prev4 INT DECLARE @Prev5 INT DECLARE @Prev6 INT DECLARE @Prev7 INT DECLARE @Prev8 INT DECLARE @Prev9 INT DECLARE @Prev10 INT DECLARE @Prev11 INT SET @Current = YEAR(getUTCDate()) * 100 + DATEPART(week,getUTCDate()) SET @Prev1 = YEAR(DATEADD(d, -7,getUTCDate())) * 100 + DATEPART(week,DATEADD(d, -7,getUTCDate())) SET @Prev2 = YEAR(DATEADD(d, -14,getUTCDate())) * 100 + DATEPART(week,DATEADD(d, -14,getUTCDate())) SET @Prev3 = YEAR(DATEADD(d, -21,getUTCDate())) * 100 + DATEPART(week,DATEADD(d, -21,getUTCDate())) SELECT @Current, @Prev1, @Prev2, @Prev3 SELECT YEAR(getUTCDate())*100 + DATEPART(week,getUTCDate()) AS 'WeekNo'`The result from the 1st SELECT is this:201302 201253 201252 201251As you can see I am missing 201301QUESTION: how can I fix this? I need YYYYMM in order.PS: I dont have a Calender table with YYYYMM, unles you know where I can get one ?Any ideasAs some of this others have pointed out, you've listed a format of YYYYMM as being what is desired but your code and output seem to indicate that what you really want is YYYYWW. Now the question becomes, what is your definition for the first week of the year? --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #1403351
 Posted Sunday, January 6, 2013 2:29 PM
 Hall of Fame Group: General Forum Members Last Login: Today @ 12:08 PM Points: 3,321, Visits: 32,657
 `CREATE TABLE [dbo].[mydate]( [mydate] [datetime] NULL) INSERT INTO [dbo].[mydate]([mydate])SELECT '20121222 00:00:00.000' UNION ALLSELECT '20121223 00:00:00.000' UNION ALLSELECT '20121224 00:00:00.000' UNION ALLSELECT '20121225 00:00:00.000' UNION ALLSELECT '20121226 00:00:00.000' UNION ALLSELECT '20121227 00:00:00.000' UNION ALLSELECT '20121228 00:00:00.000' UNION ALLSELECT '20121229 00:00:00.000' UNION ALLSELECT '20121230 00:00:00.000' UNION ALLSELECT '20121231 00:00:00.000' UNION ALLSELECT '20130101 00:00:00.000' UNION ALLSELECT '20130102 00:00:00.000' UNION ALLSELECT '20130103 00:00:00.000' UNION ALLSELECT '20130104 00:00:00.000' UNION ALLSELECT '20130105 00:00:00.000' UNION ALLSELECT '20130106 00:00:00.000' UNION ALLSELECT '20130107 00:00:00.000' UNION ALLSELECT '20130108 00:00:00.000' UNION ALLSELECT '20130109 00:00:00.000' SELECT mydate , YEAR( mydate ) * 100 + DATEPART( week , mydate ) FROM mydate;` ______________________________________________________________you can lead a user to data....but you cannot make them think and remember....every day is a school day
Post #1403358
 Posted Sunday, January 6, 2013 3:21 PM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, October 28, 2014 12:28 PM Points: 421, Visits: 564
 Sorry YYYYWW not YYYYMMthanks for reponses
Post #1403365
 Posted Monday, January 7, 2013 10:47 AM
 SSC-Addicted Group: General Forum Members Last Login: Sunday, September 29, 2013 1:24 AM Points: 429, Visits: 1,721
 To get the correct week number based on ISO standard you can use this function:`--Credit to Ramakrishna ElashwarapuCREATE FUNCTION dbo.tvfGetWeekNumberFromDate_ISO ( @dDate DATETIME )RETURNS @WeekNumber TABLE ( [Year] INT NULL ,[WeekNumber] INT NULL )WITH SCHEMABINDINGASBEGIN DECLARE @iWeekdayNumber INT ,@dCurrThurs DATETIME ,@YearFirstThurs DATETIME ,@iYearFirstThursWeekNum INT ,@dFirstThursYear DATETIME ,@iWeekNumber INT SET @iWeekdayNumber = (((DATEPART(dw, @dDate) - 1) + (@@DATEFIRST - 1)) % 7) + 1 SET @dCurrThurs = DATEADD(d,(4 - @iWeekdayNumber),@dDate) SET @YearFirstThurs = CAST(CAST(YEAR(@dCurrThurs) AS CHAR(4)) + '-01-01' AS DATETIME) SET @iYearFirstThursWeekNum = (((DATEPART(dw, @YearFirstThurs) - 1) + (@@DATEFIRST - 1)) % 7) + 1 IF (@iYearFirstThursWeekNum in (1,2,3,4)) SET @dFirstThursYear = DATEADD(d,(4 - @iYearFirstThursWeekNum),@YearFirstThurs) ELSE SET @dFirstThursYear = DATEADD(d,(4 - @iYearFirstThursWeekNum + 7),@YearFirstThurs) SET @i[WeekNumber] = DATEDIFF(d,@dFirstThursYear,@dCurrThurs)/7+1 INSERT INTO @WeekNumber SELECT YEAR(@dFirstThursYear),@iWeekNumber RETURN END`Then just do a CROSS APPLY and ROW_NUMBER() to sort:`IF OBJECT_ID('tempdb..#TempTable') IS NOT NULLDROP TABLE #TempTableCREATE TABLE #TempTable ( [ID] INT IDENTITY(1,1) NOT NULL, [mydate] DATETIME NULL, PRIMARY KEY (ID))INSERT INTO #TempTableSELECT '20121222 00:00:00.000' UNION ALLSELECT '20121223 00:00:00.000' UNION ALLSELECT '20121224 00:00:00.000' UNION ALLSELECT '20121225 00:00:00.000' UNION ALLSELECT '20121226 00:00:00.000' UNION ALLSELECT '20121227 00:00:00.000' UNION ALLSELECT '20121228 00:00:00.000' UNION ALLSELECT '20121229 00:00:00.000' UNION ALLSELECT '20121230 00:00:00.000' UNION ALLSELECT '20121231 00:00:00.000' UNION ALLSELECT '20130101 00:00:00.000' UNION ALLSELECT '20130102 00:00:00.000' UNION ALLSELECT '20130103 00:00:00.000' UNION ALLSELECT '20130104 00:00:00.000' UNION ALLSELECT '20130105 00:00:00.000' UNION ALLSELECT '20130106 00:00:00.000' UNION ALLSELECT '20130107 00:00:00.000' UNION ALLSELECT '20130108 00:00:00.000' UNION ALLSELECT '20130109 00:00:00.000' SELECT ROW_NUMBER() OVER (ORDER BY w.[Year],w.[WeekNumber]) AS RowNum ,CAST(w.[Year] AS CHAR(4)) + (CASE WHEN LEN(w.[WeekNumber]) = 1 THEN '0'+CAST(w.[WeekNumber] AS CHAR(1)) ELSE CAST(w.[WeekNumber] AS CHAR(2)) END) AS YearWeek ,mydate AS OriginalDateFROM #TempTable AS ttCROSS APPLY dbo.tvfGetWeekNumberFromDate_ISO(tt.mydate) AS wWHERE tt.ID > 0 `Output:`RowNum YearWeek OriginalDate1 201251 2012-12-22 00:00:00.0002 201251 2012-12-23 00:00:00.0003 201252 2012-12-24 00:00:00.0004 201252 2012-12-25 00:00:00.0005 201252 2012-12-26 00:00:00.0006 201252 2012-12-27 00:00:00.0007 201252 2012-12-28 00:00:00.0008 201252 2012-12-29 00:00:00.0009 201252 2012-12-30 00:00:00.00010 201301 2012-12-31 00:00:00.00011 201301 2013-01-01 00:00:00.00012 201301 2013-01-02 00:00:00.00013 201301 2013-01-03 00:00:00.00014 201301 2013-01-04 00:00:00.00015 201301 2013-01-05 00:00:00.00016 201301 2013-01-06 00:00:00.00017 201302 2013-01-07 00:00:00.00018 201302 2013-01-08 00:00:00.00019 201302 2013-01-09 00:00:00.000`
Post #1403754

 Permissions