多遍abc

Question

• 我想要不同的数字，如信用，资金，应用程序，ClosingDate为loan_officer。
• 我有一个表E.dbo.Main，我在其上应用不同的过滤器，然后尝试加入它们。这个主表以GUID作为主键。

请建议我该如何加入这些桌子。

    Select A.loan_officer, A.OrganizationId, [C].Credit as Credit,      
    [P].Application as 'App', [F].Funding as Fund, [B].ClosingDate as Brok      
    from

    --Credit Pulls     
    (SELECT Credit
    FROM  [E].[dbo].[Main]
    where Loan_Officer is not null
    and LOANFOLDER IN ('Pipeline', 'Prospect', 'Employee Pipeline')) as [C], 

    --Application Units
    (SELECT Application
    FROM  [E].[dbo].[Main]
    where loan_Officer is not null
    and LOANFOLDER IN ('Pipeline', 'Prospect', 'Employee Pipeline')
    and Denial_Date is null ) as [P],

    --Funding Units      
    (SELECT Funding
    FROM  [E].[dbo].[Main]
    where loan_Officer is not null
    and LOANFOLDER IN ('Pipeline', 'Prospect', 'Employee Pipeline')
    and Denial_Date is null) as [F],

    -- Brokered Units 
    (SELECT ClosingDate
    FROM [E].[dbo].[Main]
    where loan_Officer is not null
    and LOANFOLDER IN ('Pipeline', 'Prospect', 'Employee Pipeline')
    and Loan_Info_Channel_F2626 like 'Brok%'
    ) as [B],

    [E].[dbo].[Main] A     
    Join  [B] on B.ClosingDate_748 =A.ClosingDate_748        
    Join F on F.Funding_Date= A.Funding_Date         
    Join P on A.Application_Date= A.Application_Date               
    Join C on C.Credit_Pull_Date = A.Credit_pull_date         

Answer 1

@xQbert在这方面做了大量的工作，我只想对其进行一些扩展，以提取出常见的搜索条件，这样您就可以做一些索引，而不仅仅是扫描整个表。我还清理了case语句中的一些列，以返回正确的数据：

SELECT Loan_officer
     , OrganizationID
     ,Credit
     , CASE WHEN Denial_date is null  THEN [Application] ELSE NULL END as App
     , CASE WHEN Denial_date is null  THEN Funding else NULL end as Fund
     , CASE WHEN Loan_Info_Channel_F2626 like 'Brok%' THEN ClosingDate ELSE NULL end as Brok
FROM  [E].[dbo].[Main]
WHERE Loan_officer IS NOT NULL AND LOANFOLDER IN ('Pipeline', 'Prospect', 'Employee Pipeline')

Answer 2

我和斯西蒙在一起..。不需要联接，只需使用多个case语句；当前作为派生表的每个列都有一个。

SELECT Loan_officer
     , OrganizationID
     , CASE WHEN Loan_officer is not null and 
       LOANFOLDER IN ('Pipeline', 'Prospect', 'Employee Pipeline') THEN Credit else NULL end as Credit
     , CASE WHEN Loan_officer is not null and 
       LOANFOLDER IN ('Pipeline', 'Prospect', 'Employee Pipeline') and
       Denial_date is null  THEN Credit else NULL end as Apps
     , CASE WHEN Loan_officer is not null and 
       LOANFOLDER IN ('Pipeline', 'Prospect', 'Employee Pipeline') and
       Denial_date is null  THEN Credit else NULL end as Funding as Fund,
     , CASE WHEN Loan_officer is not null and 
       LOANFOLDER IN ('Pipeline', 'Prospect', 'Employee Pipeline') and
       Loan_Info_Channel_F2626 like 'Brok%' THEN ClosingDate else NULL end as Brok
FROM  [E].[dbo].[Main]