选择聚合忽略where原因

Question

我正在尝试将现有视图转换为我可以使用的格式。

视图vw_temp_appHoursLastTwoEntries看起来像这样:

RowNumber | PersNr | Client | Localtion | Agent | Date       | Calweek | Year
----------+--------+--------+-----------+-------+------------+---------+------
1         | 123    | 1      | 1         | ag-01 | 2020-01-01 | 1       | 2021
2         | 123    | 1      | 1         | ag-01 | 2020-01-03 | 1       | 2021
1         | 9999   | 1      | 4         | ag-01 | 2020-01-01 | 1       | 2021
2         | 9999   | 1      | 4         | ag-01 | 2020-01-07 | 1       | 2021

我需要一个不同的格式的数据，看起来像这样:

PersNr | Client | Localtion | Agent | minDate    | MaxDate    | DateDiff | Calweek | Year
-------+--------+-----------+-------+------------+------------+----------+---------+-------
123    | 1      | 1         | ag-01 | 2020-01-01 | 2020-01-03 | 3        | 1       | 2021
9999   | 1      | 4         | ag-01 | 2020-01-01 | 2020-01-07 | 7        | 1       | 2021

在原始格式中，一个人只有两行(RowNumber 1 and 2)。我想匹配每一列，并有最小和最大日期，以及在一个新的视图的差异。

我的代码:

select a.persnr, a.client, a.location, a.agent, a.calweek, a.year, 
max(a.date) as maxdate, min(b.date) as mindate
, DATEDIFF(day,a.date,b.date) as dDiff
from vw_temp_appHoursLastTwoEntries a
left join vw_temp_appHoursLastTwoEntries b on 
a.persnr = b.persnr and a.client = b.client and
a.agent = b.agent and a.date = b.date
where a.date != b.date and DATEDIFF(day,a.date,b.date) != 0
or (a.date is not null and b.date is not null) 
group by a.persnr, a.client, a.location, a.agent, a.calweek, a.year, DATEDIFF(day,a.date,b.date)

问题:

我目前得到返回值，它似乎在哪里的原因不生效，但我不明白为什么。

a.date != b.date不应该返回min-和maxdates相同的行。即使min-和maxdates不同，datediff也不会返回0以外的任何值。

Answer 1

很确定这就是你想要的:

declare @Test table (RowNumber int, PersNr int, Client int, Localtion int, Agent varchar(5), [Date] date, Calweek int, [Year] int);
insert into @Test (RowNumber, PersNr, Client, Localtion, Agent, [Date], Calweek, [Year])
values
(1, 123, 1, 1, 'ag-01', '2020-01-01', 1, 2021),
(2, 123, 1, 1, 'ag-01', '2020-01-03', 1, 2021),
(1, 9999, 1, 4, 'ag-01', '2020-01-01', 1, 2021),
(2, 9999, 1, 4, 'ag-01', '2020-01-07', 1, 2021);
select a.PersNr, a.Client, a.Localtion, a.Agent, a.Calweek, a.[Year]
, max(a.[date]) as maxdate
, min(b.[date]) as mindate
, abs(datediff(day,a.[date],b.[date])) as dDiff
from @Test a
left join @Test b on 
a.persnr = b.persnr and a.client = b.client and
a.agent = b.agent --and a.[date] = b.[date]
where (/*a.[date] != b.[date] and*/ datediff(day,a.[date],b.[date]) != 0)
and /*  not OR */ (a.[date] is not null and b.[date] is not null) 
group by a.persnr, a.client, a.Localtion, a.agent, a.calweek, a.[Year], abs(datediff(day,a.[date],b.[date]));

的回报:

<表类>PersNr客户Localtion代理Calweek年maxdatemindatedDifftbody><<tr>12311ag-01120212020-01-032020-01-012999914ag-01120212020-01-072020-01-016