i want number of hierarchy of the highest level

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

i want number of hierarchy of the highest level

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2014-04-15 : 02:19:05

hi, I need answer of each account

how many levels of the category hierarchy each account is from the highest level.
(For example, an account of category H would be 4 levels from the highest level)

CREATE TABLE [dbo].[AccountCategory](
[AccountCategoryId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[ParentCategoryId] [int] NULL,
CONSTRAINT [PK_AccountCategory] PRIMARY KEY CLUSTERED
(
[AccountCategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[AccountCategory] ON
INSERT [dbo].[AccountCategory] ([AccountCategoryId], [Name], [ParentCategoryId]) VALUES (1, N'CatA', NULL)
INSERT [dbo].[AccountCategory] ([AccountCategoryId], [Name], [ParentCategoryId]) VALUES (2, N'CatB', NULL)
INSERT [dbo].[AccountCategory] ([AccountCategoryId], [Name], [ParentCategoryId]) VALUES (3, N'CatC', 1)
INSERT [dbo].[AccountCategory] ([AccountCategoryId], [Name], [ParentCategoryId]) VALUES (4, N'CatD', 1)
INSERT [dbo].[AccountCategory] ([AccountCategoryId], [Name], [ParentCategoryId]) VALUES (5, N'CatE', 2)
INSERT [dbo].[AccountCategory] ([AccountCategoryId], [Name], [ParentCategoryId]) VALUES (6, N'CatF', 3)
INSERT [dbo].[AccountCategory] ([AccountCategoryId], [Name], [ParentCategoryId]) VALUES (7, N'CatG', 5)
INSERT [dbo].[AccountCategory] ([AccountCategoryId], [Name], [ParentCategoryId]) VALUES (8, N'CatH', 7)
SET IDENTITY_INSERT [dbo].[AccountCategory] OFF

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-04-15 : 02:30:13

what is the expected result

Veera

asifbhura
Posting Yak Master

165 Posts

Posted - 2014-04-15 : 02:43:17

CREATE TABLE [dbo].[AccountCategoryMapping](
[AccountCategoryMappingId] [int] IDENTITY(1,1) NOT NULL,
[AccountId] [int] NOT NULL,
[AccountCategoryId] [int] NOT NULL,
CONSTRAINT [PK_AccountCategoryMapping] PRIMARY KEY CLUSTERED
(
[AccountCategoryMappingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[AccountCategoryMapping] ON
INSERT [dbo].[AccountCategoryMapping] ([AccountCategoryMappingId], [AccountId], [AccountCategoryId]) VALUES (1, 1, 2)
INSERT [dbo].[AccountCategoryMapping] ([AccountCategoryMappingId], [AccountId], [AccountCategoryId]) VALUES (2, 2, 2)
INSERT [dbo].[AccountCategoryMapping] ([AccountCategoryMappingId], [AccountId], [AccountCategoryId]) VALUES (3, 3, 1)
INSERT [dbo].[AccountCategoryMapping] ([AccountCategoryMappingId], [AccountId], [AccountCategoryId]) VALUES (4, 5, 4)
INSERT [dbo].[AccountCategoryMapping] ([AccountCategoryMappingId], [AccountId], [AccountCategoryId]) VALUES (5, 6, 5)
INSERT [dbo].[AccountCategoryMapping] ([AccountCategoryMappingId], [AccountId], [AccountCategoryId]) VALUES (6, 7, 6)
INSERT [dbo].[AccountCategoryMapping] ([AccountCategoryMappingId], [AccountId], [AccountCategoryId]) VALUES (7, 8, 5)
INSERT [dbo].[AccountCategoryMapping] ([AccountCategoryMappingId], [AccountId], [AccountCategoryId]) VALUES (8, 9, 3)
INSERT [dbo].[AccountCategoryMapping] ([AccountCategoryMappingId], [AccountId], [AccountCategoryId]) VALUES (9, 9, 7)
INSERT [dbo].[AccountCategoryMapping] ([AccountCategoryMappingId], [AccountId], [AccountCategoryId]) VALUES (10, 10, 8)
INSERT [dbo].[AccountCategoryMapping] ([AccountCategoryMappingId], [AccountId], [AccountCategoryId]) VALUES (11, 11, 5)
SET IDENTITY_INSERT [dbo].[AccountCategoryMapping] OFF

levels of the category hierarchy each account is from the highest level.
(For example, an account of category H would be 4 levels from the highest level)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources