Script to Generate and Populate Date Dimension Version 2: Adding Multiple Financial Years

Posted on December 11, 2013 by Reza Rad

Almost a year ago I published first version of T-SQL script to generate and populate a general purpose date dimension here. Today I want to publish the second version of that date dimension which has some corrections (fiscal calendar calculation fixed), and additional attributes (such as current year, current month, and current day) Multiple countries Read more about Script to Generate and Populate Date Dimension Version 2: Adding Multiple Financial Years[…]

Fixing the error: The multi-part identifier … could not be bound in Join statements

Posted on September 10, 2013 by Reza Rad

One of the most common errors that you might face in join statements is this error: Msg 4104, Level 16, State 1, Line … The multi-part identifier …… could not be bound. The main reason for this error is that the source table cannot be found, for example if you have statement such as Table1.OrderDate, Read more about Fixing the error: The multi-part identifier … could not be bound in Join statements[…]

Lag and Lead Functions in SQL Server 2012

Posted on September 10, 2013 by Reza Rad

SQL Server 2012 has a set of useful functions to work in T-SQL environment. Lead and Lag are one of the most useful functions introduced in 2012. Lag function helps to access values from previous records, and Lead function helps to access values from next records in the data set. structure of working with these Read more about Lag and Lead Functions in SQL Server 2012[…]

T-SQL Script to get row count of all tables in the database

Posted on August 18, 2013 by Reza Rad

Today I’ve wrote a simple script to get number of records for all tables in a database and fetch those tables that contains at least one record. This picture shows an example of result set: Here is the script: declare @tempTable table(tablename varchar(max), rowscount int,processed bit) insert into @tempTable select TABLE_NAME,0,0 from INFORMATION_SCHEMA.TABLES where TABLE_CATALOG=‘DEMO’ Read more about T-SQL Script to get row count of all tables in the database[…]

Script for Creating and Generating members for Time Dimension

Posted on July 14, 2013 by Reza Rad

Time Dimension is an important dimension in most of the Data Warehouses, especially when requirement for keeping hours, minutes, and seconds is vital for the business. Some blog and books and articles consider time dimension as a dimension that contains date columns, but in this post I only focus on the Time part of it, Read more about Script for Creating and Generating members for Time Dimension[…]

Script for Creating and Generating members for Date Dimensions: General Purpose

Posted on January 9, 2013 by Reza Rad

Date Dimension can be found in every Data Warehouse, because Date dimension is one of the most important factors of each operational system that users like to view reports based on date dimension. So the importance and requirement to a date dimension persuade me to write a t-sql script to generate records for date dimension. Read more about Script for Creating and Generating members for Date Dimensions: General Purpose[…]

Capitalize Function (Version 2.0) – T-SQL

Posted on October 28, 2012 by Reza Rad

Hi folks, I’ve changed the t-sql code for Capitalize function, the previous capitalize function worked only if there is one space between words, but this version works even if there be more than one space between words. with this version of Capitalize; you can run the function like this: select dbo.Capitalize(‘st charles‘) and this Read more about Capitalize Function (Version 2.0) – T-SQL[…]

Replace Duplicate Spaces with Single Space in T-SQL

Posted on October 28, 2012 by Reza Rad

T-SQL code (working in SQL Server) to replace duplicate spaces (or triple or more) with a single space is : declare @text varchar(max) set @text=’ St Charles’ while charindex(‘ ‘,@text)>0 set @text=replace(@text,’ ‘,’ ‘) select @text and the result is : St Charles

T-SQL Function for Capitalize Word and Sentences

Posted on October 25, 2012 by Reza Rad

Hi folks, Today I faced a situation that I need to capitalize sentences in one column of a SQL Server database table, so I created a function to do that, I put this here to share with you. this function will change sentences like : select dbo.Capitalize(‘sample text for capitalization!‘) to : Sample Text For Capitalization! Here Read more about T-SQL Function for Capitalize Word and Sentences[…]

T-SQL Script: Find Keyword in whole Database

Posted on May 22, 2012 by Reza Rad

There are some times that you need to find all occurrence (or some of them) of a keyword in all columns/tables in a database, This is a usual scenario in data profiling. I wrote script below to fulfill this requirement, this script will search for specific keyword in all columns and tables of a database Read more about T-SQL Script: Find Keyword in whole Database[…]