当DATEDIFF> 3时合并两行

I have a temp table which has the results of a main query where all records have been pivoted out. However, there are two date fields, that when they do not match, cannot pivot into a single row.

我有一个临时表，其中包含主查询的结果，其中所有记录都已被移出。但是，有两个日期字段，当它们不匹配时，无法转入单行。

I am checking if they have greater than a 3 day difference between them, and if there is, then I need to delete the oldest date and merge the rest of the columns together.

我正在检查它们之间是否有超过3天的差异，如果有，那么我需要删除最旧的日期并将其余的列合并在一起。

I am using SQL Server 2014

我正在使用SQL Server 2014

Example Table

示例表

+--------------+-------------+-------------------------------+
|     Lname    |    Date1    |     idCode1   |    idCode2    | 
+--------------+-------------+-------------------------------+
|    Higgins   |   11/30/16  |  9008   2172  |      NULL     |
|    Higgins   |   12/31/16  |     NULL      |   4007  3589  |             
|    Shaffer   |   11/15/16  |  9000   1541  |      NULL     | 
|    Shaffer   |   11/21/16  |     NULL      |   7889  9412  |
+--------------+-------------+-------------------------------+

Needs to look like this.

需要看起来像这样。

+--------------+-------------+-------------------------------+
|     Lname    |    Date1    |     idCode1   |    idCode2    | 
+--------------+-------------+-------------------------------+
|    Higgins   |   12/31/16  |  9008   2172  |   4007  3589  |             
|    Shaffer   |   11/21/16  |  9000   1541  |   7889  9412  |
+--------------+-------------+-------------------------------+

1 个解决方案

#1

Unless I'm missing something here, a simple group by should do it (Assuming you are only going to get max 2 rows for each Lname):

除非我在这里遗漏了一些东西，否则一个简单的小组应该这样做（假设你只为每个Lname获得最多2行）：

Create and populate sample table (Please save us this step in your future questions)

创建并填充样本表（请在将来的问题中保存此步骤）

DECLARE @T AS TABLE
(
    Lname varchar(10),
    Date1 date,
    idCode1 varchar(20),
    idCode2 varchar(20)
)

INSERT INTO @T VALUES
('Higgins', '11/30/16', '9008   2172',    NULL   ), 
('Higgins', '12/31/16',    NULL     , '4007  3589'),              
('Shaffer', '11/15/16', '9000   1541',    NULL   ),  
('Shaffer', '11/21/16',    NULL     , '7889  9412')

The query:

查询：

SELECT  LName, 
        MAX(Date1) As Date1, 
        MAX(IdCode1) As IdCode1, 
        Max(IdCode2) As IdCode2
FROM @T
GROUP BY LName
HAVING DATEDIFF(DAY, MIN(Date1), MAX(Date1)) > 3

Results:

结果：

LName       Date1                   IdCode1         IdCode2
Higgins     31.12.2016 00:00:00     9008   2172     4007  3589
Shaffer     21.11.2016 00:00:00     9000   1541     7889  9412

#1