Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sequential YYYYMM calc problem


Sequential YYYYMM calc problem

Author
Message
Digs
Digs
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
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 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
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3446 Visits: 33023

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
Digs
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 564
Thanks for your interest in my problem.

But that does not produce a sequential YYYYMM sequence!

Any other idea
e4d4
e4d4
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 2398
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?
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3446 Visits: 33023
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
and remember....every day is a school day

Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45006 Visits: 39880
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3446 Visits: 33023


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
and remember....every day is a school day

Digs
Digs
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 564
Sorry YYYYWW not YYYYMM

thanks for reponses
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 1721
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



 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search