Sql-server – Select/Join multiple row values from Column A if dates in Column B are within ‘x’ seconds of each other

sql serversql server 2014sql-server-2008

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 |

Best Answer

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
  ;

Related Solutions

Sql-server – Select multiple rows from one row based on column values

I'd use UNPIVOT for this unless abr and ubr are both indexed and a relatively low proportion contain the value 1.

(Borrowing JNK's table variable)

SELECT id,
       Edition
FROM @prod
UNPIVOT (V FOR Edition IN (abr,ubr)) AS Unpvt
WHERE V = 1

Sql-server – set date value based on other row values

You can calculate the Delay in a cross apply and use dateadd() to add it to st_date in the main query.

select D1.Split3_ID,
       D1.CU_ID,
       D1.order_id,
       D1.st_date,
       D1.sku,
       D1.Priority,
       D1.Delay,
       dateadd(day, D2.Delay, cast(D1.st_date as date)) as CourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.st_date = D2.st_date and
                    D1.Priority > D2.Priority
              ) as D2(Delay)

SQL Fiddle

Update:

I am not really sure how you want to deal with different st_date within a order_id. The above query groups on order_id and st_date but I guess an alternative would be to use the lowest st_date per order_id in the dateadd function.

select D1.Split3_ID,
       D1.CU_ID,
       D1.order_id,
       D1.st_date,
       D1.sku,
       D1.Priority,
       D1.Delay,
       dateadd(day, D2.Delay, cast(isnull(D2.st_date, D1.st_date) as date)) as CourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0), 
                     min(D2.st_date)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.Priority > D2.Priority
              ) as D2(Delay, st_date)
order by D1.Split3_ID

Update 2:

The update statement for the second query could look like this.

with C as
(
select D1.CourseDate,
       dateadd(day, D2.Delay, cast(isnull(D2.st_date, D1.st_date) as date)) as NewCourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0), 
                     min(D2.st_date)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.Priority > D2.Priority
              ) as D2(Delay, st_date)
)
update C
set CourseDate = NewCourseDate

Update 3:

Or even like this if st_date is guaranteed to be the same within one order_id.

update D1
set CourseDate = dateadd(day, (select isnull(sum(D2.Delay), 0)
                               from dbo.set_dates as D2
                               where D1.order_id = D2.order_id and
                                     D1.Priority > D2.Priority), D1.st_date)
from set_dates as D1

Best Answer

Related Solutions

Sql-server – Select multiple rows from one row based on column values

Sql-server – set date value based on other row values

Related Question