3

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 |
2

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
  ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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