Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

How to calculate Median in SQL Server

Nothing earth-shattering here, I was just helping out a colleague with this so I thought I'd post up the example I gave him. – sample table:create table People(Person varchar(1) primary key,City varchar(10),Age int)go– with some sample data: insert into People select 'A','Boston',23 union all – odd #select 'B','Boston',43 union allselect 'C','Boston',29 union allselect 'D','Chicago',15 union all – single #select 'E','NY',12 union all – even #select 'F','NY',55 union allselect 'G','NY',57 union allselect 'H','NY',61go– here's our query, showing median age per city: select city,AVG(age) as MedianAgefrom (select City, Person, Age, ROW_NUMBER() over (partition by City order by Age ASC) as AgeRank,COUNT(*) over (partition by City) as CityCountfromPeople) xwherex. Read more →

Is it just me …

… or is about time I got back to some blogging? Legacy Comments Ibleif 2009-10-27 re: Is it just me ... It's not just you. Read more →

I'm back … with some news!

I apologize for not posting any new content in quite some time, but now I am back and will soon start posting on a (hopefully!) regular basis once again. The reason for my hiatus was a pretty good one, though: I recently became a father with the birth of my son Benjamin on October 2, 2008! Read more →

Database Column Names != Report Headings

Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer.  For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the previous year for each customer. Read more →

How To Calculate the Number of Week Days Between two Dates

If the start date and end date are both week days, then the total number of week days in between is simply: (total difference in days) - (total difference in weeks) * 2 or DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2 … since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i. Read more →

DataViewReader Class

public class DataViewReader : IDataReader { DataView _source; IEnumerator _enum; bool _closed = true; public DataViewReader(DataView source) { _source = source; } DataRowView current { get { return (DataRowView)_enum. Read more →

The Joy of Blog Feedback

Introduction I have been writing my little blog here for some time now, and my favorite part of doing this is of course the feedback. It's always great to hear from the readers, to have mistakes corrected, to debate various topics and techniques, and to learn a lot about SQL and the various topics I discuss here. Read more →

The Truth about "Cursor Busting" in SQL

Let's say you are called in to troubleshoot a stored procedure that is performing poorly. You dive in to investigate and this is what you find: create procedure ProcessProductsasdeclare @Products cursor, @ProductID intset @Products = cursor for select ProductID from Products order by ProductIDopen @Productsfetch next from @Products into @ProductIDwhile (@@FETCHSTATUS=0)beginexec DoSomething @ProductID fetch next from @Products into @ProductIDenddeallocate @Products Ah ha! Read more →

Log Buffer #98

Hello and welcome to the 98th edition of Log Buffer. My name is Jeff Smith and I will hosting this week's exciting episode. If, for some reason, you are not completely satisfied with this edition, simply write in and complain to Dave over at The Pythian Group and you will receive Log Buffer #99 absolutely free! Read more →

Implementing "Interfaces" in SQL

My latest article has just been published over at SQLTeam: Implementing Table Interfaces When I wrote a Table Inheritance article a few months back, the technique shown was pretty standard and straight-forward. Read more →

Need an Answer? Actually, No … You Need a Question

Welcome! The reason you were directed here is because you need assistance, and I am here to help. I am not, however, here to provide you with any answers! You see, it looks like the assistance you need is not finding an answer; it is rather that you need assistance finding a question. Read more →

GROUP BY ALL

Here's an obscure piece of SQL you may not be aware of: The "ALL" option when using a GROUP BY. Consider the following table: Create table Sales(SaleID int identity not null primary key,CustomerID int,ProductID int,SaleDate datetime,Qty int,Amount money)insert into Sales (CustomerID, ProductID, SaleDate, Qty, Amount)select 1,1,'2008-01-01',12,400 union allselect 1,2,'2008-02-25',6,2300 union allselect 1,1,'2008-03-02',23,610 union allselect 2,4,'2008-01-04',1,75 union allselect 2,2,'2008-02-18',52,5200 union allselect 3,2,'2008-03-09',99,2300 union allselect 3,1,'2008-04-19',3,4890 union allselect 3,1,'2008-04-21',74,2840SaleID CustomerID ProductID SaleDate Qty Amount———– ———– ———– ———————– ———– ———————9 1 1 2008-01-01 00:00:00. Read more →