Check out the following articles by Itzik Ben-Gan:

Packing Intervals which discusses how to ignore gaps up to a certain length and

New Solutions to the Packing Intervals Problem which...

To avoid the "0" and negative problem, change it from using the sequence number to using the event record ID.  In the example you have above, it looks like...

The best way?  Normalize your data instead of trying to store it denormalized.

Drew

The problem here is that you are using an INNER JOIN when you should be using an OUTER JOIN and in trying to emulate an OUTER JOIN, you are creating...

Shifting gears for a minute... does anyone have experience with BitBucket (source control) and Jira?  Rumor has it that they integrate seamlessly but have zero experience with either so...

It's all in the documentation.

DELETE (Transact-SQL)

-- Syntax for SQL Server and Azure SQL Database  
  
[ WITH <common_table_expression> [ ,...n ] ]  
DELETE...

It requires two steps.

CAST(xmldata.value('(ns8:date)[1]', 'DATETIMEOFFSET') AS DATETIME) RDate

Drew

Since you are on SQL 2017, you can use LEAD() or LAG().

SELECT *, CASE WHEN EffectiveFromDate < LAG(EffectiveToDate) OVER(PARTITION BY RentType ORDER BY EffectiveFromDate, EffectiveToDate) THEN 1...

There was a syntax error in your code, so it wouldn't run.  I had to guess at what you wanted.

1. WHY are you storing XML data as VARCHAR(max)?
2. WHY are...

Thanks for the reply, but I can not do that. I am taking a time like 10:02:00 and looping through the table above and checking this for each row;...

The closest that I've seen is to use the OPENXML command specifying a table name (Example E).

This is something that is usually best left to the presentation layer.  You can do it in SQL, but it is much easier in SQL 2012.  Are you really still...

The sample data is the one that I have posted. And the DDL is not actually implies student ID. I had a requirement from my manager asking for same...

A PIVOT can only work on one source column, but your data is coming from two columns.  This is why the CROSS TAB approach is usually better.  There's a similar...

• You have non-SARGable formulas in your WHERE clause.
• You call the same table multiple times when you probably only need to call it once.
• You haven't posted the execution...