Correspond Row Data
-
Hello:
I have this query:
SELECT F.DocumentID, F.[Filename], V.VariableID, V.RevisionNo, V.ValueText
FROM MultipleApproval.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo
FROM MultipleApproval.dbo.VariableValue
) V
ON V.DocumentID = F.DocumentID
INNER JOIN (SELECT VariableID, MAX(RevisionNo) AS MaxRev
FROM MultipleApproval.dbo.VariableValue
GROUP BY VariableID) V2
ON V.VariableID = V2.VariableID AND V.RevisionNo = V2.MaxRev
WHERE V.VariableID IN (52, 53, 54, 55, 56)
AND V.ValueText = 'No'
UNION
SELECT F.DocumentID, F.[Filename], V3.VariableID, V3.RevisionNo, V3.ValueText
FROM MultipleApproval.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo
FROM MultipleApproval.dbo.VariableValue
) V3
ON V3.DocumentID = F.DocumentID
WHERE V3.VariableID IN (64, 65, 66, 67, 68)
ORDER BY DocumentID, VariableIDAnd it produces this result.
In this example, if there is a value for 52, I want to return 64... 53, 65..., 54, 66..., 55, 67... and 56, 68. In the attached picture, I do not want the value for
DocumentID 2 and 3, VariableID 67, because there is no corresponding value for 55
DocumentID 2 and 3, VariableID 68, because there is no corresponding value for 56The reason why is because the vales for VariableID's 55 and 56 are 'Yes', and also on DocumentID 2 there are no names for VariableID's 64 and 65.
Much thanks in advance for any suggestions. I am fine with using any technique that makes sense.
Steve Anderson
-
I might see a problem in your subquery for V2, I'm thinking it probably should include DocumentID in the SELECT and GROUP BY, and in the JOIN. It might make more sense to do this with a ROW_NUMBER() evaluation or do a CROSS APPLY (SELECT TOP 1...) if all you're trying to do is get the highest version number's value. It's difficult to test this out though without some scripted sample data to work with:
http://www.sqlservercentral.com/articles/Best+Practices/61537/It sounds like you need a table that has your cross linked pairs in it. Then you could reference that when you're getting the values in subquery V3, maybe something like:
CREATE TABLE #VarLink (VarID int NOT NULL, LinkVarID int NOT NULL);INSERT INTO #VarLink
VALUES (52,64), (53,65), (54,66), (55,67), (56,68); -
Hello:
Thanks for your reply.
I created the table, but I do not see it anywhere? Can you guide me to where it put it, or how to do a search for it?
Also, adding the group by and DocumentID didn't not work, but did not change the result.Steve Anderson
- stephen.aa - Monday, July 10, 2017 7:25 AM
What Chris gave you was a temporary table. If you want a persistent table, you'll need to use use a non-temporary table in your User database.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Okay, I figured the table thing out, but how does this help me? How to I harvest that data.
I realize this is confusing, or at least I am confused.
If 56 = 'No' and 68 Contains a name, I want to return the Name
If 55 = 'No' and 67 Contains a name, I want to return the Name
If 54 = 'No' and 66 Contains a name, I want to return the Name
If 53 = 'No' and 65 Contains a name, I want to return the Name
If 52 = 'No' and 64 Contains a name, I want to return the NameThank you.
Steve Anderson
-
You could use a CASE Expression:
CASE WHEN VT1.ValueText = 'No' THEN VT2.ValueText ELSE VT1.ValueText END AS ValueText
Assume VT is value form the left hand side of your Varlink table, and VT2 is the right hand side.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I had to invent my own data to try this, so it's not well tested, but maybe something like this:
WITH VarYesNo AS
(SELECT V.DocumentID, V.VariableID, V.ValueText, CAST(1 AS bigint) AS recentness
FROM MultipleApproval.dbo.VariableValue V
WHERE V.VariableID IN (52, 53, 54, 55, 56)
AND V.ValueText = 'No'
UNION ALL
SELECT V3.DocumentID, V3.VariableID, V3.ValueText, ROW_NUMBER() OVER (PARTITION BY V3.DocumentID, V3.VariableID ORDER BY V3.RevisionNo DESC) AS recentness
FROM VarYesNo V2
INNER JOIN VarLink VL ON V2.VariableID = VL.VarID
INNER JOIN MultipleApproval.dbo.VariableValue V3 ON V2.DocumentID = V3.DocumentID AND VL.LinkVarID = V3.VariableID)
SELECT F.DocumentID, F.[Filename], RV.VariableID, RV.RevisionNo, RV.ValueText
FROM MultipleApproval.dbo.Documents F
INNER JOIN VarYesNo RV ON F.DocumentID = RV.DocumentID
WHERE RV.recentness = 1
ORDER BY RV.DocumentID, RV.VariableID; -
It seems to work, except for RV.Revision No.
Don't I need to somehow include this. You have a column called recentness with a descending value for RevisionNo, so it seems.In my initial code, I believe this is why there were multiple INNER JOINS on the same table, to cover isolating the MAX RevisionNo. Of course this did not work.
In this last group, there is no RV.Revision NoSteve Anderson
-
If RV.RevisionNo is needed in the results, just include it in both halves of the UNION ALL query in the CTE. I didn't have a dbo.Documents table in the testing data I made up so I didn't test that part of this properly:
WITH VarYesNo AS
(SELECT V.DocumentID, V.VariableID, V.ValueText, V.RevisionNo, CAST(1 AS bigint) AS recentness
FROM MultipleApproval.dbo.VariableValue V
WHERE V.VariableID IN (52, 53, 54, 55, 56)
AND V.ValueText = 'No'
UNION ALL
SELECT V3.DocumentID, V3.VariableID, V3.ValueText, V3.RevisionNo, ROW_NUMBER() OVER (PARTITION BY V3.DocumentID, V3.VariableID ORDER BY V3.RevisionNo DESC) AS recentness
FROM VarYesNo V2
INNER JOIN VarLink VL ON V2.VariableID = VL.VarID
INNER JOIN MultipleApproval.dbo.VariableValue V3 ON V2.DocumentID = V3.DocumentID AND VL.LinkVarID = V3.VariableID)
SELECT F.DocumentID, F.[Filename], RV.VariableID, RV.RevisionNo, RV.ValueText
FROM MultipleApproval.dbo.Documents F
INNER JOIN VarYesNo RV ON F.DocumentID = RV.DocumentID
WHERE RV.recentness = 1
ORDER BY RV.DocumentID, RV.VariableID; -
Hello:
My dilemma with this is that the last solution is no different than my first, except that it returns more data. My goal is to return less data here. I'm sorry it is so confusing, or that I have apparently not included enough information.
There are two tables:
The Documents Table:And the Variable Value Table:
When the MaxRev Value for 52 has a ValueText of 'No', I want to return the ValueText of 64, for each document, otherwise nothing
When the MaxRev Value for 53 has a ValueText of 'No', I want to return the ValueText of 65, for each document, otherwise nothing
When the MaxRev Value for 54 has a ValueText of 'No', I want to return the ValueText of 66, for each document, otherwise nothing
When the MaxRev Value for 55 has a ValueText of 'No', I want to return the ValueText of 67, for each document, otherwise nothing
When the MaxRev Value for 56 has a ValueText of 'No', I want to return the ValueText of 68, for each document, otherwise nothingIn my original post, it was returning all the 'No' values fine, but all of the corresponding names. Also, if no corresponding name exists, I do not need to return the 'No',
I really do not need to return the ValueText for 52, 53, 54, 55 or 56 ('No Values) at all.This has been quite an educational post, and again my apology for not being clear.
Thanks.
Steve Anderson
-
OK, given these requirements, and knowing that there are potentially multiple versions of the Yes/No variables as well, maybe something like this:
SELECT DocumentID, VariableID, ValueText, RevisionNo
INTO #LastRev
FROM (SELECT DocumentID, VariableID, ValueText, RevisionNo, ROW_NUMBER() OVER (PARTITION BY DocumentID, VariableID ORDER BY RevisionNo DESC) AS recentness
FROM MultipleApproval.dbo.#VariableValue) V
WHERE V.recentness = 1;SELECT V2.DocumentID, ISNULL(V3.VariableID, V2.VariableID) AS VariableID, ISNULL(V3.ValueText, V2.ValueText) AS ValueText, ISNULL(V3.RevisionNo, V2.RevisionNo) AS RevisionNo
FROM #LastRev V2
LEFT OUTER JOIN VarLink VL ON V2.VariableID = VL.VarID AND V2.ValueText = 'no'
LEFT OUTER JOIN #LastRev V3 ON V2.DocumentID = V3.DocumentID AND VL.LinkVarID = V3.VariableID
WHERE V2.VariableID NOT IN (64,65,66,67,68);I hope this works better for you, I guess these are the problems when we don't know all the data and specifications.
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Chris:
Much thanks for your patience. I have read a few books and just do not see this. Can you tell me a good reference to purchase for the future?
Below is the final result of the query:DROP TABLE #LastRev;DROP TABLE #VarLink;
CREATE TABLE #VarLink (VarID int NOT NULL, LinkVarID int NOT NULL);
INSERT INTO #VarLink
VALUES (52,64), (53,65), (54,66), (55,67), (56,68);SELECT F.DocumentID, F.[Filename], V.VariableID, V. ValueText, V.RevisionNo
INTO #LastRev
FROM MultipleApproval.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo, ROW_NUMBER() OVER (PARTITION BY DocumentID, VariableID ORDER BY RevisionNo DESC) AS recentness
FROM MultipleApproval.dbo.VariableValue) V
ON V.DocumentID = F.DocumentID
WHERE V.recentness = 1SELECT V2.DocumentID, V2.[Filename], ISNULL(V3.VariableID, V2.VariableID) AS VariableID, ISNULL(V3.ValueText, V2.ValueText) AS ValueText, ISNULL(V3.RevisionNo, V2.RevisionNo) AS RevisionNo, 'No' AS Approved
FROM #LastRev V2
LEFT OUTER JOIN #VarLink VL ON V2.VariableID = VL.VarID AND V2.ValueText = 'No'
LEFT OUTER JOIN #LastRev V3 ON V2.DocumentID = V3.DocumentID AND VL.LinkVarID = V3.VariableID
WHERE V2.VariableID NOT IN (64,65,66,67,68)
AND V3.VariableID IN (64,65,66,67,68);Steve Anderson
-
I'm not sure I understand why you added the line:
AND V3.VariableID IN (64,65,66,67,68);
since V3 is OUTER JOINed, this condition in the WHERE clause would essentially turn it back into an INNER JOIN. I'll try to explain my code, but I'm thinking I still don't quite understand the requirements and expected results. This is a complicated request though.#VarLink table is designed to associate the Yes/No variable rows (52,53,54,55,56) with their corresponding text rows (64,65,66,67,68) so we can do this as a set based operation.
#LastRev table is designed to make sure we're only looking at the most recent version of each variable for each document. The ROW_NUMBER() windowed function sorts the data and numbers the rows, sorting by RevisionNo descending allows us to more easily select all the rows that have a "recentness" value of 1, meaning the last revision for each variable on each document.
The last query takes all the variables except the linked ones (64,65,66,67,68) and does an OUTER JOIN to #VarLink. The OUTER JOIN will allow us to return the ValueText of the Yes/No variables, or the ValueText of the linked variables based on the ISNULL conditions.
-
Chris:
This last post works great! I just would not mind having a textbook that goes into these things a little deeper. Do you know if one exists?
Steve Anderson
-
Well I didn't really gain this level of knowledge just from books, it's more a mix of techniques from various resources and years of doing some trial and error experimentation on some sandbox type databases.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply