sql - SQL-SSRS 报告

Question

我正在构建一个 SSRS 报告，数据如下图所示。

我需要根据标志将卡号分别显示为主要和次要卡号以及生效日期。如果客户有不止一张卡，我想显示最新的卡信息。

如何修改我的 sql 脚本以获得所需的输出。

谢谢你。

标签： sql

Answer 1

您可以使用条件聚合。我认为这是您想要的逻辑：

select cif_id, staff_id, package, segment,
       max(case when seqnum = 1 and primary_card_flag = 'Y' then card_number end),
       max(case when seqnum = 1  and primary_card_flag = 'N' then card_number end),
       max(case when seqnum = 1 and primary_card_flag = 'Y' then valid_from_date end),
       max(case when seqnum = 1 and primary_card_flag = 'N' then valid_from_date end)
from (select t.*,
             row_number() over (partition by cif_id, staff_id, package, segment, primary_card_flag
                                order by valid_from_date desc
                               ) as seqnum
      from t
     ) t
group by cif_id, staff_id, package, segment order;