Or use CROSS APPLY... but the above is probably easier.

SELECT p.Id
  , p.Surname
  , p.Bed
  , p.OpDate
  , patientData.SampleDate
  , patientData.BP
  , patientData.Temp
FROM Patient p
CROSS APPLY (SELECT TOP 1 ID, BP, Temp,...

Lynn-

I'm really fascinated by your solution. I don't think I've come across that concept of outer joining to the FROM table using the VALUES constructor. Is that functionally similar to...

You need to enforce meaningful uniqueness in each row somehow. But that doesn't necessarily have to be done via the clustered index, it could be done via a non-clustered index...

As Steve said, you really need to post some code so we can see your table structure and sample data, etc. But what John Mitchell is suggesting might be:

            
April 17, 2019 at 9:23 pm
            
#3434226

            
            
                                                
        

Props to Drew. Was hoping I had kicked ass on this one, but not quite. Damn.

I'm feeling really heartbroken by the new site. This is one of the most sacred places on the Internet to me, and this feels like a significant loss. The new...

GAWD the new formatting on this site is AWFUL. Can someone please change it back? It's practically unreadable. 🙁

-- Drop target scratch table if it already exists:
IF OBJECT_ID('tempdb..#OPCantBeBothered') IS NOT NULL
DROP TABLE #OPCantBeBothered
;

-- Create scratch table:
CREATE TABLE #OPCantBeBothered
(
AccountNo INT, 
VersionNo INT, 
Amount INT, 
StatusDate DATE
)
;

INSERT...

Are you saying there is literally a "CREATE OR ALTER" feature? Or are you just using that name informally? The code examples you gave in the screenshot below appear to...

I've never actually used the Time datatype before, which is what it looks like you are using for OrderLine.duration. Based on my testing, it seems like the following should work...

Seems like it might be worth considering dumping the filtered data into its own tables as part of a daily process or something. And then querying those tables.

Not sure this is 100% relevant to your issue or not, but you might also want to check out SUM...
https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-2017

Amen! Glad someone found a hack. I knew it was just a matter of time! 🙂 Now we just await the OP's coworkers' defensive and dismissive replies... lol