Table Valued Parameter not pulling the correct data for my sp?
I'm going to suggest working to the solution in two phases. The first part is to make sure all the data you need is being returned. The second part is to combine the rows the way that you want.

I'm having trouble getting SqlFiddle to work with table types as well, so I'm going to work with the query directly. Here is the base query from above:

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
)
SELECT
  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,tsl.TransmittalSiteLeadFacility, 
  ts.TransmittalNumber, ts.TransmittalVersion,
  FacilityImpDate = COALESCE(tsl.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
FROM
  TransmittalSiteLead tsl
  INNER JOIN TransmittalSheet as ts
    ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
  INNER JOIN documentInfo as di 
    ON ts.transmittalID = di.documentTransmittalImp
  INNER JOIN f
    ON tsl.TransmittalSiteLeadFacility = f.FacilityId
WHERE
  transmittalSiteLeadImpDate 
    BETWEEN  @startdate AND @enddate


Even if there are duplicates or extra information, all of the data you want to return should be in there. If it isn't, then look at the JOINs or WHERE clause to see how to include that info.

The second part of the problem is to condense or limit the rows that are returned. There are a lot of ways to do this and the best way depends on a lot of factors. Here's what I'm sure is a partial list:


SELECT DISTINCT to limit results
GROUP BY with MIN to include information about a related table (See below)
EXISTS to limit the rows return
ROW_NUMBER() to pick a row based on an ordering (See below)
APPLY with TOP to get some information from a joined table


All this is to say that there's a lot of ways to do this, so do what makes sense for your situation. I've given two ways of doing this below, these aren't best for performance critical situations, but are easier to understand.

This query selects the document info and the staff id of someone who is implementing it.

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
)
SELECT
  di.DocumentNumber
  ,di.DocumentVersion
  ,di.DocumentTitle
  ,MIN(tsl.TransmittalSiteLeadStaffId) AS TransmittalSiteLeadStaffId
FROM
  TransmittalSiteLead tsl
  INNER JOIN TransmittalSheet as ts
    ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
  INNER JOIN documentInfo as di 
    ON ts.transmittalID = di.documentTransmittalImp
  INNER JOIN f
    ON tsl.TransmittalSiteLeadFacility = f.FacilityId
WHERE
  transmittalSiteLeadImpDate 
    BETWEEN  @startdate AND @enddate
GROUP BY
    di.DocumentNumber
    ,di.DocumentVersion
    ,di.DocumentTitle
ORDER BY
    di.DocumentNumber


Another way of doing it is using ROW_NUMBER. This way is very flexible, but can be more difficult to construct and understand.

This query selects matching documents and the facility and contact of the first to implement it. PARTITION sets how you want to divide the rows and ORDER BY sets how you want to select which matching row you want to use.

;WITH f AS
(
SELECT FacilityID FROM @facilities
UNION ALL SELECT @facilitydivisionID
),
FullData AS (
    SELECT
      di.DocumentNumber
      ,di.DocumentVersion
      ,di.DocumentTitle
      ,tsl.TransmittalSiteLeadFacility
      ,tsl.TransmittalSiteLeadStaffId
      ,ROW_NUMBER() OVER (PARTITION BY di.DocumentNumber ORDER BY tsl.TransmittalSiteLeadImpDate) AS RowNum
    FROM
      TransmittalSiteLead tsl
      INNER JOIN TransmittalSheet as ts
        ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
      INNER JOIN documentInfo as di 
        ON ts.transmittalID = di.documentTransmittalImp
      INNER JOIN f
        ON tsl.TransmittalSiteLeadFacility = f.FacilityId
    WHERE
      transmittalSiteLeadImpDate 
        BETWEEN  @startdate AND @enddate
)
SELECT
    DocumentNumber
    ,DocumentVersion
    ,DocumentTitle
    ,TransmittalSiteLeadFacility
    ,TransmittalSiteLeadStaffId
FROM
    FullData
WHERE
    RowNum = 1
ORDER BY
    DocumentNumber