title: Finding distinct field in a Stored Procedure
body: <p>I have been working on the Stored Procedure and trying to get distinct DBNs depending on the latest CEPCertifiedNL. Right now the problem is if a DBN has a different NetworkLeader then school is displayed twice. I am not being able to figure it out how to get only 1 DBN even though the Network leader is different. Please see my stored proc below:</p>
  
  <pre><code>DECLARE @CEPDocuments TABLE(
          Id int,
          SchoolId int,
          Submitted char DEFAULT 'Y')
  
  DECLARE @CEPReviewed TABLE(
          Id int,
          SchoolId int,
          Submitted char DEFAULT 'Y')
  
  DECLARE @CEPDocuments TABLE(
          Id int,
          SchoolId int,
          Submitted char DEFAULT 'Y')
  
  DECLARE @CEPReviewed TABLE(
          Id int,
          SchoolId int,
          Submitted char DEFAULT 'Y')
  
  DECLARE @CEPCertifiedSup TABLE(
          Id int,
          SchoolId int,
          Submitted char DEFAULT 'Y',
          SupName varchar(255),
          Note nvarchar(max),
          CreationMethod_S nvarchar(1))
  
  
  DECLARE @Invitations TABLE(
          dateAccepted datetime,
          SchoolId int)
  
  DECLARE @Reviews TABLE(
          Id int,
          SchoolId int,
          Note nvarchar(max))
  
  DECLARE @PLANDETAILS TABLE(
          SchoolId int,
          TemplateType varchar(10))
  
  DECLARE @YEAR int
  SET @YEAR = 2014
  
  DECLARE @planIds TABLE(Id int);
  INSERT INTO  @planIds( Id ) SELECT Id FROM Plans as p WHERE p.SchoolYear = @YEAR;
  
  DECLARE @cepDocumentIds TABLE(Id int);
  INSERT INTO @cepDocumentIds( Id ) 
  SELECT d.Id FROM Documents as d 
      INNER JOIN Plans as p ON d.PlanId = p.Id 
      WHERE ( d.Name LIKE '% CEP' OR d.Name LIKE '%SCEP - F' OR d.Name LIKE '%SCEP - P') AND p.SchoolYear = @YEAR;
  
  
  INSERT INTO @CEPDocuments (Id, SchoolId)
  SELECT MAX(Id) as Id, SchoolId FROM DocumentFiles 
  WHERE DocumentId in (SELECT * FROM @cepDocumentIds)
  AND DateDeleted IS NULL 
  GROUP BY SchoolId
  
  
  INSERT INTO @CEPCertifiedSup (Id, SchoolId, SupName, Note,CreationMethod_S)
  SELECT MAX(r.Id) as Id
      ,r.SchoolId
      ,p.FirstName + ' ' + P.LastName as 'FullName'
      ,r.Note
      ,r.CreationMethod
  FROM Reviews r
  INNER JOIN People p
      on r.PersonId = p.Id
  WHERE
      r.PlanId in (5,6,7)
      AND r.Note LIKE 'Superintendent % certified%'
      AND r.CreationMethod in ('C','W')
      AND r.DocumentId IS NULL
  GROUP BY r.SchoolId, p.LastName, P.FirstName,r.SchoolId,r.Note,r.CreationMethod
  
  
  DECLARE @CEPCertifiedNL TABLE(
          Id int,
          SchoolId int,
          Submitted char DEFAULT 'Y',
          NLName varchar(255),
          Note nvarchar(max),
          CreationMethod_N nvarchar(1),
          DateCreated DateTime)
  INSERT INTO @CEPCertifiedNL (Id, SchoolId, NLName,Note,CreationMethod_N,DateCreated)
  SELECT MAX(r.Id) as Id
      ,r.SchoolId
      ,MAX(p.FirstName + ' ' + P.LastName) as 'FullName'
      ,r.Note
      ,r.CreationMethod
      , r.DateCreated 
  FROM Reviews r
  INNER JOIN People p
      on r.PersonId = p.Id
  WHERE
      r.PlanId in (5,6,7)
      AND r.Note like 'Network Leader % approved %'
      AND r.CreationMethod in ('C','W')
      AND r.DocumentId IS NULL
      AND r.PersonId IS NOT NULL
      AND r.DateCreated IS NOT NULL
  GROUP BY r.SchoolId,r.Note,r.CreationMethod,r.DateCreated
  
  INSERT INTO @Invitations (dateAccepted, SchoolId)
  SELECT MAX(dateAccepted) as Id, SchoolId FROM Invitations WHERE membertitle = 'principal'  GROUP BY SchoolId
  
  
  INSERT INTO @Reviews (Id, SchoolId, Note)
  select Id, SchoolId, Note FROM Reviews 
  WHERE Id in (SELECT MAX(Id) FROM Reviews WHERE StatusId = 6 and DocumentId in (27,32,36) GROUP BY SchoolId)
  
  INSERT INTO @PLANDETAILS (SchoolId, TemplateType)
  SELECT  SchoolId
          ,CASE WHEN (p.Name LIKE 'CEP%') THEN 'CEP'
          WHEN (p.Name LIKE 'SCEP - F%') THEN 'SCEP - F'
          ELSE 'SCEP - P' END as 'TemplateType' 
  FROM    PlanDetails as pd
  INNER JOIN Plans as p ON pd.PlanId = p.Id
  WHERE   PlanId in (SELECT * FROM @planIds)
  
  
  SELECT rp.Value AS DBN
  
      ,pd.TemplateType as '2014 Template Type'
      ,ISNULL(cep.Submitted,'N') AS 'Submitted'
  
  
  
      ,CASE WHEN cerNL.Submitted IS NOT NULL 
          THEN case cerNL.CreationMethod_N
                  when 'W' then 'R'
                  else 'Y'
              end
          ELSE 'N' END  as 'Certified By NL'
      ,cerNL.NLName as 'Certifying NL'
  
      ,CASE WHEN cerSup.Submitted IS NOT NULL 
          THEN case cerSup.CreationMethod_S
                  when 'W' then 'R'
                  else 'Y'
              end
          ELSE 'N' END  as 'Certified By Sup'
  
      ,cerSup.SupName as 'Sup Name'
      ,cerNL.Note as 'NL Feedback on SCEP/CEP'
      ,cerSup.Note as 'SUP Feedback on SCEP/CEP'
      ,cerNL.DateCreated as 'Date'
  FROM Schools s
  RIGHT JOIN @Invitations i ON i.SchoolId = s.id
  LEFT JOIN ReferenceProperties rp ON rp.SchoolId = s.id
  LEFT JOIN @CEPDocuments cep ON cep.SchoolId = s.id
  LEFT JOIN @CEPCertifiedSup cerSup ON cerSup.SchoolId = s.id
  LEFT JOIN @CEPCertifiedNL cerNL ON cerNL.SchoolId = s.id
  
  LEFT JOIN @PLANDETAILS pd on s.Id = pd.SchoolId
  WHERE 
      rp.PropertyName = 'DBN' AND
      rp.Value not like '00T%'
  ORDER BY DBN
  </code></pre>
  
  <p>Please see the table below:</p>
  
  <pre><code>    DBN 2014 Template Type  Submitted   Certified By NL Certifying NL   Certified By Sup    Sup Name    NL Feedback on SCEP/CEP SUP Feedback on SCEP/CEP    Date
  02M308  NULL    N   Y   Nancy Scala Y   Marisol Bradbury    Network Leader Nancy Scala approved 2013-2014 document. Superintendent Marisol Bradbury certified 2013-2014 document.   2014-01-13 15:08:43.260
  02M312  NULL    N   Y   Nancy Scala Y   Mariano Guzman  Network Leader Nancy Scala approved 2013-2014 document. Superintendent Mariano Guzman certified 2013-2014 document. 2014-03-24 22:37:22.980
  02M313  NULL    N   Y   Alison Sheehan  Y   Marisol Bradbury    Network Leader Alison Sheehan approved 2013-2014 document.  Superintendent Marisol Bradbury certified 2013-2014 document.   2014-01-12 21:40:41.570
  02M313  NULL    N   Y   Alison Sheehan  Y   Marisol Bradbury    Network Leader Alison Sheehan approved 2013-2014 document.  Superintendent Marisol Bradbury certified 2013-2014 document.   2014-10-01 12:00:00.000
  </code></pre>