Select/Join multiple row values from Column A if dates in Column B are within 'x' seconds of each other

Question

I have the following Database structure and need a query to work in SQL Server 2008 and also SQL Server 2014.

Database: ASSETS

Table: COMPUTERS

 PK (GUID) (nvarchar) (nvarchar)
| PC_ID | PC_Hostname | Win_Ver |
+-------------+---------------+----------+
| ABCDE-12345 | ACMEINC_1 | W732 |
| FGHIJ-67890 | ACMEINC_2 | W732 |
| QWERT-34567 | ACMEINC_3 | W764 |
| JHGFD-87654 | ACMEINC_1 | W764 |
| CVBNM-08643 | ACMEINC_1 | W1064 |

Table: SOFTWARE

 PK (GUID) (GUID) (int) (nvarchar) (datetime)
| REG_ID | PC_ID | SWType | NAME_Reg | DATE_Reg |
+-------------+-------------+--------+-----------+-------------------------+
| 2WS3-8574 | ABCDE-12345 | 8 | Bob | 2018年02月19日 15:30:10.980 |
| D46G-2574 | ABCDE-12345 | 7 | Admin | 2018年02月19日 15:30:12.001 |
| 7F4H-6523 | FGHIJ-67890 | 7 | NULL | 2018年03月01日 11:02:02.954 |
| MNDE-4567 | QWERT-34567 | 8 | Joan | 2018年11月23日 09:28:59.685 |
| 8S22-1254 | QWERT-34567 | 7 | Warehouse | 2018年11月23日 09:29:00.103 |
| C9CC-1854 | JHGFD-87654 | 8 | Barry | 2018年11月23日 09:46:21.321 |
| JH21-6325 | JHGFD-87654 | 7 | Accounts | 2018年11月23日 09:46:21.332 |
| 109A-4785 | CVBNM-08643 | 8 | Sally | 2019年01月02日 12:59:59.753 |
| 34NB-6855 | CVBNM-08643 | 7 | HR | 2019年01月02日 13:00:00.211 |

I'm trying to query the SOFTWARE table for values in the 'NAME_Reg' column where not 'NULL'.

If two 'DATE_Reg' values are =< 5 sec apart they should be displayed in results on one row with the value matching 'SWType' of 8 in the 'Name' column, and the value matching 'SWType' of 7 in the 'Dept' column.

There will only ever by 2 entries in 'DATE_Reg' within a 5 sec time range of each other.

The results should display the corresponding 'PC_Hostname' linked on 'PC_ID', and the 'DATE_Reg' value corresponding to the 'SWType' value of 8 (Really I'd be ok with either of the two datetime values).

Desired Output

| PC_Hostname | Name | Dept | DATE_Reg |
+-------------+----------+-----------+-------------------------+
| ACMEINC_1 | Bob | Admin | 2018年02月19日 15:30:10.980 |
| ACMEINC_2 | Joan | Warehouse | 2018年11月23日 09:28:59.685 |
| ACMEINC_1 | Barry | Accounts | 2018年11月23日 09:46:21.321 |
| ACMEINC_1 | Sally | HR | 2019年01月02日 12:59:59.753 |

---

I'm still very very new to SQL and the closest I've gotten is with the following code after lots of googling.

USE ASSETS
SELECT c.PC_Hostname,
 CASE
 s.SWType
 when '8' then s.NAME_Reg
 END AS Name,
 CASE 
 s.SWType
 when '7' then s.NAME_Reg
 END AS Dept,
 s.DATE_Reg
FROM COMPUTERS c
 join SOFTWARE s ON (c.PC_ID = s.PC_ID)
WHERE s.NAME_Reg not like 'NULL'
ORDER BY c.PC_Hostname,s.DATE_Reg

However this results in the following output, which still has the Name & Dept values on alternating rows and has no time range matching. So after getting stuck at this point and not sure of a way forward I'm hoping someone on here could help.

| PC_Hostname | Name | Dept | DATE_Reg |
+-------------+----------+---------------+-------------------------+
| ACMEINC_1 | Bob | NULL | 2018年02月19日 15:30:10.980 |
| ACMEINC_1 | NULL | Admin | 2018年02月19日 15:30:12.001 |
| ACMEINC_1 | Barry | NULL | 2018年11月23日 09:46:21.321 |
| ACMEINC_1 | NULL | Accounts | 2018年11月23日 09:46:21.332 |
| ACMEINC_1 | Sally | NULL | 2019年01月02日 12:59:59.753 |
| ACMEINC_1 | NULL | HR | 2019年01月02日 13:00:00.211 |
| ACMEINC_3 | Joan | NULL | 2018年11月23日 09:28:59.685 |
| ACMEINC_3 | NULL | Warehouse | 2018年11月23日 09:29:00.103 |

Answer 1

You can join the software table to itself and use DATEADD() for the 5 second condition:

SELECT
 c.PC_Hostname,
 a.NAME_Reg AS Name,
 b.NAME_Reg AS Dept,
 a.DATE_Reg,
 b.DATE_Reg AS DATE_Reg_b 
FROM
 assets.software AS a
 JOIN
 assets.software AS b
 ON a.PC_ID = b.PC_ID
 AND a.SWType = 8
 AND b.SWType = 7
 AND a.DATE_Reg >= DATEADD(second, -5, b.DATE_Reg)
 AND a.DATE_Reg <= DATEADD(second, +5, b.DATE_Reg)
 LEFT JOIN
 assets.computers AS c
 ON a.PC_ID = c.PC_ID
WHERE
 a.NAME_Reg IS NOT NULL
 AND b.NAME_Reg IS NOT NULL
 ;