I want to calculate the minute difference between ids (sut to sut1), but I don't know how do this in SQL Server.
Now my query is resulting this table:
But I want this result:
My query:
select distinct
sct.SystemUnitTRID as id_sut,
sct1.SystemUnitTRID as id_sut1,
sct.ModifiedDateTime as sut,
sct1.ModifiedDateTime as sut1,
DATEDIFF(minute, sct.ModifiedDateTime, sct1.ModifiedDateTime) as diff
from
systemunittransactions sct
left join SystemUnitTransactions sct1 on sct1.ServiceTag =
sct.ServiceTag
where
sct.servicetag = 'IDXXX12'
and sct1.ServiceTag = 'IDXX12'
and sct.StatusType = 'STATUS_1'
and sct1.StatusType = 'STATUS_1'
and sct.SystemUnitTRID != sct1.SystemUnitTRID
order by
sct.SystemUnitTRID
There is something wrong with your example, you are joining by the serviceTag
but after that you filter by two different tags on each table,
one has one more X than the other. That join with that filter would never work. Assuming that it is a missing letter error in the question, and the table example that you are given which doesn't have more than one row with the same pair of id's, the query you're looking for is: