CODEWITHSUNDEEP
c#sqldatabase-administration
Ron
Ron

Reputation: 1931

SQL Query for "Case" - displaying results on the same row

I am trying to get the status of 2 files submitted, whether 'Passed', 'Failed' or 'Not Submitted' from the table PROCESSED_FILE_LOGS.

The 1st file name does not contain 'PCR' in the filename, and the 2nd file name contains 'PCR' in the file name and that's how i differentiate between these 2 files.

The Contract # are the ones that are submitting these 2 files and I am combining 4 different tables to populate the below.

enter image description here

2 Issues I have with this table, when i run my query[below]:

i. 'H0000' gets repeated, in spite of the query, to pull the MAX(PF.processed_date) - I am trying to get the last processed status for both file 1 and file 2.

ii. For 'H0000', the status for both the files are not displaying in the same line., meaning - the status for File 1 displays in one row, and the status for File 2 displays in the second row.

My query:

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

Where am i going wrong ?

Upvotes: 1

Views: 741

Answers (1)

user681574
user681574

Reputation: 553

Ok, updated this answer to reflect your comments below - there is a whole lot of syntax here I may have messed up and it is unlikely I'm going to be able to give you the perfect final solution without access to those tables, but here you go:

Basically your data is disjointed since you want to summarize the data across multiple rows into two different columns. We can use the "with" queries to create tables of data that are finding the last submission for each of the two files and then re-matching it to the main dataset by the contract number key.

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

Note also, that if you want the date Last submitted for each File1 and File2, it would be a fairly trivial exercise to pull it out now also:

In the main query ONLY just change:

MAX(PF.processed_date) as 'DateSubmitted'

to:

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'

Upvotes: 1

Related Questions