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: Tuesday, October 28, 2014 12:28 PM
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
Post #1403343
Posted Sunday, January 6, 2013 11:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 2,037, Visits: 21,478
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
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: Tuesday, October 28, 2014 12:28 PM
Points: 421, Visits: 564
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: Yesterday @ 2:51 PM
Points: 243, Visits: 2,041
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 2,037, Visits: 21,478
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
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 @ 7:15 AM
Points: 37,477, Visits: 34,345
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 2,037, Visits: 21,478

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
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: Tuesday, October 28, 2014 12:28 PM
Points: 421, Visits: 564
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse