Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » SQL Server 2008 » SQL Server 2008 - General » Sequential YYYYMM calc problem
Add to briefcase

Sequential YYYYMM calc problem Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Digs
Posted Sunday, January 06, 2013 11:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 12, 2013 12:27 PM
Points: 416, Visits: 542
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 201251

As you can see I am missing 201301

QUESTION: 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
J Livingston SQL
Posted Sunday, January 06, 2013 11:46 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 11:46 PM
Points: 1,472, Visits: 14,605 
select CONVERT(CHAR(6), getdate(), 112)

__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1403345
Digs
Posted Sunday, January 06, 2013 12:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 12, 2013 12:27 PM
Points: 416, Visits: 542
Thanks for your interest in my problem.

But that does not produce a sequential YYYYMM sequence!

Any other idea
Post #1403347
e4d4
Posted Sunday, January 06, 2013 1:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 160, Visits: 1,146
Digs (1/6/2013)
First the code...
....
The result from the 1st SELECT is this:

201302 201253 201252 201251

As you can see I am missing 201301

QUESTION: 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


You 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
J Livingston SQL
Posted Sunday, January 06, 2013 1:29 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 11:46 PM
Points: 1,472, Visits: 14,605
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 ideas


plenty of calendar scripts available if you search...heres a snippet....see last column
E&OE

regards



--==== Create a Tally table and a Calendar table

SELECT     TOP 60001 IDENTITY(INT, 1, 1) AS N  --===  will have start as 1...alter to IDENTITY(INT, 0, 1) for zero as start
INTO       dbo.Tally
FROM       sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3

ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

SET DATEFORMAT DMY

DECLARE @Date_Start AS DATETIME
DECLARE @Date_End AS DATETIME

SET @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_Start
FROM   dbo.Tally t
WHERE  t.N - 1 + @Date_Start <= @Date_End

UPDATE dbo.Calendar
SET    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
Jeff Moden
Posted Sunday, January 06, 2013 1:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:54 PM
Points: 33,113, Visits: 27,041
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 201251

As you can see I am missing 201301

QUESTION: 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


As 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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1403351
J Livingston SQL
Posted Sunday, January 06, 2013 2:29 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 11:46 PM
Points: 1,472, Visits: 14,605 

CREATE TABLE [dbo].[mydate](
	[mydate] [datetime] NULL
) 

INSERT INTO [dbo].[mydate]([mydate])
SELECT '20121222 00:00:00.000' UNION ALL
SELECT '20121223 00:00:00.000' UNION ALL
SELECT '20121224 00:00:00.000' UNION ALL
SELECT '20121225 00:00:00.000' UNION ALL
SELECT '20121226 00:00:00.000' UNION ALL
SELECT '20121227 00:00:00.000' UNION ALL
SELECT '20121228 00:00:00.000' UNION ALL
SELECT '20121229 00:00:00.000' UNION ALL
SELECT '20121230 00:00:00.000' UNION ALL
SELECT '20121231 00:00:00.000' UNION ALL
SELECT '20130101 00:00:00.000' UNION ALL
SELECT '20130102 00:00:00.000' UNION ALL
SELECT '20130103 00:00:00.000' UNION ALL
SELECT '20130104 00:00:00.000' UNION ALL
SELECT '20130105 00:00:00.000' UNION ALL
SELECT '20130106 00:00:00.000' UNION ALL
SELECT '20130107 00:00:00.000' UNION ALL
SELECT '20130108 00:00:00.000' UNION ALL
SELECT '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
Digs
Posted Sunday, January 06, 2013 3:21 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 12, 2013 12:27 PM
Points: 416, Visits: 542
Sorry YYYYWW not YYYYMM

thanks for reponses
Post #1403365
Steven Willis
Posted Monday, January 07, 2013 10:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 298, Visits: 1,323
To get the correct week number based on ISO standard you can use this function:


--Credit to Ramakrishna Elashwarapu

CREATE FUNCTION dbo.tvfGetWeekNumberFromDate_ISO
    (
    @dDate DATETIME
    )
RETURNS @WeekNumber TABLE 
    (
     [Year] INT NULL
    ,[WeekNumber] INT NULL
    )
WITH SCHEMABINDING
AS
BEGIN

    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 NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (
	[ID] INT IDENTITY(1,1) NOT NULL,
	[mydate] DATETIME NULL,
	PRIMARY KEY (ID))

INSERT INTO #TempTable
SELECT '20121222 00:00:00.000' UNION ALL
SELECT '20121223 00:00:00.000' UNION ALL
SELECT '20121224 00:00:00.000' UNION ALL
SELECT '20121225 00:00:00.000' UNION ALL
SELECT '20121226 00:00:00.000' UNION ALL
SELECT '20121227 00:00:00.000' UNION ALL
SELECT '20121228 00:00:00.000' UNION ALL
SELECT '20121229 00:00:00.000' UNION ALL
SELECT '20121230 00:00:00.000' UNION ALL
SELECT '20121231 00:00:00.000' UNION ALL
SELECT '20130101 00:00:00.000' UNION ALL
SELECT '20130102 00:00:00.000' UNION ALL
SELECT '20130103 00:00:00.000' UNION ALL
SELECT '20130104 00:00:00.000' UNION ALL
SELECT '20130105 00:00:00.000' UNION ALL
SELECT '20130106 00:00:00.000' UNION ALL
SELECT '20130107 00:00:00.000' UNION ALL
SELECT '20130108 00:00:00.000' UNION ALL
SELECT '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 OriginalDate
FROM
    #TempTable AS tt
CROSS APPLY
    dbo.tvfGetWeekNumberFromDate_ISO(tt.mydate) AS w
WHERE
    tt.ID > 0

Output:

RowNum	YearWeek	OriginalDate
1	201251	2012-12-22 00:00:00.000
2	201251	2012-12-23 00:00:00.000
3	201252	2012-12-24 00:00:00.000
4	201252	2012-12-25 00:00:00.000
5	201252	2012-12-26 00:00:00.000
6	201252	2012-12-27 00:00:00.000
7	201252	2012-12-28 00:00:00.000
8	201252	2012-12-29 00:00:00.000
9	201252	2012-12-30 00:00:00.000
10	201301	2012-12-31 00:00:00.000
11	201301	2013-01-01 00:00:00.000
12	201301	2013-01-02 00:00:00.000
13	201301	2013-01-03 00:00:00.000
14	201301	2013-01-04 00:00:00.000
15	201301	2013-01-05 00:00:00.000
16	201301	2013-01-06 00:00:00.000
17	201302	2013-01-07 00:00:00.000
18	201302	2013-01-08 00:00:00.000
19	201302	2013-01-09 00:00:00.000

 
Post #1403754
CELKO
Posted Monday, January 07, 2013 11:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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 <pattern>),
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 Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1403767
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.