Are you truly still using SQL 2008?  I can't remember if ROW_NUMBER() was introduced yet.

If it was available in 2008, then this should help

DECLARE @CustomerOrder table (
...

So, we have 2 problems to solve

• Overlapping ranges
• Islands

Bert's solution can be tweaked to 1st consolidate the overlapping sets, then calculate the islands, and finally do our range...

This will solve the data as you have provided it.

However, what happens when you have multiple overlapping units?

DECLARE @UnitMembership table (
    IdentityID int NOT...

There is a way to get sequential numbers for each child (lineitem) record, starting at some specific value.

Note, that on a very busy system, this could cause a bottleneck.

Create a...

All those UNION ALLs could also be an issue.  You may want to consider a Divide-n-Conquer path.

Create a #TempTable, then break the massive union all up, inserting each set of...

Secondly, this line in your WHERE clause will ALWAYS cause a full table/index scan.

and (a.BenefitsID=@BenefitsID OR @benefitId = 'ALL')

The reason for this is that SQL has to...

Firstly, your query is hitting the same table 8 times.  This can be vastly improved by using a cross-tab query to only hit the table once.  The following 2 queries...

I believe that APPLY was introduced in 2005

https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-apply-basics/

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

This is a duplicate post.

Original HERE

@DesNorton - I don't think sp_ExecuteSQL would actually diminish the danger. If the @WeatherFieldName variable had a semicolon in it, followed by a query - I believe it would...

Having a large list of fields, you are definitely going to have some ugly long code.

If SQL Injection is your only objection to Dynamic SQL, then you can mitigate the...

You can use OUTER APPLY

SELECT      [Car type]        = C.CarType
      ...

A small perf improvement on @shane.green code

INSERT INTO #FieldValues ( Field1, Field2, Field3 )
SELECT  Field1 = MAX( CASE
        ...

You have nothing that binds the 3 records together and nothing that orders the records.  So there is no way to consistently achieve what you are trying to do.

I'm too lazy to create tables to test.  But I believe that there are 2 ways that you could achieve this.

SELECT    Table1.EMPID
FROM  ...