How to get unique values by line - SQL Server

Question

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

Answer 1

I think you might want to use this code. First you create a CTE, with a group by. After that, you calculate the diff:

USE YourDB;
WITH Cte AS 
(
     SELECT Sct.Systemunittrid AS Id_Sut,
            Sct1.Systemunittrid AS Id_Sut1,
            MIN(Sct.Modifieddatetime) AS Sut,
            MAX(Sct1.Modifieddatetime) AS Sut1
     FROM Systemunittransactions AS Sct
       LEFT JOIN Systemunittransactions AS 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
     GROUP BY Sct.Systemunittrid,
              Sct1.Systemunittrid 
)

SELECT *, DATEDIFF(minute, Sct.Modifieddatetime, Sct1.Modifieddatetime) AS Diff
FROM Cte;

Answer 2

There is something wrong with your example, you are joining by the serviceTag

Systemunittransactions AS Sct
       LEFT JOIN Systemunittransactions AS Sct1 ON Sct1.Servicetag = Sct.Servicetag

but after that you filter by two different tags on each table,

Sct.Servicetag = 'IDXXX12' AND Sct1.Servicetag = 'IDXX12',

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:

select 
    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 and sct.StatusType = sct1.StatusType
where 
    sct.servicetag = 'IDXXX12'  
    and sct.StatusType = 'STATUS_1'
    and sct.SystemUnitTRID != sct1.SystemUnitTRID
order by 
    sct.SystemUnitTRID

Answer 3

If you are using SQL Server 2012 or newer you can simply use LAG or LEAD functions depending what do yo need

select distinct
    SystemUnitTRID as id_sut,
    lead(SystemUnitTRID) over (order by systemunittrid) as id_sut1,
    ModifiedDateTime as sut,
    lead(ModifiedDateTime) over (order by systemunittrid) as sut1,
    DATEDIFF(minute, lead(ModifiedDateTime) over (order by systemunittrid), ModifiedDateTime) as diff
from 
    systemunittransactions sct
where 
    servicetag = 'IDXXX12'
and statustype = 'STATUS_1'
order by 
    SystemUnitTRID

Answer 4

If you can't use lag and lead per the other answers here, you can achieve the same effect by creating a row_number with the ServiceTag as the partition and sort it by the ModifiedDateTime you can join the data to itself via the ServiceTag and the row_number to get your periods between each row. You will note that this works for multiple ServiceTag values:

declare @t table(ServiceTag varchar(20), StatusType varchar(20), SystemUnitTRID int, ModifiedDateTime datetime);
insert into @t values
 ('IDXXX12','STATUS_1',145818840,'2019/01/18 08:35:27')
,('IDXXX12','STATUS_1',145820934,'2019/01/18 08:50:41')
,('IDXXX12','STATUS_1',145822116,'2019/01/18 08:59:18')
,('IDXXX12','STATUS_1',145825658,'2019/01/18 09:23:23')
,('IDXXX12','STATUS_1',145826849,'2019/01/18 09:30:55')
,('IDXXX13','STATUS_1',245818840,'2019/01/17 18:35:00')
,('IDXXX13','STATUS_1',245820934,'2019/01/17 18:50:00')
,('IDXXX13','STATUS_1',245822116,'2019/01/17 18:59:00')
,('IDXXX13','STATUS_1',245825658,'2019/01/17 19:23:00')
,('IDXXX13','STATUS_1',245826849,'2019/01/17 21:30:00')
,('IDXXX14','STATUS_2',345818840,'2019/01/16 18:35:00')
,('IDXXX14','STATUS_2',345820934,'2019/01/16 18:50:00')
,('IDXXX14','STATUS_2',345822116,'2019/01/16 18:59:00')
,('IDXXX14','STATUS_2',345825658,'2019/01/16 19:23:00')
,('IDXXX14','STATUS_2',345826849,'2019/01/16 19:30:00')
;

with d as
(
    select ServiceTag
            ,StatusType
            ,SystemUnitTRID
            ,ModifiedDateTime
            ,row_number() over (partition by ServiceTag order by ModifiedDateTime) as rn
    from @t
    where StatusType = 'STATUS_1'
)
select d.ServiceTag
    ,d.SystemUnitTRID as id_sut
    ,d2.SystemUnitTRID as id_sut1
    ,d.ModifiedDateTime as sut
    ,d2.ModifiedDateTime as sut1
    ,datediff(minute,d.ModifiedDateTime,d2.ModifiedDateTime) as MinuteBoundaries    -- Returns the number of minute boundaries crossed
    ,datediff(second,d.ModifiedDateTime,d2.ModifiedDateTime)/60 as WholeMinutes -- Returns the number of whole minutes
    ,right('00' + cast(datediff(second,d.ModifiedDateTime,d2.ModifiedDateTime)/60/60 as varchar(10)),2)
        + ':'
        + right('00' + cast(datediff(second,d.ModifiedDateTime,d2.ModifiedDateTime)/60%60 as varchar(10)),2) as FormattedTime
from d
    join d as d2
        on d.ServiceTag = d2.ServiceTag
            and d.rn = d2.rn-1
order by d.ServiceTag
        ,d.ModifiedDateTime;

Output

+------------+-----------+-----------+-------------------------+-------------------------+------------------+--------------+---------------+
| ServiceTag |  id_sut   |  id_sut1  |           sut           |          sut1           | MinuteBoundaries | WholeMinutes | FormattedTime |
+------------+-----------+-----------+-------------------------+-------------------------+------------------+--------------+---------------+
| IDXXX12    | 145818840 | 145820934 | 2019-01-18 08:35:27.000 | 2019-01-18 08:50:41.000 |               15 |           15 | 00:15         |
| IDXXX12    | 145820934 | 145822116 | 2019-01-18 08:50:41.000 | 2019-01-18 08:59:18.000 |                9 |            8 | 00:08         |
| IDXXX12    | 145822116 | 145825658 | 2019-01-18 08:59:18.000 | 2019-01-18 09:23:23.000 |               24 |           24 | 00:24         |
| IDXXX12    | 145825658 | 145826849 | 2019-01-18 09:23:23.000 | 2019-01-18 09:30:55.000 |                7 |            7 | 00:07         |
| IDXXX13    | 245818840 | 245820934 | 2019-01-17 18:35:00.000 | 2019-01-17 18:50:00.000 |               15 |           15 | 00:15         |
| IDXXX13    | 245820934 | 245822116 | 2019-01-17 18:50:00.000 | 2019-01-17 18:59:00.000 |                9 |            9 | 00:09         |
| IDXXX13    | 245822116 | 245825658 | 2019-01-17 18:59:00.000 | 2019-01-17 19:23:00.000 |               24 |           24 | 00:24         |
| IDXXX13    | 245825658 | 245826849 | 2019-01-17 19:23:00.000 | 2019-01-17 21:30:00.000 |              127 |          127 | 02:07         |
+------------+-----------+-----------+-------------------------+-------------------------+------------------+--------------+---------------+

Answer 5

Try this :-select * from(select 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, ROW_NUMBER() over(partition by sct.SystemUnitTRID order by sct.SystemUnitTRID) as rownum 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)t where rownum=1