Query nanoseconds from first entry

I have a data set keyed with a TestID and a datetime2 value where there are about 8000 entries per TestID, each is about 100 nanoseconds apart.

TestID,DTS,DataX,DataY
40,2021-04-13 20:29:09.0060001,0.51649,0.51604 
40,2021-04-13 20:29:09.0060002,0.51582,0.51622
40,2021-04-13 20:29:09.0060003,0.51642,0.51642
40,2021-04-13 20:29:09.0060004,0.5163,0.51657

I’d like to query all rows for a given data set (#40 in this partial example) and return the nanoseconds from the first record. Like this:

TestID,NS,DTS,DataX,DataY
40,0,2021-04-13 20:29:09.0060001,0.51649,0.51604
40,100,2021-04-13 20:29:09.0060002,0.51582,0.51622
40,200,2021-04-13 20:29:09.0060003,0.51642,0.51642
40,300,2021-04-13 20:29:09.0060004,0.5163,0.51657

I have a query to do it but wonder if there isn’t a better way using a join?

select d.TestID, datediff_big(ns,(select min(d.dts)  from tbl_TestData d 
where d.TestID=40),d.dts) as ns, d.dts, d.DataX, d.DataY from tbl_TestData d 
where d.TestID=40

Note – server is SQL Server 2016.

Answer

You can use window functions:

select td.*,
       datediff_big(ns, min(dts) over (partition by testid), dts)
from tbl_TestData td;