我的源数据看起来像这样:输入图片描述
现在,对于每个不同的PK,我只想提取与Z99代码相关的数据,它与Z10或Z39代码具有最接近的日期,并额外计算Z99和Z10/39日期的差异。
预期结果:此处输入图像描述
有人能指导我如何实现这个用例语句或任何其他更好的方式吗?谢谢。
我尝试了下面的代码,它为我工作。注意:
-
我已经创建了中间表来获得结果。如果您没有权限,您可以继续创建temptable或CTE
-
如果z39和z10的diff相同,则两者都将被视为您没有指定任何优先级
/* tbl refers to your input table */ /* daten refers to date */ create table ads as select a.daten, b.daten as z99_dt, a.pk, a.code as codea, b.code as codeb, (a.daten - b.daten) as diff from tbl as a inner join (select distinct code, pk, daten from tbl where code='z99') as b on a.code<>b.code and a.pk = b.pk where codea IN ('z39', 'z10');
在上面的代码片段中,将通过必要的计算创建广告。下面的代码片段将找到diff
的最小值create table ref as
select pk, min(diff) as min_diff
from ads
group by pk;
下面的代码片段连接ref表和ads表以获得最终结果
select a.pk, codea as st_code, daten as st_dt, codeb as end_code, z99_dt as end_dt, diff from ads as a
inner join ref as b
on a.pk=b.pk and a.diff=b.min_diff;
请尝试一下,让我知道这是否有效!