Need Help to merge tables

[#1]

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...

[rahulpawarb · Accepted solution]

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

[#3]

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