Keep it simple and avoid any reference to SET DATEFIRST!

DECLARE@Year SMALLINT = 2011

;WITH cteCalendar(FirstOfMonth, LastOfMonth)

AS (

SELECTDATEADD(MONTH, 12 * @Year + number - 22801, 6) AS FirstOfMonth,

DATEADD(MONTH, 12 * @Year +...

DECLARE@Sample TABLE

(

RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

Data VARCHAR(5) NOT NULL

)

INSERT@Sample

(

Data

)

SELECT'' UNION ALL

SELECT'1' UNION ALL

SELECT'11'

;WITH cte(Digit)

AS (

SELECT ' ' UNION ALL

SELECT '1'

)

INSERT@Sample

(

Data

)

SELECTp1.Digit + p2.Digit + '.' + p4.Digit + p5.Digit

FROMcte...

Also asked and answered here multiple times.

SELECTBranchName,

SUM(CASE WHEN [Status] = 'A' THEN Amount ELSE 0 END) AS Active,

SUM(CASE WHEN [Status] = 'D' THEN Amount ELSE 0 END) AS Cancel

FROMdbo.Rose

GROUP BYBranchName

What does this query return?

SELECT MAX(LEN(PARSENAME('0.' + Col1, 1))) AS Part1,

MAX(LEN(PARSENAME('0.' + Col1, 2))) AS Part2

FROM Table1

WHERE Type = 'ABC'

Paul White NZ (9/3/2010)

---

You approach the problem in a slightly different way - copying the source table to a heap first and relying on the ranking function to provide the...

Paul... If you change index IXC_Transaction_AccountID_Date_TransactionDetailID for DATE column to be descending, your CTE update fails.

This behaviour is covered both here (and a correct implementation of "Ordered CTE Update") http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/%5B/url%5D

and...

Can you wait a few minutes? I just ran out of popcorn and soda...

Also asked and answered her http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=149677

and here http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/32604047-54d5-4f15-9e4c-4ebc11c40f08

This line seems to be obselete, unless it's used for filtering? It is not used in the resultset.

INNER JOIN ltf.sforce.Contact AS ReferrerContact ON ReferrerContact.Id = Prospect__c.Referring_Member__c

In...

dingels35 (9/2/2010)

---

It depends...

Three suggestions.

1) Alter existing index

CREATE NONCLUSTERED INDEX [IX_Contact_1] ON [sforce].[Contact]

(

[Join_Date__c] ASC,

[Member_Type__c] ASC,

[Member_Status__c] ASC,

[AccountId] ASC

) INCLUDE ( [LastName], [FirstName], [Email], [MMS_Member_ID__c] )

2) Create new index

CREATE NONCLUSTERED INDEX [IX_Account_2] ON [sforce].[Account]...

Also, don't use functions in WHERE clauses. The function is evaluated for every row!

Replace "dbo.floorDate(DATEADD(d,-30,GETDATE()))"

with

DATEADD(DAY, DATEDIFF(DAY, 30, GETDATE()), 0)

This expression is only evaluated once in the query.

I am much...

It is a set-operation and it looks like MERGE internally divides the records into three streams based on current data.

One stream for DELETE, one stream for UPDATE and one stream...

urmarke (9/1/2010)

---

To clarify, the query is a single group by with 25 plus columns, including case statements using the in command. eg Case When column In ('[a]','') Then X ....