Wednesday, September 21

Shibashish(DotNetOcean)

Welcome to shibashish's blog.
Lord shiva always bless you.
Posted by at
Labels:

16 comments:

  1. Unknown14 February 2013 at 03:38

    Reg_ID User_Name Parent_ID
    1 XYZ NULL
    2 X 1
    3 Y 1
    4 Z 2
    5 Z1 3
    6 Z2 3
    7 Y1 4
    8 Y2 5




    ALTER PROCEDURE [dbo].[P1]
    (@ID INT
    )
    AS
    BEGIN
    SELECT C.Reg_ID,c.USER_NAME FROM
    dbo.Child c WHERE Reg_ID=@ID
    UNION ALL
    SELECT C.Reg_ID,c.USER_NAME FROM
    dbo.Child c WHERE Parent_ID=@ID
    UNION ALL
    SELECT C.Reg_ID,c.USER_NAME FROM
    dbo.Child c
    WHERE [Parent_ID]IN (SELECT Reg_ID FROM dbo.Child
    WHERE Parent_ID =@ID)
    END

    ReplyDelete
    Replies
    1. Unknown14 February 2013 at 03:50

      1 a 0
      2 b 1
      3 c 1
      4 d 3
      5 e 4
      6 f 5
      7 g 6
      8 h 5
      9 i 8
      NULL NULL NULL

      By passing id as 1 it should retrive all record becz they all are related to each other

      Delete
  2. Unknown14 February 2013 at 04:59

    ALTER PROCEDURE [dbo].[P1]
    (@ID INT
    )
    AS
    BEGIN
    SELECT C.Reg_ID,c.USER_NAME FROM
    dbo.Child c WHERE Reg_ID=@ID
    UNION ALL
    SELECT C.Reg_ID,c.USER_NAME FROM
    dbo.Child c WHERE Parent_ID=@ID
    UNION ALL
    SELECT C.Reg_ID,c.USER_NAME FROM
    dbo.Child c
    WHERE [Parent_ID]IN
    (SELECT Reg_ID FROM dbo.Child WHERE Parent_ID=@ID)
    UNION ALL
    SELECT C.Reg_ID,c.USER_NAME FROM
    dbo.Child c
    WHERE Parent_ID IN (SELECT C.Reg_ID FROM
    dbo.Child c
    WHERE [Parent_ID]IN
    (SELECT Reg_ID FROM dbo.Child WHERE Parent_ID=@ID))
    END

    ReplyDelete
  3. Unknown14 February 2013 at 22:43

    declare @tbl_accounts table (AccountID int, name varchar(50), ParentID int)

    insert into @tbl_accounts select 1,'Alex',0
    insert into @tbl_accounts select 2,'John',1
    insert into @tbl_accounts select 3,'Mathew',2
    insert into @tbl_accounts select 4,'Philip',1
    insert into @tbl_accounts select 5,'Shone',0
    insert into @tbl_accounts select 6,'Shine',2
    insert into @tbl_accounts select 7,'Tom',2
    insert into @tbl_accounts select 8,'George',1
    insert into @tbl_accounts select 9,'Jim',5


    ;with cte as
    (
    select
    Accountid,
    name,
    parentid,
    cast(row_number()over(partition by parentid order by name) as varchar(max)) as [path]
    from @tbl_accounts
    where parentid = 0
    union all
    select
    t.AccountID,
    t.name,
    t.ParentID,
    [path] + cast(row_number()over(partition by t.parentid order by t.name) as varchar(max))
    from
    cte
    join @tbl_accounts t on cte.AccountID = t.ParentID
    )

    select
    Accountid,
    name,
    ParentID,
    [path]
    from cte
    order by path

    ReplyDelete
  4. Unknown14 February 2013 at 22:46

    Hi.......12:12 PM

    ReplyDelete
  5. Unknown14 February 2013 at 22:46

    DECLARE @Sample TABLE
    (
    Parent_ID INT ,
    Child_ID INT
    ) ;

    INSERT INTO @Sample
    VALUES ( 1, 2 ),
    ( 1, 3 ),
    ( 2, 4 ),
    ( 2, 5 ) ;

    WITH Hierarchy
    AS ( SELECT Parent_ID AS RootID ,
    1 AS [Level] ,
    '\\' + CAST(Parent_ID AS VARCHAR(MAX)) + '\' + CAST(Child_ID AS VARCHAR(MAX))AS [Path] ,
    Parent_ID ,
    Child_ID
    FROM @Sample
    WHERE NOT Parent_ID IN ( SELECT Child_ID
    FROM @Sample )
    UNION ALL
    SELECT P.RootID ,
    P.[Level] + 1 ,
    P.[Path] + '\' + CAST(C.Parent_ID AS VARCHAR(MAX)) ,
    C.Parent_ID ,
    C.Child_ID
    FROM Hierarchy P
    INNER JOIN @Sample C ON P.Child_ID = C.Parent_ID
    )
    SELECT *
    FROM Hierarchy
    ORDER BY [Path] ;

    ReplyDelete
  6. Unknown15 February 2013 at 04:13

    DECLARE @Sample TABLE
    (
    Parent_ID INT ,
    Child_ID INT
    ) ;

    INSERT INTO @Sample
    VALUES ( 1, 2 ),
    ( 1, 3 ),
    ( 2, 4 ),
    ( 2, 5 ) ;

    WITH Hierarchy
    AS ( SELECT Parent_ID AS RootID ,
    1 AS [Level] ,
    '\\' + CAST(Parent_ID AS VARCHAR(MAX)) + '\' + CAST(Child_ID AS VARCHAR(MAX))AS [Path] ,
    Parent_ID ,
    Child_ID
    FROM @Sample
    WHERE NOT Parent_ID IN ( SELECT Child_ID
    FROM @Sample )
    UNION ALL
    SELECT P.RootID ,
    P.[Level] + 1 ,
    P.[Path] + '\' + CAST(C.Parent_ID AS VARCHAR(MAX)) ,
    C.Parent_ID ,
    C.Child_ID
    FROM Hierarchy P
    INNER JOIN @Sample C ON P.Child_ID = C.Parent_ID
    )
    SELECT TOP 1 *
    FROM Hierarchy
    ORDER BY [Path] DESC ;

    ReplyDelete
  7. Unknown15 February 2013 at 04:24

    DECLARE @Sample TABLE
    (
    Parent_ID INT ,
    Child_ID INT
    ) ;

    INSERT INTO @Sample
    VALUES ( 1, 2 ),
    ( 1, 3 ),
    ( 2, 4 ),
    ( 2, 5 ) ;

    WITH Hierarchy
    AS ( SELECT Parent_ID AS RootID ,
    1 AS [Level] ,
    '\\' + CAST(Parent_ID AS VARCHAR(MAX)) + '\' + CAST(Child_ID AS VARCHAR(MAX))AS [Path] ,
    Parent_ID ,
    Child_ID
    FROM @Sample
    WHERE NOT Parent_ID IN ( SELECT Child_ID
    FROM @Sample )
    UNION ALL
    SELECT P.RootID ,
    P.[Level] + 1 ,
    P.[Path] + '\' + CAST(C.Parent_ID AS VARCHAR(MAX)) ,
    C.Parent_ID ,
    C.Child_ID
    FROM Hierarchy P
    INNER JOIN @Sample C ON P.Child_ID = C.Parent_ID
    )
    SELECT TOP 1 *
    FROM Hierarchy
    ORDER BY RootID DESC ;

    ReplyDelete
  8. Unknown18 February 2013 at 21:51

    SET @Auto=(SELECT RIGHT('0000000000'+ CONVERT(VARCHAR(50),MAX(SUBSTRING(Variation_Code,4,10))+1),10)AS Variation_Code FROM Inventory.ItemVariationDetails)
    SET @Auto='VAR'+ISNULL(@Auto,'0000000001')

    ReplyDelete
  9. Unknown18 February 2013 at 22:13

    CREATE TABLE Sample
    (
    Parent_ID INT ,
    Child_ID INT
    )

    INSERT INTO Sample
    VALUES ( 1, 2 ),
    ( 1, 3 ),
    ( 2, 4 ),
    ( 2, 5 )

    ------------------------------------------------------
    WITH Hierarchy(RootID,Level,Path,Parent_ID,Child_ID)
    AS ( SELECT Parent_ID,
    1,
    '\\' + CAST(Parent_ID AS VARCHAR(MAX)) + '\' + CAST(Child_ID AS VARCHAR(MAX)),
    Parent_ID ,
    Child_ID
    FROM Sample
    WHERE NOT Parent_ID IN ( SELECT Child_ID
    FROM Sample )
    UNION ALL
    SELECT P.RootID ,
    P.[Level] + 1 ,
    P.[Path] + '\' + CAST(C.Parent_ID AS VARCHAR(MAX)) ,
    C.Parent_ID ,
    C.Child_ID
    FROM Hierarchy P
    INNER JOIN Sample C ON P.Child_ID = C.Parent_ID
    ),
    CTE(Parent_ID,Total_Child)
    AS(SELECT Parent_ID,COUNT(*) FROM Hierarchy GROUP BY Parent_ID
    )

    SELECT TOP 1 RootID,Level,Path,P.Parent_ID,Child_ID,Total_Child
    FROM Hierarchy P
    JOIN CTE
    ON CTE.Parent_ID=P.Parent_ID
    ORDER BY RootID DESC
    ------------------------------------------------------------------

    ReplyDelete
  10. Unknown11 April 2013 at 03:59

    hi

    ReplyDelete
  11. Unknown11 April 2013 at 04:00

    SELECT * FROM [dbo].[aaaa]
    ORDER BY
    CASE
    WHEN ID >0 THEN (SELECT MAX(ID) FROM [dbo].[aaaa])+1
    ELSE 0
    END,
    ID DESC

    ReplyDelete

Please don't spam, spam comments is not allowed here.

Subscribe to: Post Comments (Atom)

.

.
ShibashishMnty
shibashish mohanty