Actually, it is more...

That's actually like three queries in one procedure there. Any idea which of the three is taking all of the time or what the breakdown is between them? ...

No, your "AS [TOTAL_BAGS]" does not go there, it would go out were i have "as Bags" as the end of that line. Which would just mean that you...

As far as I know.

The OVER keyword has to be paired with a windowing function (such as ROW_NUMBER()) or an aggregate function (such as SUM()). so your CTE should be slightly changed:

WITH OneYearByVendor...

Oh, by the way, I think that the specialty LastCred's will need to be cast-ed to varchar() before they are Coalesced, or else you might get datatype errors.

Heh. Funny, that's how my wife describes me to her mother. 😛

Glad I could help!

Then you need a junction or mapping table to represent just that relationship.

Here's something to start with:

WITH Spec as (Select *

, Row_Number() Over (Partition By DoctorID Order By LastCred) as Num

From Specialty

)

Select

D.DoctorId

, O.Addr, O.City, O.State, O.Zip

, Coalesce(S1.Specialty,...

Jeff, are you putting your articles in thread posts again? 🙂

I am sure that there is a better way to do this, but you have given us so little information that we really cannot tell you anything specific. Could...

For me it is Failed Backups, definitely. they are so much more common than most IT professionals suspect.

Developers: well, I am one as well as a DBA, so no worries...

That depends entirely on what you intend to use this relationship information for.

If you just want the database to enforce it, you could implement it as a one-to-many Foreign Key...

Well, if you are on SQL Server 2005, you should know two things:

1) this is a SQL Server 7,2000 forum. The SQL 2005 forums are higher up on the...