You either have to use a calendar table or generate a calendar table on the fly and cross apply this table the event table as per the interval type.

For example,...

Let's see if this query helps.

SELECT( CASE

WHEN M.TERM_ID LIKE '040%' THEN 'A GROUP'

WHEN M.TERM_ID LIKE '050%' THEN 'B GROUP'

WHEN M.TERM_ID LIKE '126%' THEN 'C GROUP'

ELSE 'UNKNOWN'

END ) AS...

So, you just wanted a sequential numbering for the resultset returned by your query, just add the ROW_NUMBER function in the same query itself.

Though this should work, but I really...

I have 2 questions for you.

1. How does NEW_TERMS value is 2 for B Group? It should be 1.

2. What would be the output if there exists an entry...

I recommend using the solution provided by Willem as it does not require two way REPLACE and also STUFF function performs better than the SUBSTRING function.

I really don't think so I've understood your requirement completely. Can you provide the sample output required for the provided data?

Something like this should work.

SELECTId, [Name], Designation, [Address], IsDelete,

SUBSTRING

(

REPLACE

(

REPLACE

(

( SELECT [VALUE] FROM Table2 T2 WHERE T1.Id = T2.Id FOR XML PATH('') ), '</VALUE>', ''

), '<VALUE>', ','

),...

Thank you for providing the scripts, good work.

Well, the technique is to use ROW_NUMBER() function to generate sequential numbers. And by adding the last Id value to this row...

Instead of using the system table database_files, you can use master_files table which contains data for all the databases.

select df.file_id,

df.name,

df.physical_name,

fg.name as Filegroup,

df.state_desc,

df.size*8/1024 as Size,

case df.max_size when -1 then 'Unlimited'

else cast...

Did you really read through the articles? If yes, can you post what exactly did you tried?

Though I can write the query for you, but I really want...

So, you wanted to convert rows into columns and do a COUNT on the rows. This type of requirement/concept is called CrossTab or PIVOT.

Generally, this can done...

It can done, but unless you help us with some...

Jeff Moden (2/6/2010)

---

...... I should have qualified my statement. Instead of saying...

"I believe you'll find it's a wee bit more difficult to do than just using REPLACE."

......

So here lies the problem. Since MSONE is not a default instance, you have to...

Can you also show us the deployment configurations of the project in VS/BIDS?