0

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
*/
5
  • The fiddler is helpful, but I'm not seeing values match between DocumentInfo and TransmittalSheet. Also if you could describe exactly what you want. Are there any documents missing from the current query? The way I understand the question, you should be getting the documents that you need. For the second part, are you only returning information about the document, or is there information from other tables as well? You might review the DISTINCT clause to see if it does what you need. May 7, 2014 at 4:18
  • @AlwaysLoadingData I just corrected the data and linked TransmittalNumber 8826 for example with its 5 documents in the DocumentInfo table. I will link the rest soon too to have more records returned correctly in SQLfiddle. So I'm getting some of the documents I need but not all as you are correct, I need information from the other table too: TransmittalSiteLead if you can see that in my stored procedure in sqlfiddle. This table has the different Facility numbers that I want to base my search on. So I need to get all documents implementing for each facility combined.. May 7, 2014 at 19:39
  • thats why I created the table valued parameters to hold the facility numbers seperately. But so far, the stored proc in SQLfiddle is not passing successfuly vs what I have in my server. May 7, 2014 at 19:40
  • sqlfiddle.com/#!3/3022e/1 May 7, 2014 at 19:42
  • So TransmittalID is the primary key in TransmittalSheet table, the foreign keys for the other 2 tables for that primary key is the DocumentTransmittalImp and the TransmittalSiteLeadTSID sqlfiddle.com/#!3/3022e/3 May 7, 2014 at 19:51

1 Answer 1

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
4
  • thats great, thank you so so much, I tried your second suggestion with the Row Partitioning, at first I was still getting results for the divitional facility 6 documents only, then I changed all the inner joins to Full joins, its returning all the docs that I needed when I compare it to the individual reports I had for each facility at that specific date range. I will do more testing ofcourse, but you really solved it for me as I didn't know about Row_Number. May 8, 2014 at 17:39
  • If you need FULL joins in order to get the documents that you need, then take a close look at your data. You might have documents that don't have a TransmittalSheet or Sheets that don't have a SiteLeadFacility. Look for where the NULL values are in those full joins. May 9, 2014 at 2:30
  • Your are right, I just noticed that this week when I ran the report, so I changed the full join to Inner join only between TransmittalSheet and DocumentsInfo tables as so far there is always a TransmittalSiteLeadFacility entered for any Sheet. The only issue now is that I needed it to print the FacilityID or the facilityName in the query results, so I added the Facility table to the join clause with TransmittalSiteLead, then I added the column "FacilityName" in the Select statement you have at the end of the script but its not reflecting the correct fac for some documents! May 14, 2014 at 23:07
  • I mean its giving me the documents that should be printed but when a doc has the "DivitionalID of 6", that means this document should implements for all other facilities and I should see "Div" under the FacilityName column printed, but for couple of documents today, it was printing "Arizona" or other facility.. So ultimately I want the documents that's implementing divitionally (all facilities) to show "Div" and for other documents that are implementing for multipple facilities but not applicable ot other fac, to print that specific fac.. thanks and my appologies for any confusion. May 14, 2014 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.