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 06, 2013 11:12 AM
 SSC-Addicted Group: General Forum Members Last Login: Sunday, February 16, 2014 7:21 PM Points: 418, Visits: 554
 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 06, 2013 11:46 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 2:39 PM Points: 1,636, Visits: 16,539
 `select CONVERT(CHAR(6), getdate(), 112)` __________________________________________________________________ you can lead a user to data....but you cannot make them think !__________________________________________________________________
Post #1403345
 Posted Sunday, January 06, 2013 12:33 PM
 SSC-Addicted Group: General Forum Members Last Login: Sunday, February 16, 2014 7:21 PM Points: 418, Visits: 554
 Thanks for your interest in my problem.But that does not produce a sequential YYYYMM sequence!Any other idea
Post #1403347
 Posted Sunday, January 06, 2013 1:24 PM
 SSC Veteran Group: General Forum Members Last Login: Yesterday @ 11:56 AM Points: 221, Visits: 1,692
 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 06, 2013 1:29 PM
 SSCommitted Group: General Forum Members Last Login: Today @ 2:39 PM Points: 1,636, Visits: 16,539
 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 !__________________________________________________________________
Post #1403349
 Posted Sunday, January 06, 2013 1:51 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 2:23 PM Points: 35,961, Visits: 30,254
 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1403351
 Posted Sunday, January 06, 2013 2:29 PM
 SSCommitted Group: General Forum Members Last Login: Today @ 2:39 PM Points: 1,636, Visits: 16,539
 `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 !__________________________________________________________________
Post #1403358
 Posted Sunday, January 06, 2013 3:21 PM
 SSC-Addicted Group: General Forum Members Last Login: Sunday, February 16, 2014 7:21 PM Points: 418, Visits: 554
 Sorry YYYYWW not YYYYMMthanks for reponses
Post #1403365
 Posted Monday, January 07, 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
 Posted Monday, January 07, 2013 11:15 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, January 15, 2013 11:11 AM Points: 1,945, Visits: 2,782
 Build your own Calendar table with a calendar date as the PRIMARY KEY, then add other columns to show whatever your business needs in the way of temporal information. I add a column for the ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise. CREATE TABLE Something_Report_Periods(something_report_name CHAR(10) NOT NULL PRIMARY KEY CHECK (something_report_name LIKE ), something_report_start_date DATE NOT NULL, something_report_end_date DATE NOT NULL, CONSTRAINT date_ordering CHECK (something_report_start_date <= something_report_end_date),etc);These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'There are several websites with calendars you can cut & paste if you do not want to take 20 minutes to build it yourself, but you can start your search with: http://www.calendar-365.com/week-number.html Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #1403767

 Permissions