Home How to join a sub-query to
Reply: 1

How to join a sub-query to

alexedeigba
1#
alexedeigba Published in 2017-12-07 16:42:56Z

Hey guys I have this report that i am working on. It has multiple Columns and in between those columns are some Left joins. so my question is, how do i add a left join to the query?

Declare @asofdate smalldatetime                                                                                 

Set @asofdate = '2017-08-24'

SELECT                                                                                  
      OCCONR                        as [Company Number],        
      OCCMNR                     as [Claim Number],    --CMOCUR db)
      OCEXCD                        as [Claim Counsel],   ---(CMOCUR db)
      DATEFROMPARTS(OCEFCN*100+OCEFYY,OCEFMM,OCEFDD)    as [Policy Effective Date], -- (CMOCUR db)
      iif([True CAT Nbr] is null, OCCTCD, [True CAT Nbr])   as [CAT Code],   ---(Check Comment 1)
      iif(LSCMNT is null,'', LSCMNT)    as [NOA Comments], --- (Check Comemnt 2)
      concat([First_Name],' ',[Last_Name]) as [Insured Name], --- (Check Comment 3)
      DatefromParts([OCLSCN]*100+[OCLSYY],[OCLSMM],[OCLSDD])    as [Occurrence Date], --- (CMOCUR db)
      rtrim(ltrim(OCDACC)) as [Cause of Loss],    --- (CMOCUR db)
      DatefromParts([OCRPCN]*100+[OCRPYY],[OCRPMM],[OCRPDD])    as [Claim Reported Date],  --- (CMOCUR db)
      iif(LSSETLD is null,'', LSSETLD)  as [Settled Indicator],  --- (Check Comment 2)
      --iif(SUM([Incurred Loss NET OF S&S]) - SUM([Paid Loss NET OF S&S]) = 0, 'Closed', 'Open') as [Status in AS-400]
      iif(OCCLCN=0,'',datefromparts(OCCLCN*100+OCCLYY,OCCLMM,OCCLDD)) as [Closed Date],   --- (CMOCUR db)
      iif(LSCLCN=0 OR LSCLCN IS NULL,'',DatefromParts(LSCLCN*100+LSCLYY,LSCLMM,LSCLDD)) as [All Closed Date]    ----(Comment 2)
      --[Last Reserve Change Date]  

FROM AS400.FRTDTA250.CMOCUR

LEFT JOIN Lookups.dbo.[Bad CAT Codes]                   ON [Claim Nbr] = OCCMNR     ---- This LEFT JOIN provides the CAT CODE for this report  (Comment 1)
LEFT JOIN AS400.FRTDTA250.CMLSCP                        ON LSCMNR = OCCMNR        ---- This is used to pull any COMMENTS and SETTLEMENT made on a claim   (Comment 2)
LEFT JOIN Lookups.dbo.Insured_Name_By_PolicyNum         ON Prefix = OCPRFX and Polnum = OCPLNR      --- This is to add the INSURED NAME        (comment 3)

So, the issue i am having is that i have this other sub-queries that are meant to provide information for the [Status in AS-400] and [Last Reserve Change Date]. How do i connect this queries to the SQL?

Below are the 2 queries that i am trying to add to the select query:

-- Query for [Status in AS-400]

SELECT 
     ---s1.[Claim Number],
     iif(SUM(s1.[Incurred Loss NET OF S&S]) - SUM(s1.[Paid Loss NET OF S&S]) = 0, 'Closed', 'Open') as [Open / Closed]
     ---SUM(s1.[Incurred Loss NET OF S&S]) - SUM(s1.[Paid Loss NET OF S&S])  as [Loss C/R]

FROM            
(   
   SELECT 
   OCCMNR as [Claim Number],        
   SUM(iif(DATASETTYPE IN ('Incurred LOSS'), CTTRAM, iif(DATASETTYPE IN ('SALVAGE','SUBROGATION','OTHER RECOVERIES'), -CTTRAM, 0))) AS [Incurred Loss NET OF S&S],      
   SUM(iif(DATASETTYPE IN ('Paid LOSS'), CTTRAM, iif(DATASETTYPE IN ('SALVAGE','SUBROGATION','OTHER RECOVERIES'), -CTTRAM, 0))) AS [Paid Loss NET OF S&S]       

   FROM AS400.FRTDTA250.[CMOCUR] AS A           

   INNER JOIN   AS400.FRTDTA250.[CMTRAN]AS B ON A.OCCMNR = B.CTCMNR

   LEFT JOIN Lookups.dbo.TransactionCodes AS C ON B.CTGRPC = C.TransactionCode

   WHERE    DATEFROMPARTS(CTTRCN*100+CTTRYY, CTTRMM, CTTRDD) <= @asofdate       
        and CTTRSB not in (4,12)        
        and CTTSCD = 'P'

   GROUP BY OCCMNR

   ) as s1          

   GROUP BY s1.[Claim Number]

   ORDER BY  s1.[Claim Number]

--Query for [Last Reserve Change Date]

  Select [Claim Number],                                                                                    
  left(convert(CHAR, Max([Loss Transaction Date]), 120),10) as [Last Reserve Change Date]                                                                               

  From                                                                                          
     (                                                                                          
       Select [OCCMNR] as [Claim Number],                                                                           
       DATEFROMPARTS(CTTRCN*100+CTTRYY, CTTRMM, CTTRDD) as [Loss Transaction Date],                                                                         
       iif(DATASETTYPE IN ('Incurred LOSS'), CTTRAM, 0) as [Incurred Loss]                                                                      

       From AS400.FRTDTA250.[CMTRAN] as b1                                                                              

       LEFT JOIN AS400.FRTDTA250.[CMOCUR] as a1                                                                             
       ON a1.OCCMNR = b1.CTCMNR                                                                                 

       LEFT JOIN Lookups.dbo.TransactionCodes as c1                                                                             
       ON b1.CTGRPC = c1.TransactionCode                                                                                    

       Where (iif(DATASETTYPE IN ('Incurred LOSS'), CTTRAM, 0) <> 0)                                                                                    
       and DATEFROMPARTS(CTTRCN*100+CTTRYY, CTTRMM, CTTRDD) <= @asofdate                                                                                    
       and CTTRSB not in (4,12)                                                                                     
       AND CTTSCD = 'P'                                                                                     
       ) s2                                                                                         

       group by  [Claim Number]                                                                                         

       ) as G                                                                                           
       on G.[Claim Number] = a.[OCCMNR]                                                                                 

And i use a MS-SQL Server

Kashif Qureshi
2#
Kashif Qureshi Reply to 2017-12-07 17:20:04Z

Format for joining Sub-queries are as follows:

SELECT *
FROM TABLEA A
INNER JOIN TABLED D on A.ID = D.ID
LEFT JOIN
  ( SELECT B.ID,COLUMN_A
    FROM TABLEB B
    INNER JOIN TABLEC C ON B.ID = C.ID
  ) X ON A.ID = X.ID
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.317881 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO