-- clean up any messes left over from before: if OBJECT_ID('AllTeams') is not null drop view AllTeams go
if OBJECT_ID('Teams') is not null drop table Teams go
-- sample table: create table Teams ( id int primary key, City varchar(20), TeamName varchar(20) )
Read more →
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 →
… 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 promise to get back to writing articles on a more regular basis soon, but in the meantime, here's a comment from Nathan A. on using DISTINCT and ORDER BY:
Read more →
Here's a simple SQL Server stored procedure that you can call to process an OLAP cube using T-SQL. The parameters should be self-explanatory. To me, this is a little easier and more flexible than processing using DTS packages.
Read more →
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 →
A Simple Single-Child Model Consider the following logical data model:
There are multiple Companies Each Company has many Projects Each Project has many Tasks Each Task has a Status, selected from a global list of pre-defined Statuses.
Read more →
Most people know about cascading updates and deletes, but did you know there are two other foreign key constraint options you can use to maintain referential integrity? Read all about them in my latest article over at SQLTeam.
Read more →
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 →
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 .
Read more →
A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc).
Read more →
OK, boys and girls, it's time for the mailbag! There's lots of stuff to cover, so let's get to it! — Greg E writes:
Hello Jeff,I just found your blog and wanted to know if you could point me in the right direction or possibly toss me a solution.
Read more →
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 →
Introduction There is a very simple rule when it comes to storing (and returning) data, which I see violated all the time, making life so much more complicated for everyone involved.
Read more →
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 →
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 (@@FETCH_STATUS=0)beginexec DoSomething @ProductID fetch next from @Products into @ProductIDenddeallocate @Products Ah ha!
Read more →
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 →
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 →
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 →
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 →