none
I need to add a condition in a complex SQL Statement. Need a bit of help! RRS feed

  • Question

  • I have this wonderfully working statement, that I received much great help from this forum to produce (Special thanks to @A2605).  However, I need to add one more condition.  I think its simple for someone who is in the know of the proper syntax.  Unfortunately, I am not one of those people, but I did give it the college try.

    Here is the original statement:

    WITH ALL_COMBINED AS
    
    (SELECT TOP (100) PERCENT DocumentID, [49] AS [DwgRevision], [47] AS [Description], [45] AS [ProjectName], [54] AS [PartNumber], CurrentStatusID, Pending, LatestRevisionNo, Filename, [57] AS [Version], [56] AS [Material], 
                      [73] AS [RvTbl_Description], [76] AS [RvTbl_DwgDate], [75] AS [RvTbl_Approved], [74] AS [RvTbl_Revision]
    FROM     (SELECT DocumentID, VariableID, TextOutput, CurrentStatusID, Filename, LatestRevisionNo, CASE WHEN CurrentStatusID <> 10 THEN 1 ELSE 0 END AS Pending
                      FROM      (SELECT dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID, Documents.CurrentStatusID, Documents.Filename, Documents.LatestRevisionNo, dbo.VariableValue.RevisionNo, 
                                                           dbo.VariableValue.ValueText AS TextOutput, ROW_NUMBER() OVER (PARTITION BY dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID
                                         ORDER BY dbo.VariableValue.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq
                      FROM      dbo.Documents INNER JOIN
                                        dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.DocumentID INNER JOIN
                                        dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID
                      WHERE   (dbo.Documents.Deleted = 0) AND (dbo.Documents.ExtensionID = 3) AND (dbo.Documents.CurrentStatusID <> 0) AND dbo.VariableValue.VariableID IN (57, 56, 54, 49, 47, 45, 73, 76, 75, 74)) t
    WHERE  Seq = 1) doc PIVOT (Max(TextOutput) FOR VariableID IN ([57], [56], [54], [49], [47], [45], [73], [76], [75], [74])) AS pvt
    WHERE  [54] <> 'NULL')
        SELECT ALL_COMBINED.*, dbo.DocumentsInProjects.ProjectID
        FROM     dbo.DocumentsInProjects INNER JOIN
                          ALL_COMBINED ON dbo.DocumentsInProjects.DocumentID = ALL_COMBINED.DocumentID


    What I need to happen, is the table dbo.VariableValue, I need filtered down to where dbo.VariableValue.ConfigurationID=2.  This was not a selected column, so I need to add that, with the WHERE statement.  But, there is so much nesting going on, I just cant follow it well.

    So, My attempt:

    WITH ALL_COMBINED AS
    
    (SELECT TOP (100) PERCENT DocumentID, [49] AS [DwgRevision], [47] AS [Description], [45] AS [ProjectName], [54] AS [PartNumber], CurrentStatusID, Pending, LatestRevisionNo, Filename, [57] AS [Version], [56] AS [Material], 
                      [73] AS [RvTbl_Description], [76] AS [RvTbl_DwgDate], [75] AS [RvTbl_Approved], [74] AS [RvTbl_Revision]
    FROM     (SELECT DocumentID, VariableID, TextOutput, CurrentStatusID, Filename, LatestRevisionNo, CASE WHEN CurrentStatusID <> 10 THEN 1 ELSE 0 END AS Pending
                      FROM      (SELECT dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID, Documents.CurrentStatusID, Documents.Filename, Documents.LatestRevisionNo, dbo.VariableValue.RevisionNo, dbo.VariableValue.ConfigurationID, 
                                                           dbo.VariableValue.ValueText AS TextOutput, ROW_NUMBER() OVER (PARTITION BY dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID
                                       WHERE dbo.VariableValue.ConfigurationID=2  
                                       ORDER BY dbo.VariableValue.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq
                      FROM      dbo.Documents INNER JOIN
                                        dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.DocumentID INNER JOIN
                                        dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID
                      WHERE   (dbo.Documents.Deleted = 0) AND (dbo.Documents.ExtensionID = 3) AND (dbo.Documents.CurrentStatusID <> 0) AND dbo.VariableValue.VariableID IN (57, 56, 54, 49, 47, 45, 73, 76, 75, 74)) t
    WHERE  Seq = 1) doc PIVOT (Max(TextOutput) FOR VariableID IN ([57], [56], [54], [49], [47], [45], [73], [76], [75], [74])) AS pvt
    WHERE  [54] <> 'NULL')
        SELECT ALL_COMBINED.*, dbo.DocumentsInProjects.ProjectID
        FROM     dbo.DocumentsInProjects INNER JOIN
                          ALL_COMBINED ON dbo.DocumentsInProjects.DocumentID = ALL_COMBINED.DocumentID

    I also attempted this:

    WITH ALL_COMBINED AS
    
    (SELECT TOP (100) PERCENT DocumentID, [49] AS [DwgRevision], [47] AS [Description], [45] AS [ProjectName], [54] AS [PartNumber], CurrentStatusID, Pending, LatestRevisionNo, Filename, [57] AS [Version], [56] AS [Material], 
                      [73] AS [RvTbl_Description], [76] AS [RvTbl_DwgDate], [75] AS [RvTbl_Approved], [74] AS [RvTbl_Revision]
    FROM     (SELECT DocumentID, VariableID, TextOutput, CurrentStatusID, Filename, LatestRevisionNo, CASE WHEN CurrentStatusID <> 10 THEN 1 ELSE 0 END AS Pending
                      FROM      (SELECT dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID, Documents.CurrentStatusID, Documents.Filename, Documents.LatestRevisionNo, dbo.VariableValue.RevisionNo, dbo.VariableValue.ConfigurationID, 
                                                           dbo.VariableValue.ValueText FROM dbo.VariableValue WHERE dbo.VariableValue.ConfigurationID=2  AS TextOutput, ROW_NUMBER() OVER (PARTITION BY dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID
                                       
                                       ORDER BY dbo.VariableValue.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq
                      FROM      dbo.Documents INNER JOIN
                                        dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.DocumentID INNER JOIN
                                        dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID
                      WHERE   (dbo.Documents.Deleted = 0) AND (dbo.Documents.ExtensionID = 3) AND (dbo.Documents.CurrentStatusID <> 0) AND dbo.VariableValue.VariableID IN (57, 56, 54, 49, 47, 45, 73, 76, 75, 74)) t
    WHERE  Seq = 1) doc PIVOT (Max(TextOutput) FOR VariableID IN ([57], [56], [54], [49], [47], [45], [73], [76], [75], [74])) AS pvt
    WHERE  [54] <> 'NULL')
        SELECT ALL_COMBINED.*, dbo.DocumentsInProjects.ProjectID
        FROM     dbo.DocumentsInProjects INNER JOIN
                          ALL_COMBINED ON dbo.DocumentsInProjects.DocumentID = ALL_COMBINED.DocumentID

    I really do know that this is wrong, i just dont know how to get that condition fully inside before it does all of that sorting.  I feel like the latter attempt better describes what I'm trying to accomplish, but of course, I didnt do it right.

    Thanks so much!


    Friday, August 16, 2019 7:11 PM

Answers

  • Hi martirl3,

    Now I got you. You could firstly filter the table dbo.variablevalue using cte first. Try this one, and if it is not what you want, please feel free to let me know. 

    ;with cte as (
    select * from dbo.VariableValue
    where ConfigurationID=2),
    
    ALL_COMBINED AS
    
    (SELECT TOP (100) PERCENT DocumentID, [49] AS [DwgRevision], [47] AS [Description], [45] AS [ProjectName], [54] AS [PartNumber], CurrentStatusID, Pending, LatestRevisionNo, Filename, [57] AS [Version], [56] AS [Material], 
                      [73] AS [RvTbl_Description], [76] AS [RvTbl_DwgDate], [75] AS [RvTbl_Approved], [74] AS [RvTbl_Revision]
    FROM     (SELECT DocumentID, VariableID, TextOutput, CurrentStatusID, Filename, LatestRevisionNo, CASE WHEN CurrentStatusID <> 10 THEN 1 ELSE 0 END AS Pending
                      FROM      (SELECT cte.DocumentID, cte.VariableID, Documents.CurrentStatusID, Documents.Filename, Documents.LatestRevisionNo, cte.RevisionNo, 
                                                           cte.ValueText AS TextOutput, ROW_NUMBER() OVER (PARTITION BY cte.DocumentID, cte.VariableID
                                         ORDER BY cte.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq
                      FROM      dbo.Documents INNER JOIN
                                        cte ON dbo.Documents.DocumentID = cte.DocumentID INNER JOIN
                                        dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID
                      WHERE   (dbo.Documents.Deleted = 0) AND (dbo.Documents.ExtensionID = 3) AND (dbo.Documents.CurrentStatusID <> 0) AND cte.VariableID IN (57, 56, 54, 49, 47, 45, 73, 76, 75, 74)) t
    WHERE  Seq = 1) doc PIVOT (Max(TextOutput) FOR VariableID IN ([57], [56], [54], [49], [47], [45], [73], [76], [75], [74])) AS pvt
    WHERE  [54] <> 'NULL')
        SELECT ALL_COMBINED.*, dbo.DocumentsInProjects.ProjectID
        FROM     dbo.DocumentsInProjects INNER JOIN
                          ALL_COMBINED ON dbo.DocumentsInProjects.DocumentID = ALL_COMBINED.DocumentID

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by martirl3 Tuesday, August 20, 2019 2:54 PM
    Tuesday, August 20, 2019 5:45 AM
  • Ok, I think I figured this out...

    I added case.  The full statement looks like this now:

    with cte as (
    select * from dbo.VariableValue
    WHERE  (2 = CASE 
         WHEN (VariableID = 73 OR
                      VariableID = 74 OR
                      VariableID = 75 OR
                      VariableID = 76) AND ConfigurationID = 2 THEN 2
         WHEN NOT (VariableID = 73 OR
                      VariableID = 74 OR
                      VariableID = 75 OR
                      VariableID = 76) THEN 2
        END)),
    
    ALL_COMBINED AS
    
    (SELECT TOP (100) PERCENT DocumentID, [49] AS [DwgRevision], [47] AS [Description], [45] AS [ProjectName], [54] AS [PartNumber], CurrentStatusID, Pending, LatestRevisionNo, Filename, [57] AS [Version], [56] AS [Material], 
                      [73] AS [RvTbl_Description], [76] AS [RvTbl_DwgDate], [75] AS [RvTbl_Approved], [74] AS [RvTbl_Revision]
    FROM     (SELECT DocumentID, VariableID, TextOutput, CurrentStatusID, Filename, LatestRevisionNo, CASE WHEN CurrentStatusID <> 10 THEN 1 ELSE 0 END AS Pending
                      FROM      (SELECT cte.DocumentID, cte.VariableID, Documents.CurrentStatusID, Documents.Filename, Documents.LatestRevisionNo, cte.ConfigurationID, cte.RevisionNo, 
                                                           cte.ValueText AS TextOutput, ROW_NUMBER() OVER (PARTITION BY cte.DocumentID, cte.VariableID
                                         ORDER BY cte.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq
                      FROM      dbo.Documents INNER JOIN
                                        cte ON dbo.Documents.DocumentID = cte.DocumentID INNER JOIN
                                        dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID
                      WHERE   (dbo.Documents.Deleted = 0) AND (dbo.Documents.ExtensionID = 3) AND (dbo.Documents.CurrentStatusID <> 0) AND cte.VariableID IN (57, 56, 54, 49, 47, 45, 73, 76, 75, 74)) t
    WHERE  Seq = 1) doc PIVOT (Max(TextOutput) FOR VariableID IN ([57], [56], [54], [49], [47], [45], [73], [76], [75], [74])) AS pvt
    WHERE  [54] <> 'NULL')
        SELECT ALL_COMBINED.*, dbo.DocumentsInProjects.ProjectID
        FROM     dbo.DocumentsInProjects INNER JOIN
                          ALL_COMBINED ON dbo.DocumentsInProjects.DocumentID = ALL_COMBINED.DocumentID


    • Marked as answer by martirl3 Tuesday, August 20, 2019 2:54 PM
    • Edited by martirl3 Tuesday, August 20, 2019 3:22 PM
    Tuesday, August 20, 2019 2:41 PM

All replies

  • Hi martirl3,

     

    Could you post a script with CREATE TABLE + INSERT statements with sample data and the expected result of that sample data?  So that we’ll get a right direction and make some tests.

    When you post your issue, please refer POSTING TIPS - Code, Images, Hyperlinks, Details.

     

    And also, after reading your script, I suggest you use separate "cte table" instead of sub-queries which is really confused when other people are debugging. 

    Waiting for your sample data and I'm really glad to help. 

    Sabrina 

     



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 19, 2019 8:43 AM
  • (SELECT dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID, Documents.CurrentStatusID, Documents.Filename, Documents.LatestRevisionNo, dbo.VariableValue.RevisionNo, 
                                                           dbo.VariableValue.ValueText AS TextOutput, ROW_NUMBER() OVER (PARTITION BY dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID
                                         ORDER BY dbo.VariableValue.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq
     

    In this syntax, where is 'from' clause? Do you want to use 'join' or something else? 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 19, 2019 9:31 AM
  • First of all, thank you very much, @Sabrina Zhang for your replies.  

    Please forgive me that I do not understand exactly everything you are asking.  But, I think if I rephrase my question a little, and add some of my table data, it might help.  Here it goes.

    This statement as it is written works great.  No errors at all.  Its very fast, and is good.  I had a lot of help writing it, so when you ask earliery "Where is the FROM clause"... I'm not sure how to answer.

    WITH ALL_COMBINED AS

    (SELECT TOP (100) PERCENT DocumentID, [49] AS [DwgRevision], [47] AS [Description], [45] AS [ProjectName], [54] AS [PartNumber], CurrentStatusID, Pending, LatestRevisionNo, Filename, [57] AS [Version], [56] AS [Material], [73] AS [RvTbl_Description], [76] AS [RvTbl_DwgDate], [75] AS [RvTbl_Approved], [74] AS [RvTbl_Revision] FROM (SELECT DocumentID, VariableID, TextOutput, CurrentStatusID, Filename, LatestRevisionNo, CASE WHEN CurrentStatusID <> 10 THEN 1 ELSE 0 END AS Pending FROM (SELECT dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID, Documents.CurrentStatusID, Documents.Filename, Documents.LatestRevisionNo, dbo.VariableValue.RevisionNo, dbo.VariableValue.ValueText AS TextOutput, ROW_NUMBER() OVER (PARTITION BY dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID ORDER BY dbo.VariableValue.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq FROM dbo.Documents INNER JOIN dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.DocumentID INNER JOIN dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID WHERE (dbo.Documents.Deleted = 0) AND (dbo.Documents.ExtensionID = 3) AND (dbo.Documents.CurrentStatusID <> 0) AND dbo.VariableValue.VariableID IN (57, 56, 54, 49, 47, 45, 73, 76, 75, 74)) t WHERE Seq = 1) doc PIVOT (Max(TextOutput) FOR VariableID IN ([57], [56], [54], [49], [47], [45], [73], [76], [75], [74])) AS pvt WHERE [54] <> 'NULL') SELECT ALL_COMBINED.*, dbo.DocumentsInProjects.ProjectID FROM dbo.DocumentsInProjects INNER JOIN ALL_COMBINED ON dbo.DocumentsInProjects.DocumentID = ALL_COMBINED.DocumentID

    I say that is is working, but there is one exception.  The table dbo.VariableValue needs to be filtered first.  The rows need to be reduced for only rows where ConfigurationID=2, but it only apply to those values where VariableID=73, 74, 75, 76.  (The last part is new.  I studied the data some more, and I need all ConfigurationID values for other values of VariableID)

    Here is some of the data, strategically sorted, for dbo.VariableValue.  I think this is all you need to see what is going on with this statement.

    Sample data from dbo.VariableValue:

    This thread link below can be referenced for how the original statement was developed.  There may be more clues there if there is more info desired on the original statement.  I do not understand fully everything involved.  I had some very good help from @A2605

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/08cc2002-c6ea-43ae-9932-eec6d47a16da/combining-two-very-similar-views-into-a-single-view?forum=sqldatamining






    • Edited by martirl3 Monday, August 19, 2019 3:26 PM
    Monday, August 19, 2019 2:15 PM
  • Hi martirl3,

    Now I got you. You could firstly filter the table dbo.variablevalue using cte first. Try this one, and if it is not what you want, please feel free to let me know. 

    ;with cte as (
    select * from dbo.VariableValue
    where ConfigurationID=2),
    
    ALL_COMBINED AS
    
    (SELECT TOP (100) PERCENT DocumentID, [49] AS [DwgRevision], [47] AS [Description], [45] AS [ProjectName], [54] AS [PartNumber], CurrentStatusID, Pending, LatestRevisionNo, Filename, [57] AS [Version], [56] AS [Material], 
                      [73] AS [RvTbl_Description], [76] AS [RvTbl_DwgDate], [75] AS [RvTbl_Approved], [74] AS [RvTbl_Revision]
    FROM     (SELECT DocumentID, VariableID, TextOutput, CurrentStatusID, Filename, LatestRevisionNo, CASE WHEN CurrentStatusID <> 10 THEN 1 ELSE 0 END AS Pending
                      FROM      (SELECT cte.DocumentID, cte.VariableID, Documents.CurrentStatusID, Documents.Filename, Documents.LatestRevisionNo, cte.RevisionNo, 
                                                           cte.ValueText AS TextOutput, ROW_NUMBER() OVER (PARTITION BY cte.DocumentID, cte.VariableID
                                         ORDER BY cte.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq
                      FROM      dbo.Documents INNER JOIN
                                        cte ON dbo.Documents.DocumentID = cte.DocumentID INNER JOIN
                                        dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID
                      WHERE   (dbo.Documents.Deleted = 0) AND (dbo.Documents.ExtensionID = 3) AND (dbo.Documents.CurrentStatusID <> 0) AND cte.VariableID IN (57, 56, 54, 49, 47, 45, 73, 76, 75, 74)) t
    WHERE  Seq = 1) doc PIVOT (Max(TextOutput) FOR VariableID IN ([57], [56], [54], [49], [47], [45], [73], [76], [75], [74])) AS pvt
    WHERE  [54] <> 'NULL')
        SELECT ALL_COMBINED.*, dbo.DocumentsInProjects.ProjectID
        FROM     dbo.DocumentsInProjects INNER JOIN
                          ALL_COMBINED ON dbo.DocumentsInProjects.DocumentID = ALL_COMBINED.DocumentID

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by martirl3 Tuesday, August 20, 2019 2:54 PM
    Tuesday, August 20, 2019 5:45 AM
  • Hi matirl3,

    Add the condiftion in your INNER JOIN statement:

    FROM (
    ...
    
    			FROM dbo.Documents
    			INNER JOIN dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.DocumentID AND dbo.VariableValue.ConfigurationID=2
    			INNER JOIN dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID
    
    ...
    Regards,

    Tuesday, August 20, 2019 7:36 AM
  • @Sabrina Zhang,  

    Thank you so much!

    Ok, geteting really close now.  Not sure if you saw, I found out that I need to filter for ConfigurationID=2 ONLY when VariableID=73, 74, 75, or 76.  I need ALL rows without any concern of ConfigurationID for all other values of VariableID.  I think this would involve a CASE statement??

    Tuesday, August 20, 2019 1:53 PM
  • @Y4nn.M4,

    Great reply.  This would work, but I also found out later (in a later update to the post), that I need to only apply this condition for VariableID=73, 74, 75, 76.  I need all rows without respect to ConfigurationID for all other values of VariableID.

    Can this be done in your example??  Perhaps with CASE?

    Tuesday, August 20, 2019 1:55 PM
  • Ok, I think I figured this out...

    I added case.  The full statement looks like this now:

    with cte as (
    select * from dbo.VariableValue
    WHERE  (2 = CASE 
         WHEN (VariableID = 73 OR
                      VariableID = 74 OR
                      VariableID = 75 OR
                      VariableID = 76) AND ConfigurationID = 2 THEN 2
         WHEN NOT (VariableID = 73 OR
                      VariableID = 74 OR
                      VariableID = 75 OR
                      VariableID = 76) THEN 2
        END)),
    
    ALL_COMBINED AS
    
    (SELECT TOP (100) PERCENT DocumentID, [49] AS [DwgRevision], [47] AS [Description], [45] AS [ProjectName], [54] AS [PartNumber], CurrentStatusID, Pending, LatestRevisionNo, Filename, [57] AS [Version], [56] AS [Material], 
                      [73] AS [RvTbl_Description], [76] AS [RvTbl_DwgDate], [75] AS [RvTbl_Approved], [74] AS [RvTbl_Revision]
    FROM     (SELECT DocumentID, VariableID, TextOutput, CurrentStatusID, Filename, LatestRevisionNo, CASE WHEN CurrentStatusID <> 10 THEN 1 ELSE 0 END AS Pending
                      FROM      (SELECT cte.DocumentID, cte.VariableID, Documents.CurrentStatusID, Documents.Filename, Documents.LatestRevisionNo, cte.ConfigurationID, cte.RevisionNo, 
                                                           cte.ValueText AS TextOutput, ROW_NUMBER() OVER (PARTITION BY cte.DocumentID, cte.VariableID
                                         ORDER BY cte.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq
                      FROM      dbo.Documents INNER JOIN
                                        cte ON dbo.Documents.DocumentID = cte.DocumentID INNER JOIN
                                        dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID
                      WHERE   (dbo.Documents.Deleted = 0) AND (dbo.Documents.ExtensionID = 3) AND (dbo.Documents.CurrentStatusID <> 0) AND cte.VariableID IN (57, 56, 54, 49, 47, 45, 73, 76, 75, 74)) t
    WHERE  Seq = 1) doc PIVOT (Max(TextOutput) FOR VariableID IN ([57], [56], [54], [49], [47], [45], [73], [76], [75], [74])) AS pvt
    WHERE  [54] <> 'NULL')
        SELECT ALL_COMBINED.*, dbo.DocumentsInProjects.ProjectID
        FROM     dbo.DocumentsInProjects INNER JOIN
                          ALL_COMBINED ON dbo.DocumentsInProjects.DocumentID = ALL_COMBINED.DocumentID


    • Marked as answer by martirl3 Tuesday, August 20, 2019 2:54 PM
    • Edited by martirl3 Tuesday, August 20, 2019 3:22 PM
    Tuesday, August 20, 2019 2:41 PM
  • Hi martirl3,

    Perhaps with this in the inner join :

    ...
    			INNER JOIN dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.DocumentID AND ((dbo.VariableValue.ConfigurationID=2 AND dbo.VariableValue.VariableID IN (73,74,75,76)) OR (dbo.VariableValue.VariableID NOT IN (73,74,75,76)))
    ...
    regards

    Tuesday, August 20, 2019 2:52 PM
  • Y4nn.M4,

    Is there an advantage of doing it the way your suggest, as opposed to the method shown in my latest post??

    Tuesday, August 20, 2019 2:58 PM
  • For me, it's just more readeable, but in your case, i think they are not difference.

    But one difference is that a cte can not be indexed, while the inner join on the table can use indexes on this table
    Tuesday, August 20, 2019 3:13 PM
  • I am not married to either method.  I just want the one that is going to perform the best.  What is the consequence of not being indexed?  Does it matter in this case?  Are there some limitations?

    Thank you.

    Tuesday, August 20, 2019 3:20 PM
  • If you have a lot of data and, since you have restrictions on the fields cte.VariableID and join on the field cte.DocumentID, it may be better to use the indexes if they exist on these fields to improve performance.

    Regards

    Tuesday, August 20, 2019 3:45 PM