Jeff Smith Blog

ASP - Report Class with Grouping, Headers, Footers and more

Mon Nov 17, 2003 by Jeff Smith in  asp

If you use ASP, ADO and SQL Server, check out my new article at: http://weblogs.sqlteam.com/jeffs/posts/526.aspx It includes code for a VBScript class which makes writing HTML reports based on an ADO recordset with group headers, footers, and subtotals extremely easy. Read more →

ASP Report Class

Mon Nov 17, 2003 by Jeff Smith

(Note: this is definitely an ASP topic, but because it relates directly to report-writing and using SQL Server easily and efficiently, I wanted to put an article here.) Regular old ASP with ADO works great for printing the contents of recordsets as web pages, and I know many of us out there still use these technologies. Read more →

SQL WHERE clauses: Avoid CASE, use Boolean logic

Fri Nov 14, 2003 by Jeff Smith in  t-sql, techniques, efficiency

As some of you may know, I recommend to avoid using CASE expressions in the WHERE clause of a query to express conditional logic. I prefer to have everything translated to simple ANDs, ORs and NOTs to keep things a) portable, b) easier to read and c) efficient. Read more →

Keep those SQL Crosstabs flexible!

Fri Oct 24, 2003 by Jeff Smith in  t-sql, crosstabs-pivoting-data, techniques

I recently helped someone with performing some simple cross tabs (or pivots) in SQL, such as: Select CustomerID, SUM(CASE WHEN type = 'Land' THEN Amount ELSE 0 END) as Land, SUM(CASE WHEN type = 'Sea' THEN Amount ELSE 0 END) as Sea, SUM(CASE WHEN type = 'Air' THEN Amount ELSE 0 END) as Air FROM SomeTable GROUP BY CustomerID Read more →

Comparing sets of rows from two tables

Fri Oct 3, 2003 by Jeff Smith in  t-sql, joins-relations

I thought I'd post a blog showing a technique I haven't seen elsewhere. This is from a recent post of mine at the SqlTeam forums. The problem: you have two tables, each containing mutiple rows of data per “SetID”. Read more →

PARSENAME() - a simple way to parse (some) strings in SQL

Tue Sep 30, 2003 by Jeff Smith in  miscellaneous, t-sql, code-library-sql

Handy reminder: The PARSENAME() function can be useful for parsing small strings. It returns parts 1-4 (working right to left) of a string, with each part delimited by periods. For example: Read more →

Hello All !

Fri Sep 26, 2003 by Jeff Smith in  miscellaneous

This also is my first ever blog. Very nice! I am definitely looking forward to the content on this page, we've got some great people here and the posts should be informative and I'm sure quite entertaining as well! Read more →