9 Replies Latest reply: Jan 17, 2017 2:12 AM by sagar rahul RSS

    REGISTRATION_num

    sagar rahul

      hii community

       

      was having certain problem in raising query

      i have below columns

       

      VEH_REGISTRATION_NUM

      DATE_OF_LOSS

      policy_num

      start_date

       

      DATE_OF_LOSS is when vehicle has got theft


      we want to see if REGISTRATION_num has been rebooked again after CLM_DATE_OF_LOSS with new policy num


      so we want its

      new REGISTRATION_num

      new policy_num

      new start_date


      note:

      it should be after its date of loss

          

      VEH_REGISTRATION_NUMCLM_DATE_OF_LOSSpolicy_numstart_date
      MH-01-CB-112319-Apr-2014231220072788910007-Apr-2014
      HP-78-BU-45615-Apr-2016231220772724910008-Apr-2014
      DL-04-CNB-78919-Apr-2014231220071224910005-Apr-2014
      MH-01-CB-1123231220073212491009-Apr-2014

       

      as in bold we can see that MH-01-CB-1123 has been rebooked with new policy_num 2312200732124910 and its new start date is

      09-Apr-2014

        • Re: REGISTRATION_num
          Manish Kachhia

          how you want the final table?

            • Re: REGISTRATION_num
              sagar rahul

              final output we want that all the new policy_num which are booked with same REGISTRATION_num but after is date of loss

               

              eg:

              MH-01-CB-112319-Apr-2014231220072788910007-Apr-2014
              MH-01-CB-1123231220073212491009-Apr-2014

               

              above eg see MH-01-CB-1123 has been rebooked with new policy num

               

              so i want this in my output

               

              MH-01-CB-1123231220073212491009-Apr-2014

               

              i want the query

               

              now can u help me

            • Re: REGISTRATION_num
              Rahul Pawar

              Hello SR,

               

              Please refer below code for your reference:

               

              RegistrationData:
              LOAD
                  VEH_REGISTRATION_NUM,
                  CLM_DATE_OF_LOSS,
                  policy_num,
                  start_date
              FROM [lib://AttachedFiles/RegistrationData.xlsx]
              (ooxml, embedded labels, table is Sheet1);
              INNER JOIN
              LOAD
                  VEH_REGISTRATION_NUM AS VEH_REGISTRATION_NUM,
                  Date(Max(start_date),'DD-MMM-YYYY') AS start_date
              Resident RegistrationData
              Group by VEH_REGISTRATION_NUM;
              

               

              Hope this will help!

               

              Thank you!

              Rahul

                • Re: REGISTRATION_num
                  sagar rahul

                  ty for ur help rahul

                  but i want it in query form using wild matdmatch

                    • Re: REGISTRATION_num
                      Rahul Pawar

                      Hello SR,

                       

                      Could you please elaborate more on the requirement? Does the script given by me generating desired results? These questions will help me to further analyze this issue.

                       

                      Regards!

                      Rahul

                        • Re: REGISTRATION_num
                          sagar rahul

                          as i am new to this this thing they have given a small application where i do small thinks in qlkiview


                          so i cannot use your script but i can use query so i want the querry for above problem

                            • Re: REGISTRATION_num
                              Rahul Pawar

                              Hello SR,

                               

                              Your concern is still ambiguous. However, I have drafted SQL query. Please have a look at it.

                               

                              SELECT   r.VEH_REGISTRATION_NUM,  
                                       r.CLM_DATE_OF_LOSS,  
                                       r.policy_num,  
                                       r.start_date  
                              FROM     RegistrationData r
                                       INNER JOIN (
                                                   SELECT   r1.VEH_REGISTRATION_NUM,  
                                                            MAX(r1.start_date) AS start_date
                                                   FROM     RegistrationData r1
                                                   GROUP BY r1.VEH_REGISTRATION_NUM
                                                   )r2 ON r.VEH_REGISTRATION_NUM = r2.VEH_REGISTRATION_NUM
                                                          AND r.start_date = r2.start_date
                              

                               

                              Thank you!

                              Rahul