In ASP.NET, we often would like to output "grouped" data on our web pages, like this:
Customer Product Sales-------- ---------- -----ABC FoundationProduct 1 $200Product 2 $437Product 3 $523The XLZ CompanyProduct 1 $240Product 2 $892Product 3 $395 The easiest way to do this is with nested Repeater controls; one for the outer group (Customers, this case), and within that Repeater's ItemTemplate we'd have another Repeater control for the details (Products).
Read more →
In this post, I showed a simple way to create simple but useful Date and Time user-defined data types in SQL Server 2000. Here's how to do it in SQL Server 2005, without the need for CLR types or anything fancy.
Read more →
Applying a Sub-Query, Joining a Derived Table … I think the easiest way to think of CROSS APPLY is that it is like doing a CROSS JOIN with a correlated sub-query instead of a derived table.
Read more →
As David Letterman would say, wake the kids, call the neighbors, it's time for The Mailbag! Just some quickies today. Christopher writes:
Greetings Jeff, First and foremost, great job with all of the blogs.
Read more →
On a particular real estate website, we have a "Property Summary" section that contains each property's name, the address, a phone number (if it exists), and a contact email address (if it exists).
Read more →
If you read this blog on a regular basis, you probably know that a frequently covered topic is dates and times and the use of the DateTime data type. Well, along those same lines, my latest article has just been published over at SQL Team: Working with Time Spans and Durations in SQL Server.
Read more →
Have you ever seen (or written) code like this:
select distinct(employeeID), salary from salaryhist That compiles and executes without returning any errors. I've seen that attempted many times over the years, and of course people think DISTINCT is "broken" and "not working" because they see multiple rows for each employeeID.
Read more →
I had previously written about the danger of Criteria on Outer Joins, but recently another situation popped up that occasionally causes confusion with OUTER JOINS that I thought I might address.
Read more →
Introduction A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating multiple values into single CSV string columns. For example, taking data like this:
Read more →
A web application I have inherited uses data from a stored procedure that returns two columns: Description and Override. Basically, for each item returned, the web page should display the Description unless an Override is provided, in which case the Override is displayed.
Read more →
use Northwind create procedure Test1 @MinDate datetime = null, @MaxDate datetime = null, @CustomerID nchar(5) = null, @FreightMin money = null, @FreightMax money = nullas begin set @MinDate = isnull(@MinDate,'1/1/1900') set @MaxDate = isnull(@MaxDate,'12/31/2999') set @CustomerID = isnull(@CustomerID,'%') set @FreightMin = isnull(@FreightMin,-99999) set @FreightMax = isnull(@FreightMax,99999) select * from Orders where OrderDate between @MinDate and @MaxDate and CustomerID like @CustomerID and Freight between @FreightMin and @FreightMax endgocreate procedure Test2 @MinDate datetime = null, @MaxDate datetime = null,@CustomerID nchar(5) = null, @FreightMin money = null, @FreightMax money = nullasbeginselect * from Orders where (@MinDate is null or OrderDate >= @MinDate) and (@MaxDate is null or OrderDate <= @MaxDate) and(@CustomerID is null or CustomerID = @CustomerID) and(@FreightMin is null or Freight >= @FreightMin) and(@FreightMax is null or Freight <= @FreightMax)end go – test it out: exec test1 '2/1/1996','9/20/1996', N'BERGS'exec test2 '2/1/1996','9/20/1996', N'BERGS' exec test1 exec test2 exec test1 @FreightMin=44, @FreightMax=48 exec test2 @FreightMin=44, @FreightMax=48
Read more →
Often, we need to create a flexible stored procedure that returns data that is optionally filtered by some parameters. If you wish to apply a filter, you set the parameter to the necessary value, if not, you leave it null.
Read more →
Introduction Previously, I wrote about grouping transactions by month. Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month.
Read more →
When you need to summarize transactional data by Month, there are several ways to do it, some better than others. What to ultimately choose depends on your needs. Techniques to Avoid
Read more →
Speaking of dates and times, there's a nice post from Ravi. S. Maniam over at the msdn blogs regarding the new and exciting Enhancements in Date and Time Data Types for SQL Server 2008.
Read more →
Every now and then I see T-SQL code written like this:
select somecolumn as 'columnname'from sometable Notice that 'columnname', despite its appearance, is not a string literal or a string expression, it is the alias that we are assigning to the column somecolumn.
Read more →
The Importance of Data Types Imagine that SQL Server only provided two data types: the MONEY data type to store numeric values, and VARCHAR to store text. If you are designing a database in this scenario and you need to store or return integer values, which data type – MONEY or VARCHAR – would you use?
Read more →
Ah … primary keys … such a topic! When discussing what columns to define as a primary key in your data models, two large points always tend to surface:
Surrogate Keys versus Natural Keys Normalization These can be very complicated and sometimes polarizing things to debate.
Read more →
Dealing with poorly designed databases is a simple and common fact of life for programmers. It happens, sometimes due to lack of experience or education, or sometimes because business requirements were never analyzed properly or they changed.
Read more →
I've written a two part article on using SQL GROUP BY clauses over at SQLTeam.com. It's always a common topic of discussion and confusion amongst beginner and intermediate SQL programmers alike, so I thought I'd write a fairly long and hopefully comprehensive piece that takes a common summary report request and works towards the solution step-by-step.
Read more →