Creating Dimension Table ==> Time (Example)

Script for Dimension table for Time.

Here Date_ID is the primary key which can be used as the foreign key in the fact tables.

CREATE TABLE [dbo].[DATE_DIM]

                (

                 [DATE_ID] [bigint] IDENTITY(1, 1)

                                    NOT NULL

                ,[SQL_DATE] [datetime] NOT NULL

                ,[DAY] [smallint] NOT NULL

                ,[DAY_OF_WEEK] [smallint] NULL

                ,[WEEK] [smallint] NOT NULL

                ,[MONTH] [SMALLINT] NOT NULL

                ,[QUARTER] [SMALLINT] NOT NULL

                ,[YEAR] [SMALLINT] NOT NULL

                ,[DAY_OF_YEAR] [SMALLINT] NOT NULL

                ,[DAY_TEXT] [VARCHAR](50) NOT NULL

                ,[MONTH_TEXT] [VARCHAR](50) NOT NULL

                ,[QUARTER_TEXT] [VARCHAR](50) NOT NULL

                 )

                

 declare @date datetime

 declare @enddate datetime

 set @date = CONVERT(datetime,'01-01-2012')

 set @enddate = CONVERT(datetime,'12-31-2025')

 while @date <= @enddate

   begin

   insert into DATE_DIM

   ([SQL_DATE]

   ,[day]

   ,[DAY_OF_WEEK]

   ,[WEEK]

   ,[MONTH]

   ,[QUARTER]

   ,[YEAR]

   ,[DAY_OF_YEAR]

   ,[DAY_TEXT]

   ,[MONTH_TEXT]

   ,[QUARTER_TEXT]

   )

     select

    @date as sqldate

    ,datepart(D,@date) as [day]

    ,datepart(DW,@date) as [day_of_week]

    ,DATEPART(WK,@date) as [week]

    ,DATEPART (M,@date) as [month]

    ,DATEPART(Q,@date) as [quarter]

    ,DATEPART(DY,@date) as [day_of_year]

    ,DATEPART(Y,@date) as [year]

    ,DATENAME(DW,@date) as day_text

    ,DATENAME(M,@date) as month_text

    ,'Q' + CONVERT(CHAR(1), DATENAME(QQ, @DATE)) AS QUARTERTEXT

    set @date = DATEADD(D,1,@date)   

   end                

 

Total Number Of Records In each Table

Quite often in our day today activity we need to check the count of records in each table of the database. So instead of firing count(*) against all the table, we can use the DMV's ie inbuild system procedures or functions to get the desired result.

Code :

select  distinct s.name as Name_Schema,o.name as Table_Name, p.row_count as Total_Records from sys.objects o inner join

sys.dm_db_partition_stats p on o.object_id = p.object_id
inner join sys.schemas s on o.schema_id=s.schema_id
where o.type = 'U'

So lets say we want to get the list of tables along with the total records in Adventureworks database.

Below is the output for the same:

 

Name-Schema	Table_Name	Total_Records
HumanResources	Department	16
HumanResources	Employee	290
HumanResources	EmployeeAddress	290
HumanResources	EmployeeDepartmentHistory	296
HumanResources	EmployeePayHistory	316
HumanResources	JobCandidate	13
HumanResources	Shift	3

 

SQL BLOG: First Occurrence Of Character In a String

Scenario:

 We need to find the first occurrence of character ie non numeric data in the string without using the built in function.

Example: String 577a     

First occurrence of character : 4

Approach:

i)                    Here I haven’t used any built in functions. First we need to find the number of records in the table.

ii)                   Now for each record, calculate the length of string. This operation is performed by WHILE loop

iii)                 Now for every single record, I have checked wether the character is numeric or non numeric. In case of non numeric, return the current position as the first occurrence otherwise increment the counter by 1

Full code is below:

/* First occurrence of character in a string using table variable */
/* table variable created*/
declare @table1 table(id integer identity(1,1),name varchar(5))
insert into @table1 values ('911av'),('1sdf'),('aaaa')
/* displaying content of table*/
select * from @table1

/* count the total number of records in the table */
declare @rcount integer
select @rcount = COUNT(name) from @table1
/* declare variable for outer loop */
declare @i_rcount integer
set @i_rcount = 1
/* loop that will run for each record */
declare @srecord varchar(10)
declare @result1 varchar(10)
declare @x integer
while(@i_rcount <=@rcount)
begin/* store the individual records here */
select @srecord = name from @table1 where id = @i_rcount
select @result1 = LEN(name) from @table1
set @x = 1   /* checking each character in the record */
   while(@x <=@result1)
   begin
   if(ISNUMERIC(left(@srecord,@x))) <> 1
        begin
        select 'First Occurence of character' +' '+ 'in string:'+' '+' '+ @srecord +' '+'is'+' '+ cast(@x as varchar(3))
        set @x = @result1+1 /* this will exit from the inner loop */
        end
        else
        set @x = @x+1
   end
   set @i_rcount = @i_rcount+1
end  
  
 Sample Output:

First Occurence of character in string:  911av is 4

SQL BLOG: Using Triggers for creating history tables in SQL Server

Case Study:

1)       We  need to track the employee table in terms of new records inserted along with the datetime stamp

2)      In case any updation, we need to track the same with only the column name that is updated. Rest all columns should be null. All tracking will be stored in a separate table called employee_audit.

Solution:

i)                    Creation of Employee Table:

create table employee

(

empid integer,

ename varchar(20),

location varchar(20),

company varchar(20),

doj date

)

insert into employee values (1,'aa','hyd','zzzz','2012-01-01')

insert into employee values (2,'bb','blore','xxxx','2013-01-01')

insert into employee values (3,'cc','delhi','yyyy','2013-02-28')

ii)                   Creation of Employee_Audit table that will contain the audit value:

create table employee_audit

(

empid integer,

ename varchar(20),

location varchar(20),

company varchar(20),

doj date,

audit_action varchar(20),

modified_date datetime

)

iii)                 Creation of Insert after trigger that will take care of newly inserted records:

/* create insert after  trigger and capture information on employee audit table */

create trigger tr_insert_after_employee_audit on employee

for insert

as

declare @empid integer

declare @ename varchar(20)

declare @location varchar(20)

declare @company varchar(20)

declare @doj date

declare @audit_action varchar(20)

select @empid = i.empid from inserted i;

select @ename = i.ename from inserted i;

select @location = i.location from inserted i;

select @company = i.company from inserted i;

select @doj = i.doj from inserted i;

set @audit_action = 'Insert Record'

insert into employee_audit values (@empid,@ename,@location,@company,@doj,@audit_action,getdate())

iv)                 Update trigger for keeping record of updated columns

/* trigger for updation of record in employee table */

create trigger tr_update_after_employee_audit on employee

for update

as

declare @empid integer

declare @ename varchar(20)

declare @location varchar(20)

declare @company varchar(20)

declare @doj date

declare @audit_action varchar(20)

/* variables declaration for deleted records */

declare @d_empid integer

declare @d_ename varchar(20)

declare @d_location varchar(20)

declare @d_company varchar(20)

declare @d_doj date

declare @d_audit_action varchar(20)

select @empid = case when (d.empid = i.empid) then  i.empid end from inserted i inner join deleted d on i.empid = d.empid;

select @ename = case when (d.ename = i.ename) then  i.ename end from inserted i inner join deleted d on i.empid = d.empid;

select @location = case when (d.location = i.location) then null else i.location end from inserted i left outer join deleted d on i.empid = d.empid;

select @company = case when (d.company = i.company) then null else i.company end from inserted i left outer join deleted d on i.empid = d.empid;

select @doj = case when (d.doj = i.doj) then null else i.doj end from inserted i left outer join deleted d on i.empid = d.empid;

set @audit_action = 'Updated Record'

insert into employee_audit values (@empid,@ename,@location,@company,@doj,@audit_action,getdate())

Sample Output:

Empid	Ename	Location	Company	DOJ	Action Status	Modified_Date
1	aa	hyd	zzzz	01-01-2012	Insert Record	11:03.9
2	bb	blore	xxxx	01-01-2013	Insert Record	11:03.9
3	cc	delhi	yyyy	28-02-2013	Insert Record	11:03.9
1	aa	hyd-1	NULL	NULL	Updated Record	11:34.7