Beware of rounding!

If you run the suggested code PM, you will not get today's beginning.

Declare @Today smalldatetime

-- beginning of today:

Select @Today = Cast(Cast(getdate() as int) as smalldatetime)

Try...

How do the query plan look like now?

Why the five almost identical LEFT JOINs?

SELECTLName,

FName,

Address1,

Address2,

City,

[State],

Zip,

Phone,

SSN,

0 AS [Basic],

x.EL AS Optional,

x.BAD AS EmployeeVADD,

x.VAD AS FamilyVADD,

x.SPL AS Spouse,

x.CHL AS Child,

DOB

FROMPersonalData

INNER JOINCensus ON Census.TrackingID = PersonalData.TrackingID

INNER JOINClientPlan ON ClientPlan.PlanID = Census.PlanID

INNER JOIN(

SELECTTrackingID,

MAX(CASE...

http://www.sqlteam.com/article/datediff-function-demystified

Jim, you believe that is easier than a recursive CTE?

Hari, where is the hierarchy?

DECLARE@Sample TABLE (NodeID INT, NodeName VARCHAR(20), ParentNodeID INT)

INSERT@Sample

SELECT 1,'HR',0 UNION ALL

SELECT 2,'Finance',0 UNION ALL

SELECT 3,'Production',0 UNION ALL

SELECT...

Tried this?

SELECT DENSE_RANK() OVER (PARTITION BY a.bus_manager_name, c.NumericFiscalWeek ORDER BY ISNULL(MAX(h.totalscore), 18) DESC) END AS QualityRank

Probably a recursive CTE.

Read Books Online how to write these kind of queries.

UPDATE Table1

SET Col1 = cast(convert(varchar(30), Col1, 120) AS datetime)

DECLARE@Sample TABLE

(

Site CHAR(1),

[Date] VARCHAR(5),

[Group] INT,

Page INT,

Position INT

)

INSERT@Sample

SELECT'a', '1/1', 1, 1, 5 UNION ALL

SELECT'a', '1/1', 2, 2, 6 UNION ALL

SELECT'b', '1/1', 1, 3, 7 UNION ALL

SELECT'b', '1/1', 2, 4, 8

SELECTSite,

[Date],

MAX(CASE WHEN...

You are right about table variables can't be used together with INSERT ... EXEC since this is a SQL 2000 forum.

But in SQL 2005 you can use a table variable...

HP 8710p laptop.

2.4 GHz Intel Centrino Pro

2gb ram.

Single sata 3.5" harddrive.

Microsoft SQL Server 2005 Developer Edition - 9.00.3215.00

If I tell you what I use at home, I have to kill...

Your original solution will also gain from preaggregration like this

-- Jeff 2

SELECTCompany,

[Year],

SUM(CASE WHEN [Quarter] = 1 THEN Amount ELSE 0.0 END) AS [Q1 Amt],

SUM(CASE WHEN [Quarter] = 1 THEN Quantity...

SELECTSourceID,

ProdID,

Bidder,

BidValue,

BidDate

FROM(

SELECTSourceID,

ProdID,

Bidder,

BidValue,

BidDate,

ROW_NUMBER() OVER (PARTITION BY SourceID ORDER BY BidValue DESC, BidDate) AS RecID

FROMTable

) AS d

WHERERecID = 1

The trick was not to convert CTE to derived table.

The performance gain was to subaggregate the table before making the pivot.

Also asked and answered here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105355