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/

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;

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.

 

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

 

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

This is a must-read if you’re using WordPress and want some SEO tips. 

1. The basic technical optimization: simplest stuff, highest rewards
2. Permalinks
3. Optimize your Titles for SEO
4. Optimize your Descriptions
5. Optimize the More text
6. Image Optimization
7. Template optimization
8. Breadcrumbs
9. Headings
10. Clean up your code
11. Aim for speed
12. Rethink that Sidebar
13. Advanced technical optimization: preventing duplicate content
14. Noindex, follow archive pages
15. Disable unnecessary archives
16. Pagination
17. Nofollowing unnecessary links
18. Altering your blog’s structure for high rankings
19. Pages instead of posts
20. New wine in an old bottle: use well ranking-posts to rank even better
21. Linking to related posts
22. Conversion optimization: get those readers to subscribe!
23. Comment optimization: get those readers involved
24. How should you get people to comment
25. Bond with your commenters
26. Keeping people in the conversation
27. Off site blog SEO
28. Follow your commenters
29. Use Twitter
30. Find related blogs, and work them
31. Conclusion