The first issue based on your sample data: 209 usually isn't followed by 2010 but rather 309.

The "classic" gaps'n'islands will fail here since there's no consecutive pattern.

A solution could be...

Can you add the actual execution plan as well?

It displays there's only a single row estimated.

How many rows are returned "in reality"?

Expanding on Jacks solution, but eliminating the need to "hope" for @@DateFirst being either 1 or 0, here's an approach that's totally independent on the setting of @@DateFirst.

the biggest difference...

Semms like this blog[/url] describes exactly what you're looking for.

I've seen you're using the most dangerous connection string possible.

This is absolutely the wrong way to connect to a database.

Create a SQL login limited to the database the application needs...

earlcools (8/8/2014)

---

both databases has the same name

the users are in the configuration file on the network website

<add name="DefaultConnection" connectionString="Data Source=xxxxx;Initial Catalog=aspnet-cxcDB-20130627224632.mdf;User ID=xxxxxx;Password=xxxxx" providerName="System.Data.SqlClient" />

<add name="CXCDBConnectionString1"...

Do both databases have the same name?

How can you connect to the DB restored on the NM (Mixed Mode or Windwos authentication only)?

In case of mixed mode: Did you add...

Looks like a "groups'n'islands" scenario with the additional challenge of overlapping groups.

Solutions for this scenario can be found here.

Side note: I'm sorry for being lazy. But without ready to use...

That doesn't match the sample data you provided.

ID eID inCount outCount

41772 0 NULL NULL

10000 1033 1 7

10000 1034...

What SQL Server version do you use?

If it's SS2k8 (as indicated by the forum you posted in) it looks like an issue for Jeff's running total[/url].

Disclaimer: please make sure you...

Like I said before: Step 1 is to get the query you're looking for without the additional countries that aren't included in the result set.

Please post that query.

Also, please verify that your expected output is based on your sample data. I'd expect the current version doesn't.

Step 1:

Write the query to produce the result set you're looking for (except the countries without values).

Step 2: post that query.

Step 3:

Tell us the source tabel where we can...

I would use the following approach:

WITH cte_with_values AS

(

SELECT xxx

FROM abc INNER JOIN cde ON abc.col1=cde.col1

)

SELECT your_columns

FROM tbl_with_list_of_countries t

LEFT OUTER JOIN cte ON t.country=cte.country

The verbal description:

Within the cte calculate the values...

No I'ver reached the point where my very limited Oracle knowledge ends...

Maybe Oracle requires values for each column. Don't know.