Why are scripts never written for case sensitive databases? Commence find and replace on the download.....

this is probably not done because a) I would venture that a very small fraction of installed databases are case sensitive and b) even if you have a server that is, can't you build a database with a case insensitive collation for testing?

Tony and I debated on whether we should use the term "quirky" or not but went with it in the end. Once you get the data aggregated down to just a handfull of rows, it doesn't make as much difference how you do the last step. The real beauty of the winning entry is the "one pass" pre-aggregation.

Excellent solution explained extremely well.
I will be recommending this article to my fellow workers.

I am happy you mentioned the discussion here http://www.sqlservercentral.com/Forums/Topic802558-203-3.aspx, TheSQLGuru. As written by Hugo, the ordered update can only work if a) or b) is satisfied. If you look at the execution plan for the second query of my solution, you can see that a) is satisfied.
Also, according to http://www.sqlservercentral.com/Forums/Topic802558-203-11.aspx, my solution has no WHERE clause.

Still a bad idea:

1) You cannot guarantee that you (nor some other machine with different settings, configuration, data, etc) always get the type of plan you currently get

2) There are other things than what Hugo posted that can break it

3) Microsoft states the order isn't guaranteed

4) If you address and/or caveat all the known points that currently can break it that still leaves someone finding yet another scenario where it breaks and people not getting the new rule.

Besides, people will take this type of logic and use it where it is GUARANTEED to give them the WRONG ANSWERS but they won't know it.

Signing off from this one since we appear to have to agree to disagree. Sorry, but I must state without reservation that it is inappropriate and I believe irresponsible to promulgate this type of processing at this time.

select * from Registrations

declare @starTime datetime
declare @minDate datetime
declare @runTotal int
select @runTotal = 0
select @starTime = GETDATE()

select @minDate = MIN(DateJoined)from Registrations

SELECT
DATEDIFF(MM,@minDate,Datejoined) as s1,
COUNT(*) as counts,
@runTotal as runningTotal
INTO #1
FROM
Registrations
GROUP BY DATEDIFF(MM,@minDate,Datejoined)
order by DATEDIFF(MM,@minDate,Datejoined) asc

select
DATEDIFF(MM,@minDate,DateLeft) as s1,
COUNT(*) as counts,
@runTotal as runningTotal
INTO #2
FROM Registrations
where DateLeft is not null
GROUP BY DATEDIFF(MM,@minDate,DateLeft)
order by DATEDIFF(MM,@minDate,DateLeft) asc



update #1
set @runTotal= runningTotal = @runTotal + c.counts - ISNULL(#2.counts,0)
from #1 as c
left outer join #2 on c.s1 = #2.s1



SELECT
DATEADD(MM,#1.s1,@minDate) as Monthly ,
#1.counts as NewJoins ,
#2.counts as LeftUs,
#1.runningTotal
FROM
#1
LEFT JOIN #2 on #1.s1 = #2.s1

order by runningTotal Asc

select DATEDIFF(mcs,@starTime,getdate())

Thanks for the article, definitely worth reading.

I would appreciate if somebody explain what happens if we use a CTE instead of the #Stage table in the solution "Peso 4e"? Will it be possible at all and how performance will be affected?

Just curicity from my side, and general perseption that keeping everything in memory (avoiding writing to the tempdb) is beneficial.

Appreciate your thoughts.

It's not just that the CTE has too few rows to use a parallel operation... in my experience (at least on SQL 2005) table variables (and by extension CTEs?) will never use parallel operations. Spent a bit of time converting all table variables to temporary tables for just that reason!

If my assertion is still correct (and I suspect it is, and will remain so), then all three criteria are met by using a CTE/table variable:
- it is ordered
- it will not parellelise itself (no matter how big)
- it will not partition.

So it won't break in this use.... just because someone could misuse a technique doesn't mean you shouldn't use it. Hey I've seen insert/update triggers written as if they were for only one row... so should we stop using triggers altogether or is that a different discussion?

Thanks for the article, definitely worth reading.

I would appreciate if somebody explain what happens if we use a CTE instead of the #Stage table in the solution "Peso 4e"? Will it be possible at all and how performance will be affected?

Just curicity from my side, and general perseption that keeping everything in memory (avoiding writing to the tempdb) is beneficial.

Appreciate your thoughts.

It's not just that the CTE has too few rows to use a parallel operation... in my experience (at least on SQL 2005) table variables (and by extension CTEs?) will never use parallel operations. Spent a bit of time converting all table variables to temporary tables for just that reason!

If my assertion is still correct (and I suspect it is, and will remain so), then all three criteria are met by using a CTE/table variable:
- it is ordered
- it will not parellelise itself (no matter how big)
- it will not partition.

So it won't break in this use.... just because someone could misuse a technique doesn't mean you shouldn't use it. Hey I've seen insert/update triggers written as if they were for only one row... so should we stop using triggers altogether or is that a different discussion?

>>>There are other things than what Hugo posted that can break it

Hugo posted things that wouldn't allow it to work to begin with. The rest of it, I posted to show that my poor explanation of why it worked and when it won't needed to be changed.

So far as "could break" when a future CU or SP comes out, so what? That could happen with anything and if you don't regression test before such installations, you're begging for trouble. An example of a change that was not on the deprecation list that broke a lot of code was when they changed the very heavily documented sp_MakeWebTask to require "SA" privs in SQL Server 2000 SP4. Although Microsoft is pretty good about following "the plan" in the form of deprecation lists, Microsoft can, has, and will continue to change anything they need to without warning... documented or not.

Thanks for the article, definitely worth reading.

I would appreciate if somebody explain what happens if we use a CTE instead of the #Stage table in the solution "Peso 4e"? Will it be possible at all and how performance will be affected?

Just curiosity from my side, and general perception that keeping everything in memory (avoiding writing to the tempdb) is beneficial.

Appreciate your thoughts.

"I would appreciate if somebody explain what happens if we use a CTE instead of the #Stage table in the solution "Peso 4e"? Will it be possible at all and how performance will be affected?"

You can try it, but I don't think you can nest a CTE inside another CTE which is what you would have to do if I understand your proposal.

-----------------------------------------------
>>> "Just curicity from my side, and general perseption that keeping everything in memory (avoiding writing to the tempdb) is beneficial."
-----------------------------------------------
Using a Temp table doesn't mean it's not in memory. The first place a Temp table will try to live is the same place a Table variable or the result set of a CTE may try to live... memory. When a Table Variable or Temp Table get to big for memory, they both do the same thing... start using disk space in TempDB.