Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Write a TSQL to generate a date for a BI date table

Posted on 2014-10-30
Medium Priority
268 Views
How to run a TSQL to generate the following records for a particular year ?

YEAR      FISALPERIOD   PERIOD      QUARTER      MONTH          WEEKNO      DATE
2014      8                        11                      2014/Q3              2014/11          45               2014-11-05 00:00:00.000
0
Question by:AXISHK
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 14

Expert Comment

ID: 40413081
One record per week?  per day?
0

LVL 14

Accepted Solution

Christopher Gordon earned 1400 total points
ID: 40413094
If it's daily, something like this would work (I'm assuming your fiscal period occurs three months before calendar period):

``````declare @i int = 1
declare @currentDate date = '1/1/2014'

declare @time_dim table (calendarYear int, fiscalPeriod int, calendarPeriod int, calendarQuarter varchar(10), calendarMonth varchar(10), weekno int, calendarDate datetime)

while @i <= 365
begin

insert into @time_dim (calendarYear, fiscalPeriod, calendarPeriod, calendarQuarter, calendarMonth, weekno, calendarDate)

values (
YEAR(@currentDate)
,	MONTH(@currentDate)
,	cast(year(@currentDate) as char(4)) + '/Q' + cast(DATEPART(quarter, @currentDate) as CHAR(1))
,	cast(year(@currentDate) as char(4)) + '/' + cast(DATEPART(month, @currentDate) as CHAR(2))
,	DATEPART(week, @currentDate), @currentDate)

set @i = @i + 1

set @currentDate = dateadd(day, 1, @currentDate)

end

select * from @time_dim
``````
0

LVL 66

Assisted Solution

Jim Horn earned 600 total points
ID: 40413170
Looks like you're getting an answer above, but just to throw it out here's an article on Build your own calendar table to perform complex date expressions with source code that pulls this off by day.  Scroll down to 'Now we're done modifying the table, which will look like this ' and you'll see how it looks similar to what you're asking.
0

Author Comment

ID: 40421128
Not correct, The record should be like the value below,

YEAR      PERIOD      QUARTER      MONTH      WEEKNO            DATE_IDX
2014      1               2014/Q4              2015/01      1                    2015-01-01 00:00:00.000
2014      4               2014/Q1              2014/04      14                    2014-04-01 00:00:00.000
2014      12               2014/Q3        2014/12      53                    2014-12-31 00:00:00.000
0

LVL 14

Expert Comment

ID: 40423047
I added a dateadd(m, -3, @currentDate) in the part of the code that calculates the quarter field

``````declare @i int = 1
declare @currentDate date = '1/1/2014'

declare @time_dim table (calendarYear int, fiscalPeriod int, calendarPeriod int, calendarQuarter varchar(10), calendarMonth varchar(10), weekno int, calendarDate datetime)

while @i <= 365
begin

insert into @time_dim (calendarYear, fiscalPeriod, calendarPeriod, calendarQuarter, calendarMonth, weekno, calendarDate)

values (
YEAR(@currentDate) --calendar year
,	Month(DATEADD(m, -3, @currentDate)) -- fiscal period
,	MONTH(@currentDate) -- calendar period
,	cast(year(DATEADD(m, -3, @currentDate)) as char(4)) + '/Q' + cast(DATEPART(quarter, DATEADD(m, -3, @currentDate)) as CHAR(1)) --
,	cast(year(@currentDate) as char(4)) + '/' + cast(DATEPART(month, @currentDate) as CHAR(2))
,	DATEPART(week, @currentDate), @currentDate)

set @i = @i + 1

set @currentDate = dateadd(day, 1, @currentDate)

end

select * from @time_dim

``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month4 days, 21 hours left to enroll