Missing Data

• PSB

  SSChampion

  Points: 13195

  More actions

  December 11, 2018 at 8:55 am

  #369364

  Hi,

  I need to compare tasks from template and real plans and list the actual plans names with those missing tasks from template based on Group and Task Name .

  CREATE TABLE #MissingData

  (

  Groups NVARCHAR(10),

  Tasks NVARCHAR(10),

  TemplateName NVARCHAR(10)

  )

  --Tasks that are missing in actual plan based on Group and Task and template plan

  INSERT INTO #MissingData ( Groups,Tasks,TemplateName )

  SELECT 'Group1','Task1','Temp1'

  UNION ALL

  SELECT 'Group1','Task2','Temp1'

  UNION ALL

  SELECT 'Group1','Task3','Temp1'

  UNION ALL

  SELECT 'Group2','Task3','Temp1'

  UNION ALL

  SELECT 'Group2','Task4','Temp1'

  UNION ALL

  SELECT 'Group2','Task5','Temp1'

  UNION ALL

  SELECT 'Group3','Task5','Temp1'

  UNION ALL

  SELECT 'Group4','Task1','Temp1'

  UNION ALL

  SELECT 'Group4','Task2','Temp1'

  UNION ALL

  SELECT 'Group4','Task3','Temp1'

  SELECT * FROM #MissingData

  --Which tasks exists in main table per group and Plan

  CREATE TABLE #MainTable

  (

  Groups NVARCHAR(10),

  Tasks NVARCHAR(10),

  PlanName NVARCHAR(10)

  )

  INSERT INTO #MainTable ( Groups,Tasks,PlanName )

  SELECT 'Group1','Task4','Plan1'--Task 1 , 3

  UNION ALL

  SELECT 'Group1','Task5','Plan1'

  UNION ALL

  SELECT 'Group1','Task6','Plan1'

  UNION ALL

  SELECT 'Group2','Task1','Plan2'--Task 5

  UNION ALL

  SELECT 'Group2','Task2','Plan2'

  UNION ALL

  SELECT 'Group2','Task7','Plan2'

  UNION ALL

  SELECT 'Group3','Task6','Plan3'

  UNION ALL

  SELECT 'Group4','Task4','Plan4'

  UNION ALL

  SELECT 'Group4','Task5','Plan4'

  UNION ALL

  SELECT 'Group4','Task6','Plan4'

  SELECT * FROM #MainTable

  --The tasks that is missing from #MaintAble per plan and Group as needed below compare to Missing Table . It should be expanded like the below

  SELECT 'Group1','Task1','Plan1'--Task 1 , 3

  UNION ALL

  SELECT 'Group1','Task2','Plan1'

  UNION ALL

  SELECT 'Group1','Task3','Plan1'

  UNION ALL

  SELECT 'Group4','Task1','Plan4'--Task 1 , 3

  UNION ALL

  SELECT 'Group4','Task2','Plan4'

  UNION ALL

  SELECT 'Group4','Task3','Plan4'

  UNION ALL

  SELECT 'Group2','Task3','Plan2'--Task 1 , 3

  UNION ALL

  SELECT 'Group2','Task4','Plan2'

  UNION ALL

  SELECT 'Group2','Task5','Plan2'

  UNION ALL

  SELECT 'Group3','Task5','Plan3'

  DROP TABLE #MainTable

  DROP TABLE #MissingData

  Thanks,
  PSB

• fahey.jonathan

  Hall of Fame

  Points: 3617

  More actions

  December 11, 2018 at 3:06 pm

  #2016034

  Thank you for the sample data and for the desired output.  Those are much appreciated.
  I still don't understand how the data relates.  In the #MainTable for Group1, I see Tasks 4, 5, 6, so I think I understand why the #MissingData shows Group1, Tasks 1, 2, 3.  However, for Group2, the #MainTable has Tasks 1, 2, 7, but the #MissingData has Group 2 Tasks 3, 4, 5.  I don't see a Task 6 listed as missing, even though there is not one in the main data.  The same applies for Group3; the main data has Tasks 6, so I expected to find 1, 2, 3, 4, 5 in the #MissingData, but only Task5 is listed as missing.  That makes me think either the sample data is wrong, or I don't understand how to determine what is missing from the main data.
  My guess was that we needed to find the highest "Task" number in the #MainTable for each Group, then see if all Tasks less than that value should be present in the #MainTable as well.  But that doesn't seem to be the case.  Please explain, for at least one record from your sample output, how to calculate that it is missing from the main data, and how to calculate the plan number.