Rick Caminiti dotCOM

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION getWeekByDoW

(

      – Add the parameters for the function here

      @date datetime,

    @day    varchar(3)

)

RETURNS datetime

AS

BEGIN

      DECLARE @dowNum   int,

                  @truncDate datetime,

                  @retDate datetime;

 

      select @truncDate = cast(convert(varchar, @date, 101) as datetime)

           ,@dowNum =

                  case

                        when @day = ‘Sun’

                              then 1

                        when @day = ‘Mon’

                              then 2

                        when @day = ‘Tue’

                              then 3

                        when @day = ‘Wed’

                              then 4

                        when @day = ‘Thu’

                              then 5

                        when @day = ‘Fri’

                              then 6

                        when @day = ‘Sat’

                              then 7

                        else 0

                  end

 

      select @retDate = DATEADD(DD, @dowNum - DATEPART(DW, @truncDate), @truncDate)

      RETURN @retDate

END

GO

 

select      getWeekByDoW(getdate(), ‘Sun’) Sun,

            getWeekByDoW(getdate(), ‘Mon’) Mon,

            getWeekByDoW(getdate(), ‘Tue’) Tue,

            getWeekByDoW(getdate(), ‘Wed’) Wed,

            getWeekByDoW(getdate(), ‘Thu’) Thu,

            getWeekByDoW(getdate(), ‘Fri’) Fri,

            getWeekByDoW(getdate(), ‘Sat’) Sat

 

Sun	Mon	Tue	Wed	Thu	Fri	Sat
6/28/2009	6/29/2009	6/30/2009	7/1/2009	7/2/2009	7/3/2009	7/4/2009

 

I needed to loop through a large table by date, one month at a time and process the data.  This is the basic cursor that I wrote in T-SQL.  It’s pretty straight-forward.

First, you declare the cursor as a select statement.

Then you open the cursor (make sure you CLOSE it and DEALLOCATE it when you’re done!!)

Declare cursor-scope variables to fetch the data into and fetch the data from the cursor.

Loop through the data while the fetch status is “SUCCESS”.

Then check to make sure the fetch status indicates that the row was loaded properly and do whatever processing you need to do.

When done processing, fetch the next row and continue looping.

After the looping, close the cursor and deallocate it from memory.

DECLARE cur CURSOR

      FOR select distinct  cast(convert(varchar(25),dateadd(dd,-(day([date_column])-1),[date_column]),101) as datetime) begdate

                                    ,cast(convert(varchar(25),dateadd(dd,-(day(dateadd(mm,1,[date_column]))-1),dateadd(mm,1,[date_column])),101) as datetime) enddate

              from [dbo].[StagingTable]

             order by 1;

OPEN cur

      DECLARE @begdate datetime, @enddate datetime;

      FETCH NEXT from cur INTO @begdate, @enddate

      WHILE (@@FETCH_STATUS <> -1)

      BEGIN

            IF (@@FETCH_STATUS <> -2)

            BEGIN

                        INSERT INTO [dbo].[DestTable]

                                       ([col1]

                                       ,[col2]

                                       ,[col3]

                                       ,[col4]

                                       ,[col5])

                        SELECT [col1]

                                ,[col2]

                                ,[col3]

                                ,[col4]

                                ,[col5]

                          FROM [dbo].[StagingTable]

                         WHERE [date_column] >= @begdate

                           AND [date_column] < @enddate;

/*SELECT @begdate begdate, @enddate enddate, @@ROWCOUNT insrows;*/

                        DELETE

                          FROM [dbo].[StagingTable]

                         WHERE [date_column] >= @begdate

                           AND [date_column] < @enddate;

/*SELECT @begdate begdate, @enddate enddate, @@ROWCOUNT delrows;*/

            END  

      FETCH NEXT from cur INTO @begdate, @enddate

      END

CLOSE cur

DEALLOCATE cur

GO

 

 

Return value	Description
0	The FETCH statement was successful.
-1	The FETCH statement failed or the row was beyond the result set.
-2	The row fetched is missing.

 

 Somehow, there’s no built-in function equivalent to Oracle’s LPAD or RPAD in SQL.  There are several ways to do this, in fact, I posted another way to do RPAD at http://rickcaminiti.com/bi/t-sql-equivalent-of-oracles-rpad/.  Unfortunately, this leaor
ves no way to do an LPAD.

So here’s the scenario:  You have, let’s say an account number that is stored as a number in one system and as a char in another system with leading zeros.  So, 123 might look like 000123 in the other system.  So if you need to represent 123 as 000123, you need to turn it into a char(6) and add 3 zeros to the begining.  That’s exactly what this LPAD function would do. 

CREATE FUNCTION LPAD

               (@SourceString VARCHAR(MAX),

                @FinalLength  INT,

                @PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

  BEGIN

    RETURN

      (SELECT Replicate(@PadChar,@FinalLength - Len(@SourceString)) + @SourceString)

  END

GO

 

 

REATE FUNCTION RPAD

               (@SourceString VARCHAR(MAX),

                @FinalLength  INT,

                @PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

  BEGIN

    RETURN

      (SELECT @SourceString + Replicate(@PadChar,@FinalLength - Len(@SourceString)))

  END

GO

 

 In the example above, you would accomplish this like so:

EXEC dbo.LPAD(Cast(123 AS VARCHAR),6,'0')
OR

SELECT NewString = dbo.LPAD(Cast(123 AS VARCHAR),6,'0')

Here’s where I found it. http://www.sqlusa.com/bestpractices2005/padleadingzeros/

In SQL Server, a Timestamp field helps to keep track of changes to a table in a Database.
To use it, you simply define a field without a name, and a data type of TIMESTAMP.
SQL Server automatically inserts an internal identifier into the column whenever a row is inserted or updated. To get the current value use ‘SELECT @@DBTS.

 

CREATE TABLE .t2

      (

      a int NULL,

      b int NULL,

      timestamp NULL

      )  ON [PRIMARY]

GO

COMMIT

 

 

 

insert into t

(a, b)

values (1, 2)

 

SELECT @@DBTS DBTS

DBTS

0x00000000000007E6

 

 

select * from t

a	b	timestamp
1	2	0x00000000000007E2
1	2	0x00000000000007E3
1	2	0x00000000000007E4
1	2	0x00000000000007E5
1	2	0x00000000000007E6

 

Short and sweet. Accept a varchar input parameter, try to convert it to a number. If the conversion fails for any reason, return FALSE, else, return TRUE.

CREATE OR REPLACE FUNCTION is_numeric (

   p_data   VARCHAR2

)

   RETURN NUMBER

IS

   v_number   NUMBER;

   o_bool     BOOLEAN := TRUE;

BEGIN

   BEGIN

      v_number := p_data;

   EXCEPTION

      WHEN OTHERS THEN

         o_bool := FALSE;

   END;

 

   RETURN o_bool;

END isnumber;

Just stumbled upon this site that has a great list of common SQL Server date formats. It’s never easy in SQL to format dates, but with this list, it should be simple.

This also includes 3 simple ways to get JUST the date portion of a datetime field. When you’re just starting with SQL, this is about the most common question.


CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)


CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/' +
CAST(DAY(@pInputDate) AS VARCHAR(2)) AS DATETIME)


CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) AS DATETIME)


http://www.sql-server-helper.com/tips/date-formats.aspx

I found this post about a more efficient way to get the table rowcount from a given table.

SELECT COUNT(*) statements make a full table scans to return the total table’s row count, it can take a lot of time and a lot of “energy” for a large table.

There is another way to determine the total row count in a table by using the sysindexes system table. There is a column (ROWS) in it that contains the total row count for each table in your database.

You can use the syntax below to speed up the query by several times.  You can see this by setting statistics on, as shown below.

SET STATISTICS IO ON
GO

SELECT count(*) FROM tbTest
GO

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tbTest') AND indid < 2
GO

SET STATISTICS IO OFF
GO

 

 

Table ‘tbTest’. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0.
rows       
———–
10000

(1 row(s) affected)

Table ’sysindexes’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

 

I always thought the only way to have a computed or derived column in SQL was to use some kind of a view in place, but I found out that you can actually create a computed column on a table and that you can even “persist” the data and have it indexed.

I’m only guessing that behind the scenes, SQL treats this somewhat like a trigger, like a before-trigger, where just prior to the transaction being posted, the calculations are applied and the column is populated, then the full transaction is applied.

Here’s the article where I found this information.

And here’s an example:

use database

 

create table t

( a int, b int);

 

--adding the computed column

alter table t add

x as case a 

            when -1 then

                  b

            else

                  a

            end persisted not null

--using "persisted" is required if you want to make column "not null".  I believe "persisted" is somewhat implied if computed column is derived from PK columns.

 

--just showing that you can create an index on the new column

create index z on t (x)

 

 

insert into t values (1, -1)

insert into t values (2, -1)

insert into t values (-1, 4)

insert into t values (-1, 3)

insert into t values (-1, -1)

 

select * from t

 

 

 

--you can see that the computed column worked.

a       b       x     

1       -1      1     

2       -1      2     

-1      4       4     

-1      3       3     

-1      -1      -1   

In a like statement, “%” is a wildcard for everything after or before it and “_” is a wildcard for one character.  Observe:

LIKE ‘%Z’ would return ANYthing that ended with Z.

 

LIKE ‘%Z%’ would return ANYthing with a Z in it.

 

LIKE ‘Z__’ would return anything that started with Z and had at least 2 characters after it, like:

ZOO, ZXY, ZAP

but would not return:

ZA, ZO, Z, or XYZ

 

LIKE ‘Z%’ would return anything that STARTED with Z.

 

LIKE ‘_Z%’ would return anything that had one character, then a Z, then anything or nothing else like:

YZX, YZ, ZZZ

but would not return:

XYZ, ABCZ

 

However, if you want to look for the literal value of ‘%’ or ‘_’, you need to escape it.  For instance, if you want to look for anything that begins with ‘Z_’, then you would need to escape the ‘_’, otherwise, you would be looking for anything that started with ‘Z’ followed by one wildcard character ‘_’, followed by a wildcard of any number of characters ‘%’, returning anything that just started with ‘Z’ and had at lease one character after it.

select table_catalog, column_name, table_schema, table_name
from information_schema.columns
where column_name like 'Z\_%' escape '\'

This would only return data that started with 'Z_'.

I posted earlier on how to generate a rownumber in SQL 2000, but that would not work as a source query in SSIS for some reason.  Without having to create a stored procedure or temp tables based on other temp tables, I wanted a simple way to generate this rownum in SSIS.  I was considering something with variable expressions, but then I found a way to do it with a Script Component.

Drag a Script Component Task into your DataFlow and connect it between your source and destination tasks.  Open it up and click “Inputs and Outputs”.  Double Click “Output 0″ and highlight “Output Columns” and click Add Column.  For this code to work, name that new column “rownum“.

Then open the script tab and click “Design Script” and paste this code over what is already there:

  

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 

Public Class ScriptMain

    Inherits UserComponent

    Dim counter As Integer = 0

 

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

 

        Row.rownum= counter

        counter = counter + 1

    End Sub 

End Class