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

 

 

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/

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   

Here’s are two quick ways to Delete data in one table based on another table.  This works perfectly in an ETL job where you have a staging table that indicate whether to Insert, Update, or Delete the data in the other table.

The first one uses an inner join, the second uses a simple EXISTS clause.

DELETE b

  FROM [dbo].[TABLE_1] b

 INNER JOIN [dbo].[TABLE_2] w

    ON b.[ROW_ID] = w.[ROW_ID]

 WHERE w.[CTRL_CD] = 'D'

 

 

DELETE b

  FROM [dbo].[TABLE_1] b

 WHERE EXISTS (

            SELECT 1

          FROM [dbo].[TABLE_2] w

         WHERE b.[ROW_ID] = w.[ROW_ID]

           AND w.[CTRL_CD] = 'D')

 

Oracle has a pseudo-column called ROWNUM that allows you to display the row number for each row.  There are infinite reasons why you might want to use the ROWNUM, but in SQL it’s not nearly as easy to determine.

 

In Oracle, it’s simply

SELECT ROWNUM from table_name

 

In SQL 2005+, there is actually a ROW_NUMBER() function, but you have to give it some more information to use it.

ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

 

For example:
use [Database]
select row_number() over (order by data_srce_id desc) rownum

      ,data_srce_id
from data_srce 

 

rownum	data_srce_id
1	8
2	7
3	6
4	5
5	4
6	3
7	2
8	1
9	-1

 

However, in SQL 2000 it’s a little more difficult.  The only way I’ve found is using the IDENTITY() function, which requires an INTO statement, so you pretty much have to load the results into a #temporary table and then select from the #temporary table like this:

use [Database]

SELECT IDENTITY(int, 1,1) id

      ,t1.[column1]           col1

      ,t1.[column2]           col2

      ,t1.[column3]           col3

      ,t2.[column2]           col4

  INTO #Temp

  FROM [dbo].[Table] t1

 INNER JOIN [dbo].[Table2] t2

    ON t.[column] = t2.[column]

 

select *

  from #Temp

Here’s a quick and dirty way to send email via MSSQL.  For some reason, we’re not allowed to send email using the Email Task in SSIS, so I chose this option.  Unfortunately, even if you have access to execute sp_SQLSMTPMail, you might not have access to run xp_cmdshell.

 

I ended up having to create my SSIS package with a SQL Task that runs this proc, then I had to run it with a SQL Agent Job with a proxy account that had access to xp_cmdshell.  Fun stuff.

 

 

 

 

declare @developerperson varchar(80)

declare @message_body varchar(1000)

 

set @message_body =

‘Dear ‘ + @developerperson + ‘Use \n for line breaks and escape your ”s’

 

 

EXECUTE dbo.sp_SQLSMTPMail

      @vcTo = ‘You@you.com’,

      @vcCC= ‘Them@them.com’,

      @vcSubject = ‘Super Cool SQL Email’,

      @vcBody = @message_body,

      @vcFrom = ‘Me@me.com’,

      @vcSMTPServer = ‘SMTPName’,

      @vcSenderName = ‘Some One’,

      @vcServerName = @@SERVERNAME –no need to specify because this is the default anyway

Or, lets say you want to email query results as an attachment:

EXECUTE dbo.sp_SQLSMTPMail

  @vcTo=‘you@you.com’

, @vcCC=‘them@them.com’

, @vcSubject=‘Here is your wonderful report that is terribly formatted as a txt file attachment’

, @vcquery= ‘select col1, col2, col3, col4 from database.schema.table’

 

 

 

 

 

 

 

 

Here’s the actual Proc definition: Read more…

If you’re trying to delete the first n records in a table, here’s how to do it in SQL 2005: Read more…

I recently needed a way to execute a script overnight to count the number of records that were being updated in a given hour.  I needed it to run at a specific time and to execute in 10 minute incriments and stop .  I was hoping to log the results to a file, but this does just as well.  When it’s done executing, it spits it all to the output window so I can see it in the morning when I get back.

 

This script below will start at 11pm and end at midnight and it will execute every 10 minutes and display the current time and the count.  You could just as easily use a counter and have it execute a certain number of  times.

 

–don’t even start until 11pm

WAITFOR TIME ’23:00:00.000′

declare @endtime datetime

set @endtime = cast(’2009-05-09 00:00:00.000′ as datetime)

–stop at midnight

while getdate() < @endtime

begin

      –every 10 minutes, execute

      WAITFOR DELAY ’00:10:00′;

      select getdate(), count(*)

      from tablename

      where datetimecolumn > ’5/7/2009′

      and datetimecolumn < ’5/8/2009′

end

 

 

Kudos to Jerry for helping me with this.