Oh man! My apologies for my misunderstanding. And you're right, this require some kind of recursion, wich could be implemented with a subquery.

SELECT t1.PhoneID, COUNT(t1.SubscriptionID) AS NewStarts

FROM Table1 t1

WHERE DATEDIFF(dd,...

The next script should do the trick:

SELECT COUNT(DISTINCT SubscriptionID) AS NewStarts

FROM Table1

WHERE DATEDIFF(dd, StartDate, EndDate) > 30

If you don't have repeated values for the [SubscriptionID] field, you can...

In the following link you can find and download the source of sys_CrossTab sp:

http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

You can leave it's name so, or use another that could be more useful for you.

After created...

I'm not able to get the same result that you get, but perhaps creating an index after the table [Results] be created, and before inserting records on it, may help...

I have realized that you're doing a conversion from datetime to varchar as part of your process, altough in the [Results] table [EffectiveDate] is already a datetime field.

If [EffectiveDate] is...

In the following link I found an implementation of sp_Transform that may be is different of your implementation, but it gives the result that you're expecting:

http://www.itrain.de/knowhow/sql/tsql/pivot/sp_transform_v1_1.asp

I tested it with this...

Sami,

I'm a little bit confused about the field names and the content of your sample code, but I think that this would give you a near result to your target:

select...

Instead of use a datetime field, you can use a int field to store the month and year in format [yyyymm] before exec sp_transform:

select DATEPART(yyyy, dateField) * 100 + DATEPART(mm,...

You can create a function like this that returns the values in a table:

CREATE FUNCTION dbo.fString2Table (

@String as nvarchar(MAX),

@Delimiter as nvarchar(1)

)

RETURNS @ReturnTable...

An assort of raisin and nuts. It's good for the brain, and good for the jogging training 😎

When I'm working on SQL, usually I say that I'm a DBA, some people ask me what it means? Other ask if I not longer am a web developer.

To both...

Maybe the DISTINCT clause could be useful in this case.

SELECT DISTINCT c.CASE_ID as ChildID,

c.LAST_NAME as ChildLastName,

c.FIRST_NAME as ChildFirstName,

c.MI_NAME as MI,

c.CASE_RSF as ChildRSF,

cc.Sex_Code as Sex,

cc.Brth_Date as BirthDate,

cp.PRV_ID...

It's good to know that my answer was at last a little bit useful for you. But don't thing that you have a little knowledge. Even the experts may learn...

I'm not sure how so good or bad is to perform a double evaluation, but I'm afraid that you can avoid it.

About your SQL code, if I'm rigth you don't...