An update is logically equivalent to deleting the old records and inserting the new records.  The deleted virtual table contains the old records and the inserted virtual table contains the...

WITH DaysWorked AS
(
    SELECT hss03_CaseNumber, hss03_AssignedToName, DATEDIFF(DAY, hss03_AssignedDate, LEAD(hss03_AssignedDate, 1, hss03_AssignedDate) OVER(PARTITION BY hss03_CaseNumber ORDER BY hss03_AssignedDate)) AS DaysWorked
    FROM #Ticket
)
SELECT hss03_CaseNumber,...

We're doing a major reclassification of our assets and we've asked our QA people to verify the reclassification before we deploy to prod.  They're coming back to me with "errors"...

And who was working on the ticket from 2018-05-30 to 2018-08-07?  I would assume that it was JB, because it hasn't been reassigned to anyone else.  Why aren't those days...

Most people are unwilling to open a file posted to the Internet by a random stranger.  Please post sample data as outlined in the first link in my signature.

If it's the result of an explicit conversion, you can replace it with a TRY_CONVERT/TRY_CAST as appropriate and then search for NULL values in the results.  That won't help you...

This will at least get you started.

SELECT *,
    CASE
        WHEN MAX(S_DATE) OVER(PARTITION BY [ID]) < GETDATE() THEN 'Only Current Year'
        WHEN MIN(S_DATE) OVER(PARTITION...

This is shorter, but many people don't like it for some reason.

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '06:00'),
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), '01:00')