1 Reply Latest reply: Jun 1, 2015 9:43 AM by Alessandro Saccone RSS

    How to perform These Steps ?

    Ganesh Reddy

      Hi All,

       

      I got two table as mentioned below 1.Orders  2. Clearance i am finding difficulty while performing step 2 , how to scan through the coverage table to retrieve appropriate CoverageId ? . Please find below table info and steps.

       

      Orders:

      LOAD   

           ServiceEventId,

           ServiceEventEquipmentId,

           ProfitCenterId,

            ServiceEventStartDate,

           ServiceEventEndDate,

           ServiceStartDate,

       

      FROM

      [$(vQvdPath)\ Equip_O.qvd]


      Clearance:

      LOAD

          CoverageId,

           CoverageStartDate,

           CoverageEndDate,

           EquipmentId,

           CoverageType,

           CustomerpaidVSC_Flag,

           Warrantycontracts_flag

      FROM

      [$(vQvdPath)\ Equip _Cove.qvd]

      (qvd)

       

      Steps:

      1. Retrieve ServiceEventEquipmentId, and ServiceEventStartDate from the  Orders table.

      2. For the combination of above two ,scan through the Clearance table to retrieve the appropriate CoverageId for the work order. This CoverageId should be for the same EquipmentId as the ServiceEventEquipmentId and where the ServiceEventStartDate falls between

      CoverageStartDate and CoverageEndDate. (To Perform operations on Clearance table ).

       

       

      Can anyone help me on the Step 2 it would be very helpful for me .

       

      Cheers,

      Ganesh

        • Re: How to perform These Steps ?
          Alessandro Saccone

          Do as follows:

           

          Orders_Tmp:

          LOAD    

               ServiceEventId,

               ServiceEventEquipmentId,

               ProfitCenterId,

                ServiceEventStartDate,

               ServiceEventEndDate,

               ServiceStartDate,

           

          FROM

          [$(vQvdPath)\ Equip_O.qvd]

           

           

          left join

           

          LOAD

              CoverageId,

               CoverageStartDate,

               CoverageEndDate,

               EquipmentId as ServiceEventEquipmentId,

               CoverageType,

               CustomerpaidVSC_Flag,

               Warrantycontracts_flag

          FROM

          [$(vQvdPath)\ Equip _Cove.qvd]

          (qvd);

           

          Orders:

          noconcatenate

          load * resident Orders_Tmp where ServiceEventStartDate >= ServiceEventStartDate and ServiceEventStartDate <= ServiceEventEndDate;

          drop table Orders_Tmp;

           

           

          let me know