3 Replies Latest reply: Jul 18, 2016 4:54 AM by sagar rahul RSS

    max date along with clm_no

    sagar rahul

      this is my table below

             

      AGENT_FULL_NAMEPOL_AGENT_CODEHOSPITAL_NAMEHOSPITAL_CODECLM_INTIMATION_DATECLM_XGEN_CLAIM_NUMCLM_LEVEL_OF_CARE
      HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207104-Jan-2016RC-HS15-10325506-1Secondary
      HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207130-Nov-2015RC-HS15-10325506Secondary
      HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207124-Dec-2015RC-HS15-10329647Secondary
      HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207106-Dec-2015RC-HS15-10326507Secondary
      HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207126-Jan-2016RC-HS15-10325506-2Secondary
      HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207101-May-2016RC-HS16-10349544Secondary
      HE DIRECT 1000 -3420200278133519AASTHA HOSPITALHEGIC-HS-0207131-May-2016RC-HS16-10349544-1Secondary
      HE DIRECT 1000 -3420200278133519AASTHA HOSPITALTotal


       

                 

      ICD_CODE_LEVEL1_orgClaim_TypeICD Code Blacklistcount of claimsValuestatusMax_dateclm_noCurrent_dateDiffernceclm_num
      J22-10-Not Outlier04-Jan-2016RC-HS15-10325506-118/07/2016197-
      J22-111.000Outlier30-Nov-2015RC-HS15-1032550618/07/2016232-
      A01.0-111.000Outlier24-Dec-2015RC-HS15-1032964718/07/2016208-
      J22-111.000Outlier06-Dec-2015RC-HS15-1032650718/07/2016226-
      J22-10-Not Outlier26-Jan-2016RC-HS15-10325506-218/07/2016175-
      N39.0-010.000Not Outlier01-May-2016RC-HS16-1034954418/07/201679-
      N39.0-00-Not Outlier31-May-2016RC-HS16-10349544-118/07/201649-

      -541.250Outlier31-May-2016RC-HS16-10349544-118/07/201649-

       

       

      in this table till yellow color marked are showing proper ans as i want bt whn its coming to show max date its not showing what i want

       

      i want it like this max date should show only of main claims date only not of supplementary claims and in clm_no it should main claims

      along with max date

       

      this what i want

        

      Max_dateclm_no
      04-Jan-2016RC-HS15-10325506-1
      30-Nov-2015RC-HS15-10325506
      24-Dec-2015RC-HS15-10329647
      06-Dec-2015RC-HS15-10326507
      26-Jan-2016RC-HS15-10325506-2
      01-May-2016RC-HS16-10349544
      31-May-2016RC-HS16-10349544-1
      01-May-2016RC-HS16-10349544

       

       

      expression are used by me are :

       

      for max date is =max(CLM_INTIMATION_DATE)

       

      and for clm_no is =MaxString(aggr(MaxString(CLM_XGEN_CLAIM_NUM),HOSPITAL_CODE,HOSPITAL_NAME,CLM_INTIMATION_DATE,ICD_CODE_LEVEL1_org))

       

      so any1 can help me in this

      its on urgent basics