This gives the same results but doesn't require creating a second temp table.

WITH status_ranges AS
(
SELECT
s.Starttime
,s.Endtime
,s.Status
,CASE WHEN s.Status = LAG(s.Status) OVER(ORDER BY s.Starttime) THEN 0 ELSE 1 END...

• This reply was modified 6 years, 6 months ago by drew.allen. Reason: Forgot to include the ROWS UNBOUNDED PRECEDING
• This reply was modified 6 years, 6 months ago by drew.allen.

Both Jeff and Sumathi's solutions require two scans of the table.  The following only requires one scan.

SELECT 
tfu.rptMth
,SUBSTRING(MAX(tfu.rptMth + NULLIF(tfu.Buiding, '')) OVER(ORDER BY tfu.rptMth ROWS UNBOUNDED PRECEDING),...

The following is from ITPro Logical Query Processing: What It Is And What It Means to You

Figure 3: Logical query processing order of query clauses

1 FROM...

That's not what the SQL documentation says and that doesn't make sense either. ...

I never said it "can't" be used.  I said it SHOULDN'T be used.

I...

As with all things SQL, it depends.  I did a quick test using an index on VisitID and ActivityDateTime and Jonathan's original query won hands down based on the query...

Depending on your indexes, this may perform better.

WITH Last24Hrs AS (
SELECT
VisitID
,IdentifierID
,ActivityDateTime
,DATEADD(HOUR,-24,LAST_VALUE(ActivityDateTime) OVER (PARTITION BY VisitID ORDER BY ActivityDateTime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) AS _24HrsBefore
FROM...

I recommend NOT using the ISNULL() function, which is why...

You want help troubleshooting an error message, but you didn't think that the text of the error message was relevant?

Why are you using a CASE expression rather than a COALESCE(). ...

Declare @tempAddress Table (
        Id int
      , Name1 varchar(10)
     ...

Also, as noted before, -1 has caused errors because of out-of-order date calcs.  To me, it's just not worth the potential issues.  Not to say you can't use it,...

You've been around long enough to know to use the Insert/edit code sample when entering code to preserve the formatting.

You're missing part of your code.  You're using an alias that...

I think it's a circular argument.  You don't use it, because it's not familiar, but it's not familiar, because you don't use it.  I've been using the -1...

I think it's a circular argument.  You don't use it, because it's not familiar, but it's not familiar, because you don't use it.  I've been using the -1 variant for...

The standard pattern for getting the first day of the month is:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

Stick to that pattern, don't corrupt it with -1 tricks!  That just makes it...