Sequential YYYYMM calc problem

 Author Message Digs Hall of Fame Group: General Forum Members Points: 3585 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 J Livingston SQL One Orange Chip Group: General Forum Members Points: 27152 Visits: 41347 `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 Digs Hall of Fame Group: General Forum Members Points: 3585 Visits: 564 Thanks for your interest in my problem.But that does not produce a sequential YYYYMM sequence!Any other idea e4d4 SSCrazy Group: General Forum Members Points: 2671 Visits: 2410 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? J Livingston SQL One Orange Chip Group: General Forum Members Points: 27152 Visits: 41347 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 Jeff Moden SSC Guru Group: General Forum Members Points: 507217 Visits: 44277 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs J Livingston SQL One Orange Chip Group: General Forum Members Points: 27152 Visits: 41347 `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 Digs Hall of Fame Group: General Forum Members Points: 3585 Visits: 564 Sorry YYYYWW not YYYYMMthanks for reponses Steven Willis SSCertifiable Group: General Forum Members Points: 5299 Visits: 1721 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`