Check Columns’ NULL Percentage For a Given Table –T-SQL(Three Solutions)

Posted: August 30, 2012 | Author: Jingyang Li | Filed under: SQL Server | Leave a comment

Solution 1: Using dynamic query with COUNT(col) and COUNT(*).

DECLARE @DBName VARCHAR(50)=’AdventureWorks2012′,

@TABLE_NAME VARCHAR(50) = ‘Product’,

@TABLE_SCHEMA VARCHAR(50) = ‘Production’

DECLARE @sql NVARCHAR(4000)

SELECT @sql = COALESCE(@sql + ‘, ‘, ‘SELECT ‘)

+ ‘ CAST((count(*)- CAST(count( ‘

+ Quotename(column_Name)

+ ‘) as decimal(8,2)))/Count(*)*100.00 as decimal(8,2)) as [‘

+ column_Name + ‘– NULL Value %] ‘

— CASE WHEN IS_Nullable=’YES’ THEN ‘– NULL Value %] ‘ ELSE ‘–Not Nullable column]’ END

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TABLE_NAME

AND TABLE_SCHEMA = @TABLE_SCHEMA

AND IS_Nullable=’YES’

SET @sql =@sql + ‘ FROM ‘ + Quotename(@DBName) + ‘.’

+ Quotename(@TABLE_SCHEMA) + ‘.’

+ Quotename(@TABLE_NAME)

EXECUTE master..Sp_executesql @sql

–PRINT @sql

–Based on that Null value is eliminated by an aggregate operation. I choose COUNT().

–Soution 2: Using cursor.

USE [AdventureWorks2012]

SET NOCOUNT ON;

DECLARE @TABLE_NAME NVARCHAR(50) = ‘Product’,

@TABLE_SCHEMA NVARCHAR(50) = ‘Production’,

@sql NVARCHAR(4000),

@col NVARCHAR(50)

CREATE TABLE #t

(

id INT IDENTITY(1, 1),

ColName VARCHAR(50),

[NULL%] DECIMAL(8, 2)

)

DECLARE c_cursor CURSOR FOR

SELECT column_Name

FROM [INFORMATION_SCHEMA].[COLUMNS]

WHERE TABLE_NAME = @TABLE_NAME

AND TABLE_SCHEMA = @TABLE_SCHEMA

AND IS_Nullable = ‘YES’

OPEN c_cursor;

FETCH NEXT FROM c_cursor INTO @col;

WHILE ( @@FETCH_STATUS = 0 )

BEGIN

SET @sql = N’ insert into #t (ColName, [NULL%])

SELECT TOP 1 ”’ + @col

+ ”’ , (COUNT(*) over() *1.0- COUNT(‘ + @col

+ ‘) OVER()*1.0)/COUNT(*) over() *100.0 as [NULL%] FROM ‘

+ Quotename(@TABLE_SCHEMA) + ‘.’

+ Quotename( @TABLE_NAME)

— print @sql

EXEC (@sql);

FETCH NEXT FROM c_cursor INTO @col;

END

CLOSE c_cursor;

DEALLOCATE c_cursor;

SELECT ColName, [NULL%] FROM #t

DROP TABLE #t

3.Solution three:

DECLARE @sql nvarchar(4000)

DECLARE @sql2 nvarchar(4000)

DECLARE @DBName nvarchar(100)=’AdventureWorks2012′

DECLARE @Table_Name nvarchar(100)=’Product’

DECLARE @TABLE_SCHEMA nvarchar(100)=’Production’

SET @sql = ‘SELECT * into ##Cols FROM OPENQUERY([MC046537\IFBF2012],”exec(””use ‘+@DBName+ ‘ exec sp_columns ‘+ @Table_Name +’,’ + @TABLE_SCHEMA+ ””’)”)’

–print(@sql)

EXECUTE sp_executesql @sql

SELECT @sql2 = COALESCE(@sql2 + ‘, ‘, ‘SELECT ‘)

+ ‘ CAST((count(*)- CAST(count( ‘

+ column_Name

+ ‘) as decimal(8,2)))/Count(*)*100.00 as decimal(8,2)) as [‘ + column_Name + ‘– NULL Value %] ‘

FROM ##Cols

WHERE Table_QUALIFIER = @DBName AND TABLE_NAME = @TABLE_NAME AND TABLE_OWNER= @TABLE_SCHEMA AND IS_Nullable=’YES’

SET @sql2 = @sql2 +’ FROM ‘+@DBName+’.’+@TABLE_SCHEMA+’.’+ @Table_Name

–print (@sql2)

–exec(@sql2)

EXECUTE sp_executesql @sql2

drop Table ##Cols

T-SQL to Find Gap (missing sequence item) With Range Data

<code>

Declare @t Table(id int identity(1,1), FromValue decimal(6,2), ToValue

decimal(6,2))

Declare @MaxToVal decimal(6,2)

–Load sample table data with overlap range

Insert into @t

Values (90.00 , 100.01),

(90.55 , 100.00),

(100.05 , 110.00)

–Get the max ending value

SELECT @MaxToVal=max(Tovalue) FROM @t;

–Create sequense with gap

;with mycte

as (

SELECT id, Cast(FromValue * 100 AS INT) AS val FROM @t

UNION ALL

SELECT b.id, b.val + 1 AS val

FROM mycte b INNER JOIN @t a ON a.id = b.id

WHERE val < a.ToValue * 100

)

–Return all sequence–no gap from min to max values

, mycteAll as

(

Select CAST(min(FromValue)*100 as int) as val from @t

Union ALL

SELECT val + 1 as val FROM mycteAll WHERE val <@MaxToVal*100

)

–Get the missing values for gap

–SELECT Cast(val / 100.0 AS DECIMAL(6, 2)) AS Val FROM mycteAll

–EXCEPT

–SELECT Cast(val / 100.0 AS DECIMAL(6, 2)) AS Val FROM mycte

SELECT Cast(a.val / 100.0 AS DECIMAL(6, 2)) AS Val FROM mycteAll a

LEFT JOIN mycte b ON a.val=b.val WHERE b.val IS NULL

–overwrite the default maximum recursion 100

OPTION (MAXRECURSION 0)

/* Result

100.02

100.03

100.04

*/</code>

A Sliding Aggregate (multiply) Code Sample (ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

Posted: August 27, 2012 | Author: Jingyang Li | Filed under: SQL Server 2012 | 2 Comments

I came across a question at T-SQL Forum here :http://social.msdn.microsoft.com/Forums/en/transactsql/thread/ed1ac163-546d-4dce-a259-edb5eb215862

Two solutions with crecursive cte and triangular join were posted.
Here is UPDATED version (the early approaches at the end are not correct)

There are a few ways to calculate a group product with T-SQL. Itzik discussed the performance advantage
to use mathematical approach to solve the problem. http://msdn.microsoft.com/en-us/library/aa496039(v=sql.80).aspx
The mathematical approch is to use LOG/LOG10 and POWER/EXP functions to convert a multiplication into a LOG addition to get our desired result.
EXP(SUM(Log(val)) or POWER(10.,SUM(Log(val, 10)).

With the new window function in SQL Server 2012, it is very easy to calculate the aggregate within OVER clause:
OVER (PARTITION BY id ORDER BY val ROWS unbounded preceding). By using UNBOUNDED PRECEDING, the window starts at the first row of the partition.

I choose POWER OVER EXP is due to the rounding issue with large numbers for the result. See an example from this thread:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/14a92db0-7c0a-4be6-b8c4-9b2dce8863c8/
Here is the sample data and query:
–SQL Server 2012
DECLARE @t TABLE
(
k INT IDENTITY(1, 1), id INT, val INT
)

INSERT INTO @t
VALUES (1,2),(1,4),(1,3),
(1,10), (2,5), (2,6),
(3,7),(4,0),(7,2000000),(7,200000)

SELECT id, val
,POWER(10.,SUM(Log(NULLIF(val,0), 10)) OVER (PARTITION BY id ORDER BY val ROWS unbounded preceding)) product1
,EXP(SUM(Log(NULLIF(val,0))) OVER (PARTITION BY id ORDER BY val ROWS unbounded preceding)) product2

,EXP(SUM(Log(NULLIF(val,0))) OVER (PARTITION BY id ORDER BY val ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)) SlidingProduct

FROM @t

We can also get a moving PRODUCT by using ROWS BETWEEN 1 PRECEDING AND CURRENT ROW syntax as shown in the query.

Here is the one I got with SQL Server 2012:

DECLARE @t TABLE (k INT IDENTITY(1, 1), id INT, val INT )

INSERT INTO @t VALUES (1, 2),(1,3),(1, 4), (1,10),(2,5),(2,6),(3,7), (4, 0);

;WITH mycte

(

SELECT id, val,

SUM(val) OVER (PARTITION BY id ORDER BY val ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)-val AS preRow1,

Lag(val) OVER (PARTITION BY id ORDER BY val ) preRow2

FROM @t

)

SELECT id, val,

–preRow1, preRow2,

IIF(preRow1=0,val,val*preRow1) AS solution1,

ISNULL(val*preRow2,val) AS solution2 FROM mycte

1 2 2 2

1 3 6 6

1 4 12 12

1 10 40 40

2 5 5 5

2 6 30 30

3 7 7 7

4 0 0 0

To do a sliding PRODUCT, I managed to use the new SQL Server 2012 feature Rows BETWEEN 1 PRECEDING AND CURRENT ROW to get a solution:

–SQL Server 2012

DECLARE @t TABLE

(

k INT IDENTITY(1, 1), id INT, val INT

)

INSERT INTO @t

VALUES (1,2),(1,3),(1,4),

(1,10),

(2,5), (2,6),

(3,7)

;WITH mycte

(

SELECT id, val, (SUM(val) OVER (PARTITION BY id ORDER BY val ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) -val )*val AS preProd

FROM @t)

SELECT id, val, IIF(min(preProd) OVER (PARTITION BY id ORDER BY val

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)=0, IIF(preprod=0,val,preprod), min(preProd) OVER (PARTITION BY id ORDER BY val

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)*val) aProd FROM mycte

Retrieve numbers From a String

Posted: August 22, 2012 | Author: Jingyang Li | Filed under: SQL Server, SQL Server 2005, SQL Server 2008 | Leave a comment

1. Recursive CTE:

declare @s varchar(100)='abcdef923ghicde456'

declare @pos int

;with mycte as

(select @s as numCol, PATINDEX('%[^0-9]%', UPPER(@s)) pos

union all

Select cast(REPLACE(numCol, SUBSTRING(numCol, pos, 1), '') as varchar(100)) numCol,

PATINDEX('%[^0-9]%', REPLACE(numCol, SUBSTRING(numCol, pos, 1), '')) pos from mycte WHERE mycte.pos&gt;0

)

select numCol from mycte WHERE pos=0

--OPTION (MAXRECURSION 100);

2.Loop

declare @s varchar(100)='abcdef923ghicde456'

declare @pos int

SET @pos = PATINDEX('%[^0-9]%',@s);

WHILE @pos &gt; 0

BEGIN;

SET @s = REPLACE(@s, SUBSTRING(@s, @pos, 1), '');

SET @pos = PATINDEX('%[^0-9]%', @s);

END;

select @s

3. Tally table:

–http://beyondrelational.com/modules/2/blogs/70/posts/10821/extract-only-numbers-from-a-string.aspx

Declare @s varchar(100),@result varchar(100)

set @s='as4khd0939sdf78'

set @result=''

SELECT @result=@result+

case when number like '[0-9]' then number else '' end from

(

select substring(@s,number,1) as number from

(

select number from master..spt_values
where type='p' and number between 1 and len(@s)

) as t1

) as t2

select @result as only_numbers

Declare @s varchar(100),@result varchar(100)

set @s='as4khd0939sdf78'

set @result=''

SELECT @result=@result+

case when number like '[^0-9]' then number else '' end from

(

select substring(@s,number,1) as number from

(

select number from master..spt_values
where type='p' and number between 1 and len(@s)

) as t1

) as t2

select @result as only_Non-numbers

How to Identify and Remove Duplicate Indexes

Posted: August 20, 2012 | Author: Jingyang Li | Filed under: SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2012 | Leave a comment

I have followed Paul Randal’s biweekly newsletter for sometime. I tried to read it no matter how busy I was. I posted linkes from Paul’s email for my future reference. The links are two postings by Kimberly Tripp detailed about how to identify duplicate indexes and the scripts to remove them.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/UnderstandingDuplicateIndexes.aspx

http://www.sqlskills.com/BLOGS/KIMBERLY/post/RemovingDuplicateIndexes.aspx

Reporting Services (SSRS) Multi Value Parameter (Integer Type )

Posted: August 8, 2012 | Author: Jingyang Li | Filed under: SQL Server 2008, SQL Server Reporting Services (SSRS) | 1 Comment

There is an easy way to setup a report integer type multiple value parameter without helper function or T-SQL manipulation.
From the Report Parameter Properties:

Check the parameter (ids for example) to allow multiple values

From Dataset Property:

Set the parameter value with this experission:

=Parameters!ids.Value

(The following works with built-in functions)

=Split(CStr(Join(Parameters!ids.Value,”,”)),”,”)

The WHERE clause in query part looks like this

WHERE id IN (@ids)
–other condition …
Of course, you can use Filter from Dataset Property section to do the same with small dataset. (remove the parameter from the WHERE clause).

Get Last Week’s Records with T-SQL

Posted: August 2, 2012 | Author: Jingyang Li | Filed under: SQL Server, SQL Server 2005, SQL Server 2008 | 3 Comments

You can use DATEADD and DATEDIFF functions to get the start time of a particular week from your current date.

You can compare your datetime column value with the start time of the week and retrieve all records within the range.

Here is the code snippet:

SELECT DATEADD(week, DATEDIFF(week,0,GETDATE())-1,-1) BeginningOfLastWeek,

DATEADD(week, DATEDIFF(week,0,GETDATE()),-1) BeginningOfThisWeek,

DATEADD(week, DATEDIFF(week,0,GETDATE())+1,-1) BeginningOfNextWeek

Last week’s data

…WHERE mydate >= DATEADD(week, DATEDIFF(week,0,GETDATE())-1,-1)

AND mydate < DATEADD(week, DATEDIFF(week,0,GETDATE()),-1)

This week’s data

…WHERE mydate >= DATEADD(week, DATEDIFF(week,0,GETDATE()),-1)

AND mydate < DATEADD(week, DATEDIFF(week,0,GETDATE())+1,-1)

Get this Sunday or Monday and last Sunday or Monday:

—Start from last Monday
SELECT DATEADD(WEEK, DATEDIFF(WEEK, ‘1900-01-01’, CURRENT_TIMESTAMP)-1, ‘1900-01-01’)
—Start from this Monday
SELECT DATEADD(WEEK, DATEDIFF(WEEK, ‘1900-01-01’, CURRENT_TIMESTAMP), ‘1900-01-01’)
—Start from Sunday
SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP ), Cast(CURRENT_TIMESTAMP as date))
—Start from last Sunday
SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP )-7, Cast(CURRENT_TIMESTAMP as date))

Jingyang Li

SQL Server

Check Columns’ NULL Percentage For a Given Table –T-SQL(Three Solutions)

T-SQL to Find Gap (missing sequence item) With Range Data

A Sliding Aggregate (multiply) Code Sample (ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

Retrieve numbers From a String

How to Identify and Remove Duplicate Indexes

Reporting Services (SSRS) Multi Value Parameter (Integer Type )

Get Last Week’s Records with T-SQL

Recent Posts

Archives

Categories

Meta