Using STDEVP in TSQL to eliminate outliers

If you have a bell curve distribution of events, then only 68% of values will be within 1 standard deviation away from the mean (95% are covered by 2 standard deviations). Using stdev / stdevp sql function I can limit the original data set and find an average for the values that are within the appropriate number of standard deviations excluding outliers.

declare @stdtest table (colname varchar(20), colvalue int)

insert into @stdtest (colname, colvalue) values ('x', 377942)
insert into @stdtest (colname, colvalue) values ('a', 377943)
insert into @stdtest (colname, colvalue) values ('a', 2041)
insert into @stdtest (colname, colvalue) values ('h', 5)
insert into @stdtest (colname, colvalue) values ('h', 6)
insert into @stdtest (colname, colvalue) values ('b', 5)
insert into @stdtest (colname, colvalue) values ('b', 8)
insert into @stdtest (colname, colvalue) values ('h', 7)

insert into @stdtest (colname, colvalue) values ('z', 377943)
insert into @stdtest (colname, colvalue) values ('z', 50000)
insert into @stdtest (colname, colvalue) values ('z', 2041)
insert into @stdtest (colname, colvalue) values ('z', 524)
insert into @stdtest (colname, colvalue) values ('z', 50)
insert into @stdtest (colname, colvalue) values ('z', 4)
insert into @stdtest (colname, colvalue) values ('z', -100000)


select test.colname, avg(test.colvalue) stat_average, count(*) effective_cnt, min(bounds.cnt) total_cnt
from @stdtest test
inner join
    (
        select colname, STDEVP(colvalue) cstdev, AVG(colvalue) cavg, count(*) cnt
        from @stdtest
        group by colname
    ) bounds on bounds.colname = test.colname
where abs(bounds.cavg - test.colvalue) <= bounds.cstdev * 1   -- 1stdev = 68%   2stdev = 95%
group by test.colname

.NET 4.5 Improvements

.NET 4.5 Improvements for Cloud and Server Applications

http://blogs.msdn.com/b/somasegar/archive/2012/05/16/net-improvements-for-cloud-and-server-applications.aspx

Entity Framework 5 Enums and Moving Solution from EF 4.3

• Moving a solution from EF4.3 +.NET 4 to EF5 +.NET 4.5
• Using the new SQL Server Object Explorer in Visual Studio 11
• Working with the new DbLocal database
• Seeing the enum support in action : how it impacts the database, inserting and querying data with enums.

Original Julie Lerman's post

Microsoft Application Architecture Guide

The guide helps you to:

• Understand the underlying architecture and design principles and patterns for developing successful solutions on the Microsoft platform and the .NET Framework.
• Identify appropriate strategies and design patterns that will help you design your solution's layers, components, and services.
• Identify and address the key engineering decision points for your solution.
• Identify and address the key quality attributes and crosscutting concerns for your solution.
• Create a candidate baseline architecture for your solution.
• Choose the right technologies for your solution.
• Identify patterns & practices solution assets and further guidance that will help you to implement your solution.

http://msdn.microsoft.com/en-us/library/ff650706

Regex for TSQL comments

This regular expression will help you to match comments in TSQL code.
Single line comments (--)  and multiline (/**/) are supported. Multiline comments can be nested one into another. Only one level of nesting is supported.

/\*(?>(?:(?!\*/|/\*).)*)(?>(?:/\*(?>(?:(?!\*/|/\*).)*)\*/(?>(?:(?!\*/|/\*).)*))*).*?\*/|--.*?\r?[\n]|--.*?$

We used this regular expression to remove all comments from TSQL before analysis.

Inspired by http://stackoverflow.com/a/7690874/1012986

Convert flv to mp4 using ffmpeg

ffmpeg -i 9.flv -vcodec mpeg4 9.mp4

Check if files exist using PowerShell

The file controls.txt contains a list of filenames with path.
We want to find non existent files

cat .\controls.txt | Where-Object {-not (test-path $_)}

Get unique sorted lines from file and save to another file.

cat .\controls.txt | Sort-Object | Get-Unique > .\controls-sorted.txt

How to find unused controls registration in ASP.NET

In order to find unused controls registration this regular expression might be helpfull

<%@\s*Register\s+TagPrefix\s*=\s*"(?<tagprefix>[^"]+)"\s+TagName\s*=\s*"(?<tagname>[^"]+)"\s+Src\s*=\s*"[^"]+"\s*%>(?!.*?\k<tagprefix>:\k<tagname>\s+)

I used powergrep to run this regex. The "Dot matches newline" checkbox should be enabled to work properly.