Finding the tree structure
-
I have the following table:
SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '2' as Version, 1 as Counter, '01-10-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '1.5' as Version, 0 as Counter, '05-03-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '2.5' as Version, 1 as Counter, '05-04-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '3' as Version, 2 as Counter, '05-05-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '2' as Version, 3 as Counter, '06-01-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '1.3' as Version, 0 as Counter, '01-10-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '1.2' as Version, 1 as Counter, '03-10-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '3' as Version, 2 as Counter, '04-10-2011' as CreatedDate
I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output
Can someone please help me with this?
Thank you in advance
-
rockstar283 (6/3/2015)
I have the following table:SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '2' as Version, 1 as Counter, '01-10-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '1.5' as Version, 0 as Counter, '05-03-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '2.5' as Version, 1 as Counter, '05-04-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '3' as Version, 2 as Counter, '05-05-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '2' as Version, 3 as Counter, '06-01-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '1.3' as Version, 0 as Counter, '01-10-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '1.2' as Version, 1 as Counter, '03-10-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '3' as Version, 2 as Counter, '04-10-2011' as CreatedDate
I want to write a script where if a user enters the version number, then [font="Arial Black"]the output should show all the upward and downward nodes[/font]..e.g. if a user selects '1.2' version then following should be the output
Can someone please help me with this?
Thank you in advance
My apologies but I'm not getting it. Why don't things like A-Item1 and D-Item1.3 show up? It seems to me that A-Item1 and A-Item 1.1 is in the "upline" of A-Item1.2 and D-Item1.3 is in the "downline" of D-Item1.2.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This is just a shot at the dark here because I'm still not exactly sure what you want. The output is a little different but I built it based on what 'I' thought you wanted. Which is essentially give me all items with version 1.2 if its the current version AND if it isn't give me the versions before or after that version.
DECLARE @test-2 TABLE (Item NVARCHAR(1), [Version] NUMERIC(2,1), [Counter] INT, CreatedDate DATE)INSERT INTO @test-2
SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '2' as Version, 1 as Counter, '01-10-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '1.5' as Version, 0 as Counter, '05-03-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '2.5' as Version, 1 as Counter, '05-04-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '3' as Version, 2 as Counter, '05-05-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '2' as Version, 3 as Counter, '06-01-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '1.3' as Version, 0 as Counter, '01-10-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '1.2' as Version, 1 as Counter, '03-10-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '3' as Version, 2 as Counter, '04-10-2011' as CreatedDate
DECLARE @search NUMERIC(2,1) = 1.2
SELECT
t.*,
CASE WHEN up.Item IS NULL THEN 'CURRENT' ELSE 'UP' END AS 'UP/DOWN',
up.*
FROM
@test-2 t
OUTER APPLY (
SELECT * FROM @test-2 WHERE [Counter] = (t.Counter + 1) AND t.Item = Item
)up
WHERE
t.version = @search
UNION ALL
SELECT
t.*,
'DOWN' AS 'UP/DOWN',
dn.*
FROM
@test-2 t
CROSS APPLY (
SELECT * FROM @test-2 WHERE [Counter] = (t.Counter - 1) AND t.Item = Item AND [Version] = @search
) dn
SELECT quote FROM brain WHERE original = 1
0 rows returned -
Thank you for your efforts mate..thats the start I wanted
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply