Creating a Compound NULLIF in AVG Function With SqlServer

My use case is I’ve got a session survey column on the Silicon Valley Code Camp session evaluation page.  The results of a question like “How is this course overall” can be 0,1,2,3,4,5 where 0 means the person did not answer the question and 5 means the person chose not applicable.  I want to throw away both of these answers in my average using the SqlServer AVG function (knowing AVG does not include nulls). With some help from StackOverflow and some creative nesting, I came up with the following Sql. It feels ugly and not scalable, but it does work.  I’m open to other suggestions but thought I’d post it anyhow as a first pass just to see what others think.  I’m including the full query so it’s clear what I’m going … Continue Reading

Tip for Using SQLServer To Count By Alpha Names in a list

image.png

  So, this is not a big tip, but worth at least 10 minutes to figure it out on your own.  Here is to saving you 10 minutes:   SELECT LEFT (userlastname, 1) as alpha, count(id)FROM attendeesWHERE id IN ( SELECT attendeesid FROM AttendeesCodeCampYear WHERE codecampyearid = 6 )GROUP BY LEFT (userlastname, 1)OORDER BY LEFT (userlastname, 1) It’s pretty self explanatory.  Our case is we have two tables that we track code camp attendees.  One is the master list, and the other is a detail by year.  (6 is this year)   … Continue Reading

Writing A Transact SQL (TSQL) Procedure For SQL Server 2008 To Delete Rows From Table Safely

In this post, we will show and explain a small TSQL Sql Server 2008 procedure that deletes all rows in a table that are older than some specified date.  That is, say the table has 10,000,000 rows in it the accumulated over the past 2 years.  Say you want to delete all but the last 30 days of activity.  If you just simply say DELETE FROM table WHERE id>10000, you will cause this to happen in one transaction and likely, you will get an error.  That’s the best case.  The worst case is your system tries to do this, eventually consumes all the resources in your computer and crashes your server. … Continue Reading

Using SqlServer 2008 and TSQL Subtract 1 Hour From All Values In a DateTime Column

In this post, we’ll go briefly the process of how you would update all rows in a SQL Server 2008 table such that a particular date column will be moved back 1 hour in time.  This is actually pretty simple, but being that I typically do my work in the ORM layer (that is LINQ2SQL or Entity Framework), I just don’t do much of this.  The process I’m going to follow is first to use Microsoft SQL Server Management Studio to make sure I know what I’m doing with the Sql Server 2008 functions, then add it to an UPDATE statement. … Continue Reading

Follow

Get every new post delivered to your Inbox

Join other followers: