You do realize that the IF and the ELSE section are EXACTLY THE SAME, right?  One definition of insanity is doing the same thing and expecting different results.

Drew

You can use RIGHT() or SUBSTRING(), but both of those tend to be more complex than STUFF().

Drew

Hi,

I am doing STUFF in my query which is concatenating the blank data too and data looks like below. Sometimes there is space+comma and sometimes only comma in the...

You would use a CROSS APPLY or OUTER APPLY like any other table-valued function.

FROM data.dbo.CalendarEvents AS ce
INNER JOIN data.dbo.ObjectMetadata AS OM2
   ON ce.ParentObject = OM2.ObjectID
INNER...

I would need some data and expected results to work with.

Drew

Also, get rid of the DISTINCT.  UNION already does a distinct, so specifying DISTINCT here just clutters up your code with no benefit.

Drew

Hey Drew,

Which part of the query is static, I am curious?

If you referring to the last query I posted, that's the printed version after I did PRINT @query.

I don't...

The problem you are seeing is that you made a dynamic part of your query static.  Specifically, the 'Year_' + CONVERT(NCHAR(4), YEAR(eventyear)) needs to be dynamic so that when you...

tjolliffe's approach requires two scans of each of the tables.  The following only uses one scan of each of the tables, but you may be better off just skipping the...

It's embedded.  You need to ADD quotes, not take them away

''YEAR_'' + CONVERT(nvarchar(8), YEAR(eventyear)) eventyear,

Before you execute the dynamic SQL, print it.

PRINT @query

Run that query, fix the issues, and...

DATE/TIME data should be stored in DATE/TIME fields.  If they are stored in any other format, you should consider changing that.

DATE/TIME data is not stored in a human-readable format.  It...

T-SQL 2012 has a new built in function to give a running total, so you don't need a cursor for running totals that sum distinct counts. Previously sum...

• This reply was modified 6 years, 8 months ago by drew.allen. Reason: edited to break up long line of code

Did you really think it was necessary to post 5000 lines of data to illustrate your issue?

Drew

From your data, I think what you're really dealing with is a overlapping intervals problem.  You have overlapping intervals (student start and end dates) and you need a running count...

This thread is SEVEN YEARS OLD, so it's not safe to make any assumptions about what the OP is currently using.

Drew