tag:blogger.com,1999:blog-5557928922282685284.post636311331140904852..comments2023-10-19T09:47:02.908-07:00Comments on DotNetOcean: Shibashish(DotNetOcean)Anonymoushttp://www.blogger.com/profile/04730238970222809678noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-5557928922282685284.post-17726597994661995302013-04-11T04:00:04.775-07:002013-04-11T04:00:04.775-07:00SELECT * FROM [dbo].[aaaa]
ORDER BY
CASE
... SELECT * FROM [dbo].[aaaa] <br /> ORDER BY<br /> CASE<br /> WHEN ID >0 THEN (SELECT MAX(ID) FROM [dbo].[aaaa])+1<br /> ELSE 0<br /> END,<br /> ID DESCAnonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-6231173002211968702013-04-11T03:59:50.468-07:002013-04-11T03:59:50.468-07:00hihiAnonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-74985571635221601512013-02-18T22:13:23.479-08:002013-02-18T22:13:23.479-08:00CREATE TABLE Sample
(
Parent_ID INT ,
Child_ID INT...CREATE TABLE Sample<br />(<br />Parent_ID INT ,<br />Child_ID INT<br />) <br /><br />INSERT INTO Sample<br />VALUES ( 1, 2 ),<br />( 1, 3 ),<br />( 2, 4 ),<br />( 2, 5 )<br /><br />------------------------------------------------------<br />WITH Hierarchy(RootID,Level,Path,Parent_ID,Child_ID)<br />AS ( SELECT Parent_ID,<br />1,<br />'\\' + CAST(Parent_ID AS VARCHAR(MAX)) + '\' + CAST(Child_ID AS VARCHAR(MAX)),<br />Parent_ID ,<br />Child_ID<br />FROM Sample <br />WHERE NOT Parent_ID IN ( SELECT Child_ID<br />FROM Sample )<br />UNION ALL<br />SELECT P.RootID ,<br />P.[Level] + 1 ,<br />P.[Path] + '\' + CAST(C.Parent_ID AS VARCHAR(MAX)) ,<br />C.Parent_ID ,<br />C.Child_ID<br />FROM Hierarchy P<br />INNER JOIN Sample C ON P.Child_ID = C.Parent_ID<br />),<br />CTE(Parent_ID,Total_Child)<br />AS(SELECT Parent_ID,COUNT(*) FROM Hierarchy GROUP BY Parent_ID<br />)<br /><br />SELECT TOP 1 RootID,Level,Path,P.Parent_ID,Child_ID,Total_Child<br />FROM Hierarchy P<br />JOIN CTE<br />ON CTE.Parent_ID=P.Parent_ID<br />ORDER BY RootID DESC<br />------------------------------------------------------------------Anonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-88694933573350393352013-02-18T22:13:11.585-08:002013-02-18T22:13:11.585-08:00hiiiiiiiiiiiiiiihiiiiiiiiiiiiiiiAnonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-75397532162407312362013-02-18T21:51:52.448-08:002013-02-18T21:51:52.448-08:00SET @Auto=(SELECT RIGHT('0000000000'+ CONV...SET @Auto=(SELECT RIGHT('0000000000'+ CONVERT(VARCHAR(50),MAX(SUBSTRING(Variation_Code,4,10))+1),10)AS Variation_Code FROM Inventory.ItemVariationDetails)<br />SET @Auto='VAR'+ISNULL(@Auto,'0000000001')Anonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-63167499148377712762013-02-15T04:24:23.723-08:002013-02-15T04:24:23.723-08:00DECLARE @Sample TABLE
(
Parent_ID INT ,
Child_ID I...DECLARE @Sample TABLE<br />(<br />Parent_ID INT ,<br />Child_ID INT<br />) ;<br /><br />INSERT INTO @Sample<br />VALUES ( 1, 2 ),<br />( 1, 3 ),<br />( 2, 4 ),<br />( 2, 5 ) ;<br /><br />WITH Hierarchy<br />AS ( SELECT Parent_ID AS RootID ,<br />1 AS [Level] ,<br />'\\' + CAST(Parent_ID AS VARCHAR(MAX)) + '\' + CAST(Child_ID AS VARCHAR(MAX))AS [Path] ,<br />Parent_ID ,<br />Child_ID<br />FROM @Sample<br />WHERE NOT Parent_ID IN ( SELECT Child_ID<br />FROM @Sample )<br />UNION ALL<br />SELECT P.RootID ,<br />P.[Level] + 1 ,<br />P.[Path] + '\' + CAST(C.Parent_ID AS VARCHAR(MAX)) ,<br />C.Parent_ID ,<br />C.Child_ID<br />FROM Hierarchy P<br />INNER JOIN @Sample C ON P.Child_ID = C.Parent_ID<br />)<br />SELECT TOP 1 *<br />FROM Hierarchy<br />ORDER BY RootID DESC ;Anonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-12778513154664045292013-02-15T04:24:10.722-08:002013-02-15T04:24:10.722-08:00okokAnonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-48435441118318417922013-02-15T04:13:19.097-08:002013-02-15T04:13:19.097-08:00DECLARE @Sample TABLE
(
Parent_ID INT ,
Child_ID I...DECLARE @Sample TABLE<br />(<br />Parent_ID INT ,<br />Child_ID INT<br />) ;<br /><br />INSERT INTO @Sample<br />VALUES ( 1, 2 ),<br />( 1, 3 ),<br />( 2, 4 ),<br />( 2, 5 ) ;<br /><br />WITH Hierarchy<br />AS ( SELECT Parent_ID AS RootID ,<br />1 AS [Level] ,<br />'\\' + CAST(Parent_ID AS VARCHAR(MAX)) + '\' + CAST(Child_ID AS VARCHAR(MAX))AS [Path] ,<br />Parent_ID ,<br />Child_ID<br />FROM @Sample<br />WHERE NOT Parent_ID IN ( SELECT Child_ID<br />FROM @Sample )<br />UNION ALL<br />SELECT P.RootID ,<br />P.[Level] + 1 ,<br />P.[Path] + '\' + CAST(C.Parent_ID AS VARCHAR(MAX)) ,<br />C.Parent_ID ,<br />C.Child_ID<br />FROM Hierarchy P<br />INNER JOIN @Sample C ON P.Child_ID = C.Parent_ID<br />)<br />SELECT TOP 1 *<br />FROM Hierarchy<br />ORDER BY [Path] DESC ;Anonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-14425739789803630662013-02-14T22:46:37.065-08:002013-02-14T22:46:37.065-08:00DECLARE @Sample TABLE
(
Parent_ID INT ,
Ch...DECLARE @Sample TABLE<br /> (<br /> Parent_ID INT ,<br /> Child_ID INT<br /> ) ;<br /><br />INSERT INTO @Sample<br />VALUES ( 1, 2 ),<br /> ( 1, 3 ),<br /> ( 2, 4 ),<br /> ( 2, 5 ) ;<br /><br />WITH Hierarchy<br /> AS ( SELECT Parent_ID AS RootID ,<br /> 1 AS [Level] ,<br /> '\\' + CAST(Parent_ID AS VARCHAR(MAX)) + '\' + CAST(Child_ID AS VARCHAR(MAX))AS [Path] ,<br /> Parent_ID ,<br /> Child_ID<br /> FROM @Sample<br /> WHERE NOT Parent_ID IN ( SELECT Child_ID<br /> FROM @Sample )<br /> UNION ALL<br /> SELECT P.RootID ,<br /> P.[Level] + 1 ,<br /> P.[Path] + '\' + CAST(C.Parent_ID AS VARCHAR(MAX)) ,<br /> C.Parent_ID ,<br /> C.Child_ID<br /> FROM Hierarchy P<br /> INNER JOIN @Sample C ON P.Child_ID = C.Parent_ID<br /> )<br /> SELECT *<br /> FROM Hierarchy<br /> ORDER BY [Path] ;Anonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-56306763729413681602013-02-14T22:46:21.738-08:002013-02-14T22:46:21.738-08:00Hi.......12:12 PMHi.......12:12 PMAnonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-21680213898275471202013-02-14T22:43:01.849-08:002013-02-14T22:43:01.849-08:00declare @tbl_accounts table (AccountID int, name v...declare @tbl_accounts table (AccountID int, name varchar(50), ParentID int)<br /><br />insert into @tbl_accounts select 1,'Alex',0<br />insert into @tbl_accounts select 2,'John',1<br />insert into @tbl_accounts select 3,'Mathew',2<br />insert into @tbl_accounts select 4,'Philip',1<br />insert into @tbl_accounts select 5,'Shone',0<br />insert into @tbl_accounts select 6,'Shine',2<br />insert into @tbl_accounts select 7,'Tom',2<br />insert into @tbl_accounts select 8,'George',1<br />insert into @tbl_accounts select 9,'Jim',5<br /><br /><br />;with cte as<br />(<br />select <br /> Accountid, <br /> name, <br /> parentid, <br /> cast(row_number()over(partition by parentid order by name) as varchar(max)) as [path]<br />from @tbl_accounts<br />where parentid = 0<br />union all<br />select<br /> t.AccountID,<br /> t.name,<br /> t.ParentID,<br /> [path] + cast(row_number()over(partition by t.parentid order by t.name) as varchar(max))<br />from<br /> cte<br />join @tbl_accounts t on cte.AccountID = t.ParentID<br />)<br /><br />select <br /> Accountid,<br /> name,<br /> ParentID,<br /> [path]<br />from cte <br />order by pathAnonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-81252987395458862332013-02-14T05:10:46.125-08:002013-02-14T05:10:46.125-08:00it have to come from 1 to 9 but it is only showing...it have to come from 1 to 9 but it is only showing 1 to 5.keep coding:Anonymoushttps://www.blogger.com/profile/04730238970222809678noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-31955940247360498252013-02-14T05:01:44.711-08:002013-02-14T05:01:44.711-08:00hihiAnonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-87470589077041833682013-02-14T04:59:15.918-08:002013-02-14T04:59:15.918-08:00ALTER PROCEDURE [dbo].[P1]
(@ID INT
)
AS
BEGIN...ALTER PROCEDURE [dbo].[P1]<br /> (@ID INT<br /> )<br /> AS<br /> BEGIN<br /> SELECT C.Reg_ID,c.USER_NAME FROM<br /> dbo.Child c WHERE Reg_ID=@ID<br /> UNION ALL<br /> SELECT C.Reg_ID,c.USER_NAME FROM<br /> dbo.Child c WHERE Parent_ID=@ID<br /> UNION ALL<br /> SELECT C.Reg_ID,c.USER_NAME FROM<br /> dbo.Child c <br /> WHERE [Parent_ID]IN <br /> (SELECT Reg_ID FROM dbo.Child WHERE Parent_ID=@ID)<br /> UNION ALL<br /> SELECT C.Reg_ID,c.USER_NAME FROM<br /> dbo.Child c<br /> WHERE Parent_ID IN (SELECT C.Reg_ID FROM<br /> dbo.Child c <br /> WHERE [Parent_ID]IN <br /> (SELECT Reg_ID FROM dbo.Child WHERE Parent_ID=@ID))<br /> ENDAnonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-46686650522244325672013-02-14T03:50:24.903-08:002013-02-14T03:50:24.903-08:001 a 0
2 b 1
3 c 1
4 d 3
5 e 4
6 f 5
7 g 6
8 h 5
9 ...1 a 0<br />2 b 1<br />3 c 1<br />4 d 3<br />5 e 4<br />6 f 5<br />7 g 6<br />8 h 5<br />9 i 8<br />NULL NULL NULL<br /><br />By passing id as 1 it should retrive all record becz they all are related to each otherAnonymoushttps://www.blogger.com/profile/04730238970222809678noreply@blogger.comtag:blogger.com,1999:blog-5557928922282685284.post-90878188469822131742013-02-14T03:38:04.028-08:002013-02-14T03:38:04.028-08:00Reg_ID User_Name Parent_ID
1 XYZ NULL
2 X 1
3 Y 1
...Reg_ID User_Name Parent_ID<br />1 XYZ NULL<br />2 X 1<br />3 Y 1<br />4 Z 2<br />5 Z1 3<br />6 Z2 3<br />7 Y1 4<br />8 Y2 5<br /><br /><br /><br /><br />ALTER PROCEDURE [dbo].[P1]<br /> (@ID INT<br /> )<br /> AS<br /> BEGIN<br /> SELECT C.Reg_ID,c.USER_NAME FROM<br /> dbo.Child c WHERE Reg_ID=@ID<br /> UNION ALL<br /> SELECT C.Reg_ID,c.USER_NAME FROM<br /> dbo.Child c WHERE Parent_ID=@ID<br /> UNION ALL<br /> SELECT C.Reg_ID,c.USER_NAME FROM<br /> dbo.Child c <br /> WHERE [Parent_ID]IN (SELECT Reg_ID FROM dbo.Child<br /> WHERE Parent_ID =@ID)<br /> ENDAnonymoushttps://www.blogger.com/profile/01488080215380138340noreply@blogger.com