Couldn't test it out but maybe something along these lines?

SELECTd.acctid

, d.acctname

, d.Debit

, c.credit

, d.Debit - c.credit AS InvoiceBalance

FROM(

SELECT a.acctid, a.acctname, SUM(COALESCE(c.invamt, 0)) AS Debit

FROM invoices c

INNER JOIN accounts a ON...

Pulled from script originally written to run against 2000 but works in 2005

SELECT sum(convert(int,round((aa.size*1.000)/128.000,0)))

FROM dbo.sysfiles aa

LEFT JOIN dbo.sysfilegroups bb ON aa.groupid = bb.groupid

No worries, glad I was able to help.

Pro for same db name throughout: code promotion. It enables the same version of the script to be ran in all environments. Call it good old fashioned CYA but I...

just use a subquery to determine the records you want to keep:

SELECTt.*

FROM@temp t

INNER JOIN (

SELECTclientnumber, code, plan, MAX(eff_date) as eff_date

FROM@temp

GROUP BY clientnumber, code, plan

) x ON t.clientnumber = x.clientnumber

AND t.code...

There are exceptions, but we usually handle all formatting on the front-end.

Haven't seen anything this simple in practice but you get the idea.

SET NOCOUNT ON

DECLARE @temp TABLE (SomeValue NUMERIC(5,2))

INSERT...

got beat to the punch but here's what I came up with..

declare @reoders table (rxno int)

declare @hrxs table (rxno int, posteddate datetime)

insert into @reoders (rxno) values (1)

insert into @reoders (rxno)...

Don't know if it's the best option or not but eliminating the use of subqueries from the column list should make it perform better.

SELECT i.woi_product, i.woi_ordref, s.wos_startdate

FROM @woitem i

JOIN @xTOOLS...

You could also use return values to validate success/failure

declare @retval int

--Inserting data table 1

exec @retval = sp_InsertData1

-- SUCCESS: @retval = 0

-- FAILURE: @retval = 1

If @retval = 1 then goto...

Depending on how they created the tables and what roles they had at the time, you might be able to go off table owner.

select name from sysobjects where xtype =...

Why not download and run the advisor?

If you've already changed to 90: back it up, change compatibility back to 80 and run it.

DECLARE @temp TABLE (

RowId INT IDENTITY

, SalesId INT

)

INSERT INTO @temp (

SalesId

)

SELECT Salesid

FROM temp

ORDER BY dealid DESC

SELECT TOP 5 t.SalesId

FROM @temp t

INNER JOIN (

SELECT SalesId, MIN(RowId) AS RowId

FROM @temp

GROUP BY SalesId

)...

We're a small shop (one SA / one DBA) so responsibilities tend to overlap. Historically the SA was responsible for building, patching (OS and DB) and configuring the servers while...

Seen (and wrote) a lot of legacy code where the JOIN criteria is in the WHERE clause. Unless you're seeing '*=' or '=*', I wouldn't worry too much about...

Probably not the most eloquent solution but maybe this'll help.

CREATE TABLE #Reviews

(

Person_ID INT NOT NULL ,

Review_Date DATETIME NULL ,

Initial_Review VARCHAR(MAX) NULL ,

PKID INT NULL ,

)

insert into #Reviews (Person_ID, Review_Date, Initial_Review,...