Monday, April 22, 2013

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                

 

Wednesday, April 3, 2013

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

 

 
 



Friday, March 1, 2013

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

Thursday, February 28, 2013

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