ITIL Service Management Life Cycle

ITIL Process Implementation

Find Duplicate Records

SELECT     tablefield, COUNT(tablefield) AS dup_count
FROM         table
GROUP BY tablefield
HAVING     (COUNT(tablefield) > 1)

Difference Between COUNT(DISTINCT) vs COUNT(ALL)

SELECT COUNT([FieldName])

FROM [TableName]

GOSELECT COUNT(ALL [FieldName])

FROM [TableName]

GOSELECT COUNT(DISTINCT [FieldName])

FROM [TableName]

GO

Date Conversions with Different Formats.

select convert (varchar(20),getdate(),20)
output result
2011-03-08 16:53:04

select convert (varchar(20),getdate(),21)
output result
2011-03-08 17:04:09.

select convert (varchar(20),getdate(),22)
output result
03/08/11  5:04:42 PM

select convert (varchar(20),getdate(),23)
output result
2011-03-08

select convert (varchar(20),getdate(),24)
output result
17:05:51

select convert (varchar(20),getdate(),25)
output result
2011-03-08 17:06:16.

select convert (varchar(20),getdate(),100)
output result
Mar  8 2011  4:54PM

select convert (varchar(20),getdate(),101)
output result
03/08/2011

select convert (varchar(20),getdate(),102)
output result
2011.03.08

select convert (varchar(20),getdate(),103)
output result
08/03/2011

select convert (varchar(20),getdate(),104)
output result
08.03.2011

select convert (varchar(20),getdate(),105)
output result
08-03-2011

select convert (varchar(20),getdate(),106)
output result
08 Mar 2011

select convert (varchar(20),getdate(),107)
output result
Mar 08, 2011

select convert (varchar(20),getdate(),108)
output result
17:00:33

select convert (varchar(20),getdate(),109)
output result
Mar  8 2011  5:01:09

select convert (varchar(20),getdate(),110)
output result
03-08-2011

select convert (varchar(20),getdate(),111)
output result
2011/03/08

select convert (varchar(20),getdate(),112)
output result
20110308

select convert (varchar(20),getdate(),113)
output result
08 Mar 2011 17:03:06

select convert (varchar(20),getdate(),114)
output result
17:03:31:553

Show Total in SQL

SELECT distinct(b.fieldname),CONVERT(varchar,SUM(a.fieldname),1) as total
 from tablename a, tablename b
 where  a.fieldname=b.fieldname
 group by b.tablename

Insert Record from Temp Table to Actual Table

insert into ActualTableName
Select FiledName from TempTable$
where FieldName NOT IN(select FieldName from ActualTableName)