Add missing data

• PSB

  SSChampion

  Points: 13195

  More actions

  June 20, 2018 at 12:33 pm

  #406734

  CREATE TABLE #Template

  (

  TaskName VARCHAR(20),

  TemplateName VARCHAR(20)

  )

  INSERT INTO #Template ( TaskName,TemplateName)

  SELECT 'Task1', 'Template 1' UNION ALL

  SELECT 'Task2', 'Template 1' UNION ALL

  SELECT 'Task3', 'Template 1' UNION ALL

  SELECT 'Task4', 'Template 1'

  SELECT * FROM #Template

  CREATE TABLE #TasksByPlan

  (

  TaskName VARCHAR(20),

  TemplateName VARCHAR(20),

  PlanName VARCHAR(20)

  )

  INSERT INTO #TasksByPlan ( TaskName,TemplateName,PlanName)

  SELECT 'Task1', 'Template 1' ,'Plan1'UNION ALL

  SELECT 'Task2', 'Template 1','Plan1' UNION ALL

  SELECT 'Task3', 'Template 1','Plan1' UNION ALL

  SELECT 'Task1', 'Template 1','Plan2' UNION ALL

  SELECT 'Task2', 'Template 1','Plan2' UNION ALL

  SELECT 'Task3', 'Template 1','Plan2' UNION ALL

  SELECT 'Task4', 'Template 1','Plan2' UNION ALL

  SELECT 'Task1', 'Template 1','Plan3' UNION ALL

  SELECT 'Task2', 'Template 1','Plan3'

  SELECT * FROM #TasksByPlan

  I want to add missing tasks per plan from the #Template table . Any tasks that are missing for each plan should be added to #TasksByPlan table .

  I will have to add the following missing tasks to #TasksByPlan

  SELECT 'Task4', 'Template 1','Plan1'

  SELECT 'Task3', 'Template 1','Plan3'

  SELECT 'Task4', 'Template 1','Plan3'

  Please help .

  Thanks,
  PSB

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  June 20, 2018 at 12:50 pm

  #1994709

  Here is one way
  WITH AllPlans
AS
(
  SELECT DISTINCT
    tbp.PlanName
  FROM #TasksByPlan tbp
)
INSERT #TasksByPlan
(
  TaskName
, TemplateName
, PlanName
)
SELECT
    t.TaskName
,    t.TemplateName
,    AllPlans.PlanName
FROM
    #Template t
CROSS JOIN AllPlans
EXCEPT
(SELECT
  tbp.TaskName
,  tbp.TemplateName
,  tbp.PlanName
FROM #TasksByPlan tbp);

  SELECT *
FROM #TasksByPlan tbp
ORDER BY
   tbp.TaskName
,  tbp.TemplateName
,  tbp.PlanName;

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  June 20, 2018 at 12:57 pm

  #1994710

  And another with the same technique:
  CREATE TABLE #Template (
    TaskName VARCHAR(20),
    TemplateName VARCHAR(20)
);
INSERT INTO #Template (TaskName, TemplateName)
SELECT 'Task1', 'Template 1' UNION ALL
SELECT 'Task2', 'Template 1' UNION ALL
SELECT 'Task3', 'Template 1' UNION ALL
SELECT 'Task4', 'Template 1'

  SELECT *
FROM #Template;

  CREATE TABLE #TasksByPlan (
    TaskName VARCHAR(20),
    TemplateName VARCHAR(20),
    PlanName VARCHAR(20)
);
INSERT INTO #TasksByPlan (TaskName, TemplateName, PlanName)
SELECT 'Task1', 'Template 1', 'Plan1' UNION ALL
SELECT 'Task2', 'Template 1', 'Plan1' UNION ALL
SELECT 'Task3', 'Template 1', 'Plan1' UNION ALL
SELECT 'Task1', 'Template 1', 'Plan2' UNION ALL
SELECT 'Task2', 'Template 1', 'Plan2' UNION ALL
SELECT 'Task3', 'Template 1', 'Plan2' UNION ALL
SELECT 'Task4', 'Template 1', 'Plan2' UNION ALL
SELECT 'Task1', 'Template 1', 'Plan3' UNION ALL
SELECT 'Task2', 'Template 1', 'Plan3';

  SELECT TBP.TemplateName, TBP.PlanName, TBP.TaskName
FROM #TasksByPlan AS TBP
ORDER BY TBP.TemplateName, TBP.PlanName, TBP.TaskName;

  WITH TasksByPlan AS (

      SELECT DISTINCT TBP.TemplateName, TBP.PlanName
    FROM #TasksByPlan AS TBP
),
    TaskTemplate AS (

          SELECT DISTINCT T.TemplateName, T.TaskName
        FROM #Template AS T
)
INSERT INTO #TasksByPlan (TaskName, TemplateName, PlanName)
SELECT TT.TaskName, TT.TemplateName, TBP.PlanName
FROM TaskTemplate AS TT
    INNER JOIN TasksByPlan AS TBP
        ON TT.TemplateName = TBP.TemplateName
        AND TBP.PlanName + TT.TaskName NOT IN (
            SELECT TBP2.PlanName + TBP2.TaskName
            FROM #TasksByPlan AS TBP2
            WHERE TBP2.TemplateName = TBP.TemplateName
            );

  SELECT TBP.TemplateName, TBP.PlanName, TBP.TaskName
FROM #TasksByPlan AS TBP
ORDER BY TBP.TemplateName, TBP.PlanName, TBP.TaskName;

  DROP TABLE #TasksByPlan;
DROP TABLE #Template;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• PSB

  SSChampion

  Points: 13195

  More actions

  June 21, 2018 at 7:44 am

  #1994823

  Thanks All. It works perfectly .