SQL查询“情况” - 显示在同一行

Question

我想获得的提交2个文件的状态结果，是否“合格”，“失败”或从表中PROCESSED_FILE_LOGS“未提交”。

第一个文件名不包含“PCR”的文件名，第二个文件名中包含“PCR”的文件名，这就是我这2个文件之间如何区分。

合同＃是被提交这2个文件的那些和我结合4个不同的表来填充下方。

2点的问题我有这个表，当我运行我的查询[下文]：

我。'H0000'被重复，尽管查询，拉MAX（PF.processed_date） - 我试图得到文件1和文件2的最后处理状态。

ii。对于“H0000”，两者的文件不会在同一行显示，这意味着状态 - 在一个行对文件1个显示状态，以及在第二行中为文件2个显示状态。

我的查询：

select distinct 

      OC.cms_contract_number as 'Contract #' 
      ,case when (PF.PROCESSED_FILE_NAME not like '%PCR%' and FS.DISPLAY is not null) then FS.display else 'Not Submitted' end as 'File 1 Status' 
      ,case when (PF.PROCESSED_FILE_NAME like '%PCR%' and FS.DISPLAY is not null) then FS.display else 'Not Submitted' end as 'File 2 Status' 
      ,MAX(PF.processed_date) as 'Date Submitted' 

    from 
        ((((persons P join person_affiliations PA on P.person_id = PA.person_id) 
      join external_orgs EO on EO.org_id = PA.org_id) 
      join org_contracts OC on OC.org_contracts_id = PA.org_contracts_id) 
      left outer join  processed_file_logs PF on PF.org_contracts_id = OC.org_contracts_id) 
      left outer join processed_file_status FS on FS.file_status_id = PF.file_status_id 
    group by OC.CMS_CONTRACT_NUMBER, PF.SUBMISSION_ID, 
    FS.DISPLAY, PF.PROCESSED_FILE_NAME 

我在哪里去了？

Answer 1

好了，更新了这个答案，以反映您的评论如下 - 有一大堆语法在这里我可能搞砸了，这是不可能的，我将能够给你最终的完美解决方案，无需访问这些表，但在这里你去：

基本上你的数据是脱节的，因为要跨越多行数据汇总为两个不同的列。我们可以使用“with”查询来创建查找两个文件中每个文件的最后一次提交的数据表，然后通过合同编号键将其重新匹配到主数据集。

With A as (select OC.cms_contract_number as cms_contract_number, PF.PROCESSED_FILE_NAME as PROCESSED_FILE_NAME, FS.DISPLAY as DISPLAY, PF.processed_date as processed_date 
from 
      ((((persons P join person_affiliations PA on P.person_id = PA.person_id) 
    join external_orgs EO on EO.org_id = PA.org_id) 
    join org_contracts OC on OC.org_contracts_id = PA.org_contracts_id) 
    left outer join  processed_file_logs PF on PF.org_contracts_id = OC.org_contracts_id) 
    left outer join processed_file_status FS on FS.file_status_id = PF.file_status_id) 

select distinct 

    A.cms_contract_number as 'Contract #' 
    ,max(s1.FileStatus) as 'File 1 Status' 
    ,max(s2.FileStatus) as 'File 2 Status' 
    ,MAX(A.processed_date) as 'Date Submitted' 

from A 
left outer join 
(
    select distinct 

     A.cms_contract_number as 'Contract' 
     ,case when (A.DISPLAY is not null) then A.display else 'Not Submitted' end as 'FileStatus' 
     ,MAX(A.processed_date) as 'DateSubmitted' 

    from A 
    where A.PROCESSED_FILE_NAME not like '%PCR%' or A.PROCESSED_FILE_NAME is null 
    group by A.CMS_CONTRACT_NUMBER, case when (A.DISPLAY is not null) then A.display else 'Not Submitted' end 
) s1 on s1.Contract = A.CMS_CONTRACT_NUMBER and s1.DateSubmitted = A.processed_date 
left outer join 
(
    select distinct 

     A.cms_contract_number as 'Contract' 
     ,case when (A.DISPLAY is not null) then A.display else 'Not Submitted' end as 'FileStatus' 
     ,MAX(A.processed_date) as 'DateSubmitted' 

    from A 
    where A.PROCESSED_FILE_NAME like '%PCR%' or A.PROCESSED_FILE_NAME is null 
    group by A.CMS_CONTRACT_NUMBER, case when (A.DISPLAY is not null) then A.display else 'Not Submitted' end 
) s2 on s2.Contract = A.CMS_CONTRACT_NUMBER and s2.DateSubmitted = A.processed_date 
group by A.CMS_CONTRACT_NUMBER 

还要注意，如果你想提交的每个文件1和文件2日最后，这将是一个相当简单的练习将其拉出现在也：

在主查询仅只是改变：

MAX(PF.processed_date) as 'DateSubmitted' 

到：

max((select s1.DateSubmitted from StatusOfLastFile1 s1 where s1.Contract = A.CMS_CONTRACT_NUMBER)) as 'File 1 Last Submit', 
max((select s2.DateSubmitted from StatusOfLastFile2 s2 where s2.Contract = A.CMS_CONTRACT_NUMBER)) as 'File 2 Last Submit'