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.

 

 

SELECT SERVERPROPERTY('Edition') AS Edition,

       SERVERPROPERTY('ProductLevel') AS ProductLevel,

       SERVERPROPERTY('ProductVersion') AS ProductVersion

Edition	ProductLevel	ProductVersion
Enterprise Edition	SP2	9.00.3282.00

 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’d heard about short-circuiting before but had never seen it actually applied.  I guess the main benefit of the OrElse operator is that it can improve performance if the “bypassed” condition is cumbersome.  It’s basically an OR statement, but if the first expression evaluates as TRUE, it doesn’t need to evaluate the second expression.  Normally, both expressions would be evaulated, but in reality, if the first one is TRUE, the whole condition is TRUE, so why evaluate the second one, right?

result

Required. Any Boolean expression.

expression1

Required. Any Boolean expression.

expression2

Required. Any Boolean expression.

If expression1 is	And expression2 is	Then result is
True	(not evaluated)	True
False	True	True
False	False	False

It’s this simple:

Dim A As Integer = 10
Dim B As Integer = 8
Dim C As Integer = 6
Dim myCheck As Boolean
myCheck = A > B OrElse B > C   ' True. Second expression is not evaluated.
myCheck = B > A OrElse B > C   ' True. Second expression is evaluated.
myCheck = B > A OrElse C > B   ' False.

If MyFunction(5) = True OrElse MyOtherFunction(4) = True Then
' If MyFunction(5) is True, MyOtherFunction(4) is not called.
   ' Insert code to be executed.
End If

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