https://weblogs.sqlteam.com/jeffs/ Recent content on Jeff Smith Blog Hugo -- gohugo.io nospam@nospam.xyzasdf (Jeff Smith) nospam@nospam.xyzasdf (Jeff Smith) Copyright © 2018 - Jeff Smith Fri, 11 May 2012 10:10:51 -0500 https://weblogs.sqlteam.com/jeffs/2012/05/11/why-to-avoid-select-from-tables-in-your-views/ Fri, 11 May 2012 10:10:51 -0500 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2012/05/11/why-to-avoid-select-from-tables-in-your-views/ -- 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) ) https://weblogs.sqlteam.com/jeffs/2010/08/30/sql-server-calculate-median/ Mon, 30 Aug 2010 07:32:11 -0500 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2010/08/30/sql-server-calculate-median/ 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. https://weblogs.sqlteam.com/jeffs/2009/10/27/61031/ Tue, 27 Oct 2009 12:50:54 -0500 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2009/10/27/61031/ … 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. https://weblogs.sqlteam.com/jeffs/2008/12/23/60803/ Tue, 23 Dec 2008 09:25:35 -0600 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2008/12/23/60803/ 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: https://weblogs.sqlteam.com/jeffs/2008/10/27/process-olap-cube-with-t-sql/ Mon, 27 Oct 2008 15:26:05 -0500 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2008/10/27/process-olap-cube-with-t-sql/ 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. https://weblogs.sqlteam.com/jeffs/2008/10/16/60734/ Thu, 16 Oct 2008 16:04:35 -0500 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2008/10/16/60734/ 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! https://weblogs.sqlteam.com/jeffs/2008/08/13/examining-database-primary-keys/ Wed, 13 Aug 2008 11:06:48 -0500 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2008/08/13/examining-database-primary-keys/ 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. https://weblogs.sqlteam.com/jeffs/2008/08/13/sql-server-set-null-set-default-foreign-key-constraints/ Wed, 13 Aug 2008 09:56:49 -0500 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2008/08/13/sql-server-set-null-set-default-foreign-key-constraints/ 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. https://weblogs.sqlteam.com/jeffs/2008/08/06/60669/ Wed, 06 Aug 2008 11:43:26 -0500 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2008/08/06/60669/ 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. https://weblogs.sqlteam.com/jeffs/2008/07/31/week-days-between-two-dates/ Thu, 31 Jul 2008 12:39:43 -0500 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2008/07/31/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 . https://weblogs.sqlteam.com/jeffs/2008/07/24/60657/ Thu, 24 Jul 2008 09:12:33 -0500 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2008/07/24/60657/ 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). https://weblogs.sqlteam.com/jeffs/2008/07/16/60652/ Wed, 16 Jul 2008 16:32:51 -0500 nospam@nospam.xyzasdf (Jeff Smith) https://weblogs.sqlteam.com/jeffs/2008/07/16/60652/ 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.