2 Replies Latest reply: Jan 18, 2017 6:50 AM by Saravanan S RSS

    Need Help to merge tables

    Saravanan S

      Hello,

       

      Thanks for your valuable input in advance....

       

      I have a hard time in finding a resolution for the below issue..

       

      I have 2 Tables with me..... One is Activity summary ( which has details of both open and closed activity) and the other one is open activity details....

       

      Activity summary has multiple occurrence of same activity, for example (Sample)

       

      Activity ID     AverageResponsetime   Totalturnaround

      1                         0                                   0

      1                         0                                   0

      1                         3                                   19

       

      I have an SQL query to get the activity that has Totalturnaround,

       

      SELECT [ACTIVITY_ID]

        FROM (

            SELECT *, CASE WHEN (max ([TURNAROUND_TIME]) OVER(PARTITION BY [ACTIVITY_ID]) = [TURNAROUND_TIME]) THEN 1 ELSE 0 END as MyRow

            FROM [eGActiveDB].[dbo].[EGMLR_SMY_ACTIVITY] ) t

        WHERE MyRow = 1 and (GMT_DATE > CONVERT(DATETIME, '2017-1-1 00:00:00', 102));

       

      I have an other table that has open activity and I want to exclude open activity from the activity summary,

       

      SELECT        ACTIVITY_ID, DEPT_ID, DEPT_NAME, USER_ID, ENTRYPOINT_NAME, ENTRYPOINT_ADD, TURNAROUND_TIME, TURNAROUND_TIME_BH, RESPONSE_TIME_BH,

                               MET_SLA, IS_REPLIED, ACTIVITIES_TURNEDAROUND, ACTIVITY_SUB_TYPE_VALUE

      FROM            EGMLR_SMY_ACTIVITY

      WHERE        (NOT EXISTS

                                   (SELECT        ACTIVITY_ID

                                     FROM            EGPL_CASEMGMT_ACTIVITY_LT_9000

                                     WHERE        (EGMLR_SMY_ACTIVITY.ACTIVITY_ID = ACTIVITY_ID)));

       

      Now I want to merge these two queries and load data into qlikview.....Load closed activities (that are not in open activity table and get unique value out the output)

       

      Activity ID     AverageResponsetime   Totalturnaround

      1                         0                                   0                  - activity OPen

      1                         0                                   0                  - activity OPen

      1                         3                                   19                  - activity OPen

      2                         0                                   0                  - activity Closed

      2                         0                                   0                  - activity Closed

      2                         3                                   22                  - activity Closed

      3                         0                                   0                  - activity Closed

      3                         0                                   0                  - activity Closed

      3                         3                                   33                  - activity Closed

       

      My end result has to be ,

       

      Activity ID     AverageResponsetime   Totalturnaround

      2                         3                                   22                  - activity Closed

      3                         3                                   33                  - activity Closed


      Please provide your expert view...


        • Re: Need Help to merge tables
          Rahul Pawar

          Hello Saravanan,

           

          Below is SQL Query level quick workaround:

          1. Write a SQL Query to get all the activity records i.e. Open, Closed, with/without Turn-around etc.

          2. Filter SQL Query written in step 1 by adding filter condition (In WHERE Clause) to get those records with Turn-around

          3. Again filter SQL Query written in step 1 by adding filter condition - NOT EXISTS (In WHERE Clause) to exclude the Open activity records


          Below is pseudo code:

           

          SELECT   a.*
          FROM     eGActiveDB.dbo.EGMLR_SMY_ACTIVITY a
          WHERE    1 = 1
          AND      a.ACTIVITY_ID IN (
                                     SELECT   ACTIVITY_ID
                                     FROM    (
                                              SELECT   *,
                                                       CASE WHEN (max ([TURNAROUND_TIME]) OVER(PARTITION BY [ACTIVITY_ID]) = [TURNAROUND_TIME]) THEN 1 ELSE 0 END as MyRow
                                              FROM     eGActiveDB.dbo.EGMLR_SMY_ACTIVITY
                                              ) t
                                     WHERE    MyRow = 1
                                     AND     (GMT_DATE > CONVERT(DATETIME, '2017-1-1 00:00:00', 102))
                                     )
          AND     NOT EXISTS (
                              SELECT   1
                              FROM     eGActiveDB.dbo.EGMLR_SMY_ACTIVITY
                              WHERE    NOT EXISTS (
                                                   SELECT   ACTIVITY_ID
                                                   FROM     EGPL_CASEMGMT_ACTIVITY_LT_9000
                                                   WHERE   (eGActiveDB.dbo.EGMLR_SMY_ACTIVITY.ACTIVITY_ID = ACTIVITY_ID)
                                                   )
                              AND      a.ACTIVITY_ID = eGActiveDB.dbo.EGMLR_SMY_ACTIVITY.ACTIVITY_ID
                              )
          
          

           

          Hope this will solve the purpose.

           

          Thank you!
          Rahul

            • Re: Need Help to merge tables
              Saravanan S

              Thank you Rahul. I took off NOT from your suggested code to get the output...

               

              1. AND     NOT EXISTS ( 
              2.                     SELECT   1 
              3.                     FROM     eGActiveDB.dbo.EGMLR_SMY_ACTIVITY 
              4.                     WHERE    NOT EXISTS