Archives of the TeradataForum

Message Posted: Mon, 14 Aug 2006 @ 14:47:52 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Pratheesh,

This should give you what you need. The first clause gets the claim lines with only one adjustment and the second clause gets all the claim lines with more than one adjustment. The union all is used because a record in clause one would never show up in clause two.

     -- ---------------------------------------------------------------
     Select
             CLM_LN_ADJMT_KEY,
             CLM_LN_SRVC_STRT_DT,
             SRC_DW_CD,
             CLM_SRC_SYS_CD,
                  CLM_NBR,
             CLM_LN_NBR,
             MBR_KEY,
          CLM_LN_ADJMT_NBR as "MAX_ADJ_NBR"
     >From  DFLT_CLM_GTT
     Where SRC_DW_CD = :src_dw_cd_in
     GROUP BY  PARTITION BY CLM_NBR, CLM_LN_NBR, SRC_DW_CD,
     CLM_SRC_SYS_CD,MBR_KEY
     HAVING COUNT(*) = 1
     UNION ALL
     select
             a.CLM_LN_ADJMT_KEY,
             a.CLM_LN_SRVC_STRT_DT,
             a.SRC_DW_CD,
             a.CLM_SRC_SYS_CD,
                  a.CLM_NBR,
             a.CLM_LN_NBR,
             a.MBR_KEY,
          MAX(a.CLM_LN_ADJMT_NBR) as "MAX_ADJ_NBR"
     FROM (Select
                     b.CLM_LN_ADJMT_KEY,
                     b.CLM_LN_SRVC_STRT_DT,
                     b.SRC_DW_CD,
                     CLM_SRC_SYS_CD,
                          b.CLM_NBR,
                     b.CLM_LN_NBR,
                     b.MBR_KEY,
                  b.CLM_LN_ADJMT_NBR
             From  DFLT_CLM_GTT b
             Where b.SRC_DW_CD = :src_dw_cd_in
             GROUP BY b.CLM_NBR, b.CLM_LN_NBR, b.SRC_DW_CD,
     b.CLM_SRC_SYS_CD,b.MBR_KEY, b.CLM_LN_ADJMT_NBR
             HAVING COUNT(*) > 1 ) a
     Where a.SRC_DW_CD = :src_dw_cd_in
       and a.clm_disp_cd = 'READJ'
     GROUP BY
             a.CLM_LN_ADJMT_KEY,
             a.CLM_LN_SRVC_STRT_DT,
             a.SRC_DW_CD,
             a.CLM_SRC_SYS_CD,
                  a.CLM_NBR,
             a.CLM_LN_NBR,
             a.MBR_KEY
     ORDER BY CLM_NBR,CLM_LN_NBR;

     -- ---------------------------------------------------------------

Regards,

Jack A. Richter
Data Management Consultant/Specialist
Kaiser Permanente

	<Prev	Next>		<<First	<Prev	Next>	Last>>