This kind of vertical to horizontal rotation cannot be done with pure sql. You will need to resort to a function. Horizontal to vertical rotation can be done using case...

When I set @date1 to Sun '2006-11-05' and @date1 to Wed '2006-11-01' your code returns 48 hours. That doesn't look right to me.

Oops, line near end

set @diff=datediff(d,@startdate,@padded_enddate)+1

shoud read

set @diff=datediff(d,@normstartdate,@padded_enddate)+1

It's amazing how many things you can get wrong doing date arithmetic. I think I may have posted this function a while back (see below) which calculates the number of workdays between...

Bad database design or not, the problem remains the same. It would be easy to come up with a good database design of an arbitrary problem where you will run...

The function datepart gives you 1-7 for Sun-Sat. But beware of the SET DATEFIRST setting of your database/session.

When having to deal with specific days of a week (e.g. Saturday, Sunday) I...

I think the INVOICE table contains a row for each invoice line item just like PAYMENT contains a row for each payment (possibly more than one) agains a single invoice...

By the way, when checking for nulls you are better off using the ISNULL function instead of CASE. And in the previous example it's no longer even needed.

Oops, I meant to say LEFT JOIN and not RIGHT JOIN up there. You might also want to join the sub-selects to REG in order to be able to say...

Sometime the best way to see what you are selecting is to remove the SUM and GROUP BY stuff. Thus when I throw some data at it (see the end...

Your tables LOGON_TYPE and QUERY seem to be the same with the difference of one column (TYPE). Why this redundancy?

There comes a time when I simply will not try to solve a problem using pure SQL. It becomes too convoluted and hard to understand and modify. Thus the bells...

In the interest of saving some time, I tried out this idea in VB. Should be easily portable to T-SQL. The idea is that you want to always use as...

Has anyone ever done it the other way around? I.e. from within Oracle (say sqlplusw or pl/sql developer) link to a SQL Server table and copy it into an Oracle...

I recently changed the computername of a machine running SQL Server 2000. The end efffect of this (amongst probably others that I don't know of) was that all SQL Server Agent jobs...