SQL Round To Quarter

This is a reminder to myself how to round to the nearest fraction, in this case a quarter.
Next time I won't spend 15 minutes trying to recall the rounding formula. I'll just look it up here.

DECLARE @target smallmoney
SET @target = 597.80

--round to quarter hourSELECT ROUND( @Target/25.0, 2) * 25

-- Result = 597.75


(Originally posted 8/3/2007)

Delete Duplicates In One Statement

Using a Microsoft T-SQL extension you are able to conduct a self-join on a DELETE statement. The hitch is you cannot use an alias for the first table in the second FROM clause. This works best if there is at least someway to uniquely idenify the table. This will not work with Updateable Views since they do not allow self-joins.

DELETE FROM tblBlah
FROM tblBlah
JOIN tblBlah a
ON tblBlah.ClientName = a.ClientName
AND tblBlah.ClientId > b.ClientId


For a more complete examination see my test code below. Just copy and paste into query anaylzer and give it a run.

SET NOCOUNT ON
-- Create Duplicate temporary table
IF Object_Id('tempdb.dbo.#dups') IS NOT NULL DROP TABLE #dups
CREATE TABLE #dups(
pk int identity(1,1),
Firstname varchar(50),
LastName varchar(50))-- Populate Duplicate table
Insert into #dups (FirstName, LastName)
Values ('Jack','Sparrow')
Insert into #dups (FirstName, LastName)
Values ('Jack','Sparrow')
Insert into #dups (FirstName, LastName)
Values ('Jack','Sparrow')
Insert into #dups (FirstName, LastName)
Values ('Will','Turner')
Insert into #dups (FirstName, LastName)

Values ('Will','Turner')
Insert into #dups (FirstName, LastName)
Values ('Elizabeth','Swann ')
Insert into #dups (FirstName, LastName)
Values ('Elizabeth','Swann ') -- The Table with all duplicates
SELECT * FROM #dups -- Delete duplicates and keep the min primary key record.
-- Reverse the comparison operator to keep the
-- max primary key record.
DELETE FROM #dups
FROM #dups
JOIN #dups b
ON #dups.FirstName = b.FirstName
AND #dups.LastName = b.LastName
AND #dups.pk > b.pk -- The table de-duped
SELECT *FROM #dups

(Originally posted 9/22/2006)

SQL Search Script

In our data warehouse environment we have over 10 terabytes of data (staging and production reportable), comprised of over 5000 tables populated by over 500 SQL Agent jobs. So, when I get a question about a particular table I don't always know the answer from memory. I refer often to my SQL Search Script. This lets me know which tables, views, stored procedures and jobs relate to a particular search string.The only thing missing is SQL embedded in DTS.

DECLARE @SQL varchar (500)

DECLARE @Search varchar (200)

--Enter your search key-word or table name

SET @Search = 'SearchString'

DECLARE @DBName varchar (30)

DECLARE Databases CURSOR FAST_FORWARD FOR

IF OBJECT_ID('tempdb.dbo.#FindObjects') IS NOT NULLDROP TABLE #FindObjects

CREATE TABLE #FindObjects

(

Database_Name varchar(128) Null,

Obj_Name varchar(128) Null,

dbType char(1) Null

)

OPEN Databases

FETCH Databases INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'INSERT INTO #FindObjects '

SET @SQL = @SQL + 'SELECT DISTINCT ''' + @DBName +''', [Name], a.Type '

SET @SQL = @SQL + 'FROM ' + @DBName +'.dbo.sysobjects a '

SET @SQL = @SQL + 'LEFT JOIN ' + @DBName +'.dbo.syscomments b ON a.ID = b.ID '

SET @SQL = @SQL + 'WHERE a.Type IN (''P'',''U'',''V'')'

SET @SQL = @SQL + 'AND (b.Text LIKE ' + '''' + '%'+ @Search + '%' + ''''

SET @SQL = @SQL + 'OR a.Name LIKE ' + '''' + '%' +@Search + '%' + ''')'

EXEC(@SQL)

FETCH Databases INTO @DBName

END

CLOSE Databases

DEALLOCATE Databases

-- Objects Results

SELECT * FROM #FindObjects order by Database_Name,dbType, Obj_Name

-- Check Jobs

SELECT Job_Name = b.name, a.step_id, a.Step_Name,a.subSystem, a.command, a.database_name,a.output_file_name

FROM msdb.dbo.sysJobsteps a

Join msdb.dbo.sysjobs b on a.Job_id = b.Job_id

Where Step_name like '%' + @Search + '%'

or a.command like '%' + @Search + '%'

(Originally published 12/28/2007)

SSIS Slowly Changing Dimensions

In SQL Server Integration Services 2005 or 2008 there is a Data Flow component called the Slowly Changing Dimension Item (SCD). Unfortunately when you use the SCD wizard to create a type 2 slowly changing dimension you have to choose between using a current indicator or a valid date range technique. You can not have both, at least through the wizard. To add both you will have to edit the package after the wizard is complete.

Complete the Slowly Changing Dimension wizard. I prefer choosing the Single Column / Current Flag option for Historic Attribute Changes during the wizard.

Modify the components in the SDC update branch

1. Add a new derived column with an expression like (DT_DBTIMESTAMP) GETDATE()
2. Modify the OLEDB Command Update statement, i.e.:UPDATE [dbo].[tbl_Cost_Center_Dim] SET [Current_Flag] = ? ,Row_End_Date = ?WHERE [Cost_Center_Code] = ? AND [Current_Flag] = '1'
3. Modify the OLEDB Command, Column Mapping tab, to reflect the re-alignment the new parameter(s) added.

Modify the components in the SDC Insert path. These are the components after the Union All component.

1. Add new derived columns for begin and end effective dates. The end effective date should be a conformed standard future date such as 6/6/2079.
2. Add the new columns to the mapping of the OLEDB Insert Destination component.

In addition to using this process for Row Effective dates it is also useful for using Row Created and Row Updated dates. (Originally posted 10/10/2007)