See if this helps

create table mytable(yr int, mn char(3), Spend int)

insert into mytable(yr,mn,Spend)

select 2008, 'Jan' , 100 union all

select 2008, 'Feb' , 200 union all

select 2008, 'Mar' , 100 union...

Jeff Moden (7/9/2008)

---

Aw rats... the fly in the ointment is that the ID's must already be in the correct hierarchical order in order to display in the correct order... ...

SELECT calls.cLog.value('OperatorsName[1]', 'varchar(512)') as OperatorsName

, calls.cLog.value('Date[1]', 'varchar(512)') as...

SELECT col1 AS "@N",

col2 AS "text()"

FROM @temp

FOR XML PATH('D'),ROOT('Root'),TYPE

Excellent!

Well deserved.

As an example, see if this helps

/* ItemsSold */

(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key = @dateKey) as TotalItemsSoldDay,

(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey) as...

You can use indexed views for this

http://www.sqlservercentral.com/Forums/Topic471378-338-1.aspx#bm471408

Another alternative

SELECT

A.RetailerID,

X.RetailerIDTheirs

FROM TableA AS A

CROSS APPLY (SELECT TOP 1 B.RetailerIDTheirs FROM TableB B WHERE...

Wrox : Professional C# 2008

Coming from a C++ background, I found the 2005 version of this really helpful (apart from the numerous typos)

This should perform better than your correlated subquery

SELECT p.Publisher, COUNT(b.Publisher_Ref) AS BookCount

FROM Publishers p

LEFT OUTER JOIN Books b ON b.Publisher_Ref = p.Ref

GROUP BY p.Publisher

ORDER BY p.Publisher

select cust,

count(case when prod='P' then prod end) as 'No. of Product (P)',

count(case when prod='Q' then prod...

> XPath is not exactly friendly.

Yep!

,RelOp.op.value('(./*:RunTimeInformation/*:RunTimeCountersPerThread)[1]/@ActualRows','int')

Either remove the "declare default element namespace" clause from the query or add the namespace to the XML

[RelOp xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" AvgRowSize="9" ...

The problem is that your XML could have multiple RunTimeInformation elements such as this

[RelOp AvgRowSize="9" EstimateCPU="0.0014337" EstimateIO="0.0138859"]

...

You should be able to get the overlap using this (DDL and sample data would help)

select startYear,

endYear,

...