Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sequential YYYYMM calc problem Expand / Collapse
Author
Message
Posted Sunday, January 6, 2013 11:12 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Sunday, June 8, 2014 9:18 PM
Points: 420, Visits: 559
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
Posted Sunday, January 6, 2013 11:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 1,886, Visits: 18,564
select CONVERT(CHAR(6), getdate(), 112)



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

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

Group: General Forum Members
Last Login: Sunday, June 8, 2014 9:18 PM
Points: 420, Visits: 559
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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:47 AM
Points: 223, Visits: 1,718
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
Posted Sunday, January 6, 2013 1:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 1,886, Visits: 18,564
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
Posted Sunday, January 6, 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 @ 5:34 PM
Points: 36,793, Visits: 31,250
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1403351
Posted Sunday, January 6, 2013 2:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 1,886, Visits: 18,564

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
Posted Sunday, January 6, 2013 3:21 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Sunday, June 8, 2014 9:18 PM
Points: 420, Visits: 559
Sorry YYYYWW not YYYYMM

thanks for reponses
Post #1403365
Posted Monday, January 7, 2013 10:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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 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
Posted Monday, January 7, 2013 11:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 1,945, Visits: 2,863
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