2 Replies Latest reply: Jan 19, 2014 12:45 PM by Dinesh Reddy Seelam RSS

    Dates Issue

    Dinesh Reddy Seelam

      Last try simple way

      DATA MODEL

      I have 2 tables one with fields- Primary key SID, warrstart, warr end end and other table with con start, con end

      Condition:

      Warr end was limited to current fiscal year 2013

      Issue:

      Duplicates issue after resident load of max warr end and min constart

      Example- when I join 2 tables

      There are 3 same sids with warrstart, warrend dates following constart, conend dates for diff con_types

      SIDWarr.CONTRACT_TYPECon.CONTRACT_TYPEWARR STARTWARR ENDCON STARTCON END
      1nullpp9/18/20133/17/20143/18/20149/17/2017
      1wspp9/18/20133/17/20143/18/20149/17/2017
      1ewpp9/18/20133/17/20143/18/20149/17/2017


      Requirement?

      -Show me only max warend and a following min contract start date and there corresponding records for each sid and dont show other records


      Testing:

      When I try to test only one table for max warr start date then also I am getting duplicates

      Example

      SIDWARR_CON TYPEWARR STARTWARR END
      2Warranty Support01/04/201314/01/2014
      2Warranty Support01/03/201314/01/2014
      2Warranty Support14/11/201214/01/2014

      In this case its picking max warr end but also all warr start dates which is not right it should be corresponding date for for max warr start date


      I am using resident loads for max warr end and min con start for each table

      Please help  .................



      Thanks in advance

        • Re: Dates Issue
          Stefan Wühl

          I think it would be easier if you post your input data (or a sample / mock up of the data), and your requested results made out of the input.

            • Re: Dates Issue
              Dinesh Reddy Seelam

              Hello Swuehl

               

              I attached a Sample QVW check it out

               

              Output should be 1st criteria MAX WARR END DATE  following MIN CONTRACT START DATE and corresponding dates

              not supposed to get any duplicates but in the sample u can see some duplicates

               

              Results expecting

               

              IDWarranty.CONTRACT_TYPEWARR STARTWARR ENDCON STARTCON ENDContracts.CONTRACT_TYPE
              1-02/04/201201/04/201301/10/201104/07/2014FS
              2EW01/01/201330/06/201311/03/201010/03/2014POP
              3EW--01/04/201031/03/2014POP
              4WS11/03/201310/03/2014---
              5EW01/01/201330/06/201327/11/201226/11/2016POP
              6EW21/01/201320/07/201301/01/201331/12/2016POP
              7WS13/06/201212/06/201301/01/201331/12/2016POP
              8---12/03/201312/03/2013POP
              9-27/06/201226/06/201309/07/201230/06/2014PP
              10-26/12/201225/12/201315/06/201215/06/2012FS