-1

I am using SQL Server v18.0

I have the folowing table:

TestEquipmentStatistics The table represents the time use for the test equipment.

The character C_ represents connect operation and the D_ represents disconnect operation

How can I create query that return the time use for range of dates of specific device name?

1
  • Can you provide the table structure DDL and a sample set of output data please? – George.Palacios Dec 10 '18 at 8:33
0
create table tbl (DeviceName varchar(10), Connect_Statistics varchar(30));

insert into tbl values
('reg', 'C_09-12-2018 11:36:40'),
('reg', 'D_09-12-2018 11:38:52'),
('reg', 'C_09-12-2018 14:46:09'),
('reg', 'D_09-12-2018 14:46:18'),
('reg', 'C_09-12-2018 12:03:18'),
('reg', 'D_09-12-2018 12:03:25');
GO

Assuming than for each C_ type there is a corresponding D_, and that there are no intersection between dates of different records, you can use next solution:

with ct as
(
  select 
      DeviceName, 
      substring(Connect_Statistics, 1, 1) as [Type],
      cast(substring(Connect_Statistics, 3, 19) as datetime) as [Date]
  from   
      tbl
)
select
    t1.DeviceName,
    t1.[Date] as StartDate,
    t3.EndDate,
    DATEDIFF(second, t1.[Date], t3.EndDate) DiffSeconds
from
    ct t1
cross apply (select top 1
               [Date] as EndDate
             from
               ct t2
             where 
               t2.DeviceName = t1.DeviceName
               and t2.[Type] = 'D'
               and t2.[Date] > t1.[Date]
             order by
               t2.DeviceName,
               t2.[Date] asc) t3
where
    t1.[Type] = 'C'
order by
    t1.DeviceName,
    t1.[Date];

GO
DeviceName | StartDate           | EndDate             | DiffSeconds
:--------- | :------------------ | :------------------ | ----------:
reg        | 12/09/2018 11:36:40 | 12/09/2018 11:38:52 |         132
reg        | 12/09/2018 12:03:18 | 12/09/2018 12:03:25 |           7
reg        | 12/09/2018 14:46:09 | 12/09/2018 14:46:18 |           9

db<>fiddle here

Not the answer you're looking for? Browse other questions tagged or ask your own question.