Loop to create a Date Dimension

Alas, I continue to find loops and cursors a challenge so I thought I'd try it out with the basics... the syntax below can be used to create a date dimension on any server; it does not reference any database tables.

NOTE: Additional columns could be added by using numerous other SQL datepart (etc.) functions.

/******************************************************

* FOR DEVELOPERS who have the rights to build a table

*******************************************************

* For testing purposes, I like to create my own schema;

* the syntax below could be done as a temp table as well

* (scroll towards the bottom of this SQL).

*******************************************************/

/* SETUP: I like to create my own schema for testing...

*

* create schema epriddyTEST

*

* --drop table epriddyTEST.dimDate

* -- create table epriddyTEST.dimDate

* -- (FullDate datetime,

* -- Year int,

* -- DayOfWeek varchar(20))

* -- create clustered index FullDateIndex on epriddyTEST.dimDate(FullDate)

********************************************************/

--I like to make the calculations relative to today

declare @Today datetime

set @Today = convert(datetime,convert(char(10),getdate(),101))

---------------------------------------

--STEP1: set up the date range desired

---------------------------------------

declare @StartDate datetime

set @StartDate = dateadd(yy,-5,@Today) -- 5 years ago today

set @StartDate = '01/01/'+cast((datepart(yy,@StartDate)) as varchar) -- starting Jan. 1st

declare @EndDate datetime

set @EndDate = dateadd(yy,5,@Today) -- 5 years from now today

set @EndDate = '12/31/'+cast((datepart(yy,@EndDate)) as varchar) -- ending Dec. 31st

--USE QUERIES BELOW TO DOUBLE-CHECK Range Selected...

--Select Today = @Today, StartDate = @StartDate, EndDate = @EndDate

--select datediff(d,@StartDate,@EndDate) -- number of loops

---------------------------------------

--STEP2: Setup Loop piece

---------------------------------------

declare @count int; --NumberOfLoops

set @count = -1

while @count < (select datediff(d,@StartDate,@EndDate)) -- this is the total number of times it should loop

begin

set @count = @count + 1

insert into epriddyTEST.dimDate (FullDate, [Year],[DayOfWeek])

Select FullDate = @StartDate+@count

,[Year] = datepart(yy,(@StartDate+@count ))

,[DayOfWeek]=datename(weekday,(@StartDate+@count ))

end

Select * from epriddyTEST.dimDate

--All done!

/******************************************************

* FOR ANALYSTS and Others who may not have permissions

* to create permanent tables; here's a temp table option

*******************************************************/

--I like to make the calculations relative to today

declare @Today datetime

set @Today = convert(datetime,convert(char(10),getdate(),101))

---------------------------------------

--STEP1: set up the date range desired

---------------------------------------

declare @StartDate datetime

set @StartDate = dateadd(yy,-5,@Today) -- 5 years ago today

set @StartDate = '01/01/'+cast((datepart(yy,@StartDate)) as varchar) -- starting Jan. 1st

declare @EndDate datetime

set @EndDate = dateadd(yy,5,@Today) -- 5 years from now today

set @EndDate = '12/31/'+cast((datepart(yy,@EndDate)) as varchar) -- ending Dec. 31st

--USE QUERIES BELOW TO DOUBLE-CHECK Range Selected...

--Select Today = @Today, StartDate = @StartDate, EndDate = @EndDate

--select datediff(d,@StartDate,@EndDate) -- number of loops

---------------------------------------

--STEP2: Setup Loop piece

---------------------------------------

declare @count int; --NumberOfLoops

set @count = -1

IF (object_id ('tempdb.dbo.#dimDate') is not null)

BEGIN

DROP TABLE #dimDate

END

create table #dimDate

(FullDate datetime,

[Year] int,

[DayOfWeek] varchar(20),

MonthNum int,

MonthName varchar(20))

create clustered index FullDateIndex on #dimDate(FullDate) -- I always create a index if I have to use a temp table

while @count < (select datediff(d,@StartDate,@EndDate)) -- this is the total number of times it should loop

begin

set @count = @count + 1

insert into #dimDate (FullDate, [Year],[DayOfWeek],MonthNum, MonthName)

Select FullDate = @StartDate+@count

,[Year] = datepart(yy,(@StartDate+@count ))

,[DayOfWeek]=datename(weekday,(@StartDate+@count ))

,MonthNum = datepart(mm,(@StartDate+@count ))

,[MonthName] = DATENAME(month, (@StartDate+@count ))

end

Select * from #dimDate

--All Done!

I'm not dead...

Sorry that I haven't posted in quite awhile. I've got a list of items I want to add to this blog but have been pushing through our OLAP cube conversion project and just haven't posted anything! Good news: We're almost done converting our 2000 DTS and Analysis Services cubes to the new 2005 Integration Services & Analysis Services packages (sweet baby!)....

... just wanted to let you know that I haven't abandoned this post altogether; just busy with development work ;-)

Checking for and dropping temp tables

IF (object_id ('tempdb.dbo.#fact_stg') is not null)

BEGIN

DROP TABLE #fact_stg

END

Again, me with the syntax ;-)

Proper case function

Many thanks to kselvia who posted this at: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37760

I'm adding it here in case the forum I found this on loses the posting:

"

CREATE FUNCTION dbo.fCapFirst(@input NVARCHAR(4000)) RETURNS NVARCHAR(4000)

AS

BEGIN

DECLARE @position INT

WHILE IsNull(@position,Len(@input)) > 1

SELECT @input = Stuff(@input,IsNull(@position,1),1,upper(substring(@input,IsNull(@position,1),1))), @position = charindex(' ',@input,IsNull(@position,1)) + 1

RETURN (@input)

END

It's not as sophisticated as the others but it's about 3 times as fast:

select dbo.fCapFirst(Lower(Column)) From MyTable

"

. . . Totally awesome!

Row_number without partition by

I always forget how to do this!!

Select

RowNumber = row_number() over(order by MonthLabel),

MonthLabel

from (Select distinct MonthLabel, Year from dbo.dimTime) dt

Insert row syntax

insert into dbo.TableLoads (TableName,LoadTS) values ('dbo.Status',getdate())

Another example. . . Manipulating timestamps

declare @Today datetime
set @Today = convert(datetime,convert(char(10),getdate(),101))
declare @TodayHour datetime
set @TodayHour = dateadd(hh,(datepart(hh,getdate())),@Today)

Select @Today, @TodayHour

declare @Today15Min_pre datetime
set @Today15MIn_pre = dateadd(mi,16,@TodayHour)

declare @Today15Min datetime
set @Today15Min = dateadd(ms,-2,@Today15Min_pre)

Select @Today15min_pre, @Today15Min

Cast as varchar

I always get the parentheses in the wrong order and then have to look this up!

ProviderKey = cast(GroupID as varchar)
+'-'+cast(BillingID as varchar)
+'-'+cast(LocationID as varchar)
+'-'+cast(UserID as varchar)

Convert UTC datetime to getdate time zone

Okay, there's probably a bunch of ways to do this, but, I'm working on my Analysis Services 2005 OLAP Query Log and found that the StartTime being tracked is in UTC (universal time clock). Alas, that's not meaningful for me. . .

I've been building a SQL Server Integration Services package and needed to check to see if the OLAP Query Log has run today; if so, I'd like one of the most recent queries (alas, our time stamp doesn't capture miliseconds to pull apart queries run in close proximity). Here's what I ended up doing:


Select *
from
(SELECT RowNumber = row_number() over(partition by (1) order by StartTime desc)
,[MSOLAP_Database]
,[MSOLAP_User]
,[StartTime]
,StartTime_NonUTC = dateadd(mi,datediff(mi,getutcdate(),getdate()),StartTime)
FROM [OLAP].[dbo].[OlapQueryLog]
) as log
where RowNumber = 1 -- in case there are multiple queries for the same time (since it's not to milisecond level) . . . return only one row

Indexing: Clustered vs. Non-clustered

Here’s what I’ve learned, but take it all with a grain of salt . . . In attending conferences & through discussions, it sounds like indexes are still a highly debated topic, but here’s what I’ve got so far:

* Every table can have only one clustered index and up to 200+ nonclustered indexes

* Every table should have a clustered index

* A clustered index is typically a fairly unique reference to a single row in the dataset, but it doesn’t have to be unique – it can be created as “Create Clustered Index Idx1 on t1(c)”

* Clustered indexes are usually surrogate keys or the columns on which tables join (e.g. PersonNum) – when doing a index seek, the server looks for the clustered index

* Clustered indexes are a sorting of the data

* Non-clustered indexes can be on anything useful :-)

* A non-clustered index works by keeping a “index” of which clustered index contains the non-clustered index information. When doing a index seek, the server looks at the non-clustered index which points it to which clustered index record(s) contain that information.


The typical analogy is that a clustered index is similar to page numbers (“page 3”) where as a nonclustered index is similar to a book index (e.g. ICD-9’s: “lumbosacral sprains is found on page 327”).

Issues currently being debated include: Should the clustered index be on the business key or the surrogate key? For incremental loading should the clustered index be on fields like loadTS? How many is too many indexes? . . . . There doesn’t seem to be any clear answers at the conferences & forums. So, we’re not alone in being unsure.

Here's a simple sample of indexing a temp table:

create table #ProductNumbers
(ProductID int,
ProductName varchar(50),
QuantityPerUnit varchar(50),
UnitPrice numeric 15,2)

create clustered index ProductIDIndex on #ProductNumbers(ProductID)
create nonclustered index ProductNameIndex on #ProductNumbers(ProductName)

insert into #ProductNumbers (ProductID, ProductName, QuantityPerUnit, UnitPrice)

Select
ProductID,
ProductName,
QuantityPerUnit,
UnitPrice
from Northwinds.dbo.Products
where Discontinued = 'True'

Simple sample of Row_Num command

Again, using the SQL Server Northwinds Database . . . Say, I want to find the most expensive product for each Category in the Product Table:

Select
CategoryID,
ProductID,
ProductName,
UnitPrice,
UnitsAvailable

from
(Select CategoryID,
ProductID,
ProductName,
UnitPrice,
UnitsAvailable = UnitsInStock+UnitsOnOrder,
[RowNumber] = row_number() over(partition by CategoryID order by UnitPrice desc)
from dbo.Products) as vt

where [RowNumber] = 1


P.S. I hate that I appear to lose formatting when the blog is posted. . . i should invest some time in learning to tweak html!

IsNumeric command

No worries, the virtual table below creates the data set and so no data tables are needed to run this query. . . .

Select
TestVariable = case when isnumeric(org.OrderAmountRangeQty)= 0 then org.OrderAmountRangeQty
else 'Its a number' end
from
(
Select OrderAmountRangeQty = 'Z'
union all
Select OrderAmountRangeQty = '1'
union all
Select OrderAmountRangeQty = '15'
union all
Select OrderAmountRangeQty = '#2'
union all
Select OrderAmountRangeQty = '3'
) as org
-- isnumeric(column) = 0 means it's not a number
-- isnumeric(column) = 1 means the column value IS a number

Similar functions: IsDate identifies if the freetext field is a date.

Simple sample of a Table variable

declare @Today datetime
set @Today = convert(datetime, convert(char(10), getdate(),101))

declare @FullDates Table (SevenDaysAgo)
insert into @FullDates (SevenDaysAgo)
Select FullDate as SevenDaysAgo
from dbo.DimDate
where FullDate >= DATEADD(dd,-7, @Today) and FullDate <= getdate() Select SevenDaysAgo
from @FullDates

Syntax to execute a Stored Procedure in query window

exec [stored procedure name] parameter1_value, parameter2_value


For example if you have a stored procedure with @Facility, @StartDate, and @EndDate parameters then:


exec [dbo].[s_PROCEDURE_Name] 'SHRB','01-01-2009','01-31-2009'

Convert getdate() to date as of midnight AM

I always have to remind myself how to do this . . . Is that sad?

Declare @today datetime

Set @Today = getdate()

Declare @Today3 smalldatetime

Set @Today3 = convert(datetime,convert(char(10),getdate(),101))

Select @Today, @Today3

Field Length

This formula may be necessary when you're dealing with a tool (e.g. Dashboard tool) that has limits to the character lengths that it can read. You may want to be proactive in identifying potential problems.

Using the SQL Server Northwind Traders Sample Database:

SELECT

ProductID,

ProductName,

FieldLength = len(ProductName)

FROM Northwind.dbo.Products

where len(ProductName) >57

Welcome to my SQL blog!

I'm just starting this out with the purpose of having a quick reference to remember SQL syntax. Enjoy!