Table Valued Parameter not pulling the correct data for my sp?

Question

http://sqlfiddle.com/#!3/b2643/5/1

http://sqlfiddle.com/#!3/b2643/5/0

This link above has the sample database tables I created on SQLfiddle.

I have 3 main tables in my stored procedure:

TransmittalSheet, DocumentInfo and TransmittalSiteLead. Each TS can have many documents released with it and also many site lead staffs for each Transmittal that implements at different times. Our users run reports through the use of a VB application but its pulling the data from a SQL Server in the back end via stored procedures. Currently, they can pull all docs implemening per facility individually/ seperately, Sometimes these documents are shared or implemented in multipple facilities at the same date.

My goal is to get all the documents implementing for all facilities (5 of them) at once for the one division (West) within specific date range, then at the end I would like to take care of duplicates or I mean showing the same shared document once.

With the help of Aaron Bertrand, he was able to show me the use of Table Valued Parameter, now I get partial results, versus all documents that I see when I run the original individual stored procedure that has one variable pointing to one facility at a time, so I wasn't sure what I'm missing in my query, please advice

CREATE TYPE dbo.Facilities AS TABLE
(
  FacilityID TINYINT PRIMARY KEY
);
go


declare @F dbo.Facilities;
Insert @F Values (1), (2), (3), (4), (5), (7);

-- Facility 6 is considered the Division go

create PROCEDURE [dbo].[GetImpDocsThree]
  @facilities dbo.Facilities ReadOnly,
  @facilitydivisionID tinyint = 6,
  @startdate smalldatetime = '06/16/2005',
  @enddate smalldatetime = '04/28/14'
AS
BEGIN
  SET NOCOUNT ON; 

  ;WITH f AS
  (
    SELECT FacilityID FROM @facilities
    UNION ALL SELECT @facilitydivisionID
  )

  SELECT  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,      f.FacilityID,tsl.TransmittalSiteLeadFacility, 
ts.TransmittalNumber, ts.TransmittalVersion,
FacilityImpDate = COALESCE(tsl.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
   FROM dbo.DocumentInfo AS di
   INNER JOIN dbo.TransmittalSheet AS ts 
     ON di.DocumentTransmittalImp = ts.TransmittalID
     LEFT OUTER JOIN dbo.TransmittalSiteLead AS tsl ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
   INNER JOIN f 
     ON tsl.TransmittalSiteLeadFacility = f.FacilityID

-- TSSiteLeadImpDate sometimes have different dates than TransmittalImpDate

-- Select documents implementing within the date range  

        Where transmittalSiteLeadImpDate 
    BETWEEN  @startdate AND @enddate
     order by di.DocumentNumber

    END


-- Above runs successfully but it returns partial data as I need something like this   for the last part in the date range query as it was working for the individual sp:

/*
-- Select documents implementing within the date range
            where ((SELECT dbo.TransmittalSiteLead.TransmittalSiteLeadImpDate 
            FROM dbo.TransmittalSiteLead
            --WHERE dbo.DocumentInfo.DocumentTransmittalImp = dbo.TransmittalSiteLead.TransmittalSiteLeadTSID
            where (dbo.TransmittalSiteLead.TransmittalSiteLeadFacility = f.facilityID OR 
                    dbo.TransmittalSiteLead.TransmittalSiteLeadFacility = @facilitydivisionID))
                     BETWEEN  @startdate AND @enddate)

order by di.DocumentNumber

              END
*/

Answer 1

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