After several tests, It became clear that Chris query is 1.5 times faster, at least on my development server. Although, both run in about one second for a million rows....

wolfkillj (8/7/2013)

---

Only...

You could change it like this

;WITH base AS (

SELECT Initial_Date = DATEADD( mm, -3, MAX(post_dm)) ,

Final_Date = MAX(post_dm)

FROM ztb_forecastable_metrics_hist

)

SELECT

post_dm -- etc

FROM ztb_forecastable_metrics_hist ztb

JOIN base...

I see letter k in both rows.

Kipling Road

Alexandra Park

The forums part is mainly useless for the purpose that is presented (Database Pros Who Need Your Help!) because most topics are already solved.

There could be some kind of outstanding...

Lowell (8/6/2013)

---

no, you should say "Because @@rowcount is affected by any commands in the session, it's my practice to re-select from the table in case there was a trigger that...

First, I'm giving you the solution as you pictured it, but also another one that might be more effective. It's up to you to choose.

set @sql = 'select (CAST(MONTH(ContractDate) as...

Why are you using a CTE when a normal query could work?

If you give us more information on your query, we might suggest something different, but to solve your problem,...

If you only have 2 rows, then it can be easy to do it with MAX and MIN for your columns.

SELECT id,

MIN( Field1) Field1,

...

There's no need for a cursor or a while loop. Check the following article to solve your problem.

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

It's mostly a local issue, you're erasing the cookies or the history on your explorer.

You should avoid to use strings or integers to store dates. This might help you with your problem.

SELECT CAST( STUFF(STUFF(STUFF(strDate, 13, 0, ':'), 11, 0, ':'), 9, 0, '...

Something that's not LEFT( myString, CHARINDEX(' ', myString)) ?

Here you go, it generates the expected output based on your sample data.

;With MySampleData([Member],[primarycondition],[primaryintensity],[secondarycondition],[secondaryintensity])

AS

(

SELECT 'M2345','hf','1','','' UNION ALL

SELECT 'M2345','COPD','1','','' UNION ALL

SELECT 'M2345','CAD','1','','' UNION ALL

SELECT 'M2345','dia','1','','' UNION ALL

SELECT 'M2345','Ast','1','','' UNION ALL

SELECT 'M2345','hf','2','',''...

The problem is that your WHERE clause is converting the OUTER JOIN into an INNER JOIN. Change those conditions to the ON clause and it should work as expected.