Want Sorting inside of CTS

Question

Post reply

Want Sorting inside of CTS

dilip.aim11

Mr or Mrs. 500

Points: 574
More actions
July 10, 2012 at 1:29 am

#263497

hi ..
I have Query For Get Hierarchy Data Using CTS...
Its Working Fine
There Is Three Step
1 - G
2 - S
3 - A
-----------------------------------
Now i want My 2 Level Should be in sort order which i given in one column but ..
i am not getting that please Help me..
this is my table and data --
CREATE TABLE [dbo].[My_Test_Temp](
[AccountID] [bigint] IDENTITY(1,1) NOT NULL,
[CategoryID] [bigint] NULL,
[RootID] [bigint] NULL,
[TRNType] [varchar](1) NULL,
[AcType] [varchar](2) NULL,
[AcName] [nvarchar](max) NULL,
[CrDr] [varchar](2) NULL,
[Active] [int] NULL,
[Sort_Order] [int] NULL,
[HasParent] [varchar](1) NULL,
[Descr] [ntext] NULL,
[account_Code] [varchar](5) NULL,
[IsTaxExempt] [bit] NULL,
[PostingType] [varchar](50) NULL,
[isReconcile] [bit] NULL,
[Bank_Name] [varchar](150) NULL,
[Sort_Code] [varchar](50) NULL,
[Account_number] [varchar](150) NULL,
[company_ID] [int] NULL,
[Default_DPID] [int] NULL,
[Default_TaxType] [int] NULL,
[Default_TaxID] [int] NULL,
[URN] [nvarchar](max) NULL,
[LAST_Updated] [datetime] NULL,
[new_Status] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[My_Test_Temp] ON
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (351, 0, 351, N'G', N'PL', N'Expenditure', N'0', 1, 5, N'N', N' ', N'AC85', 0, N'NULL', 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (352, 351, 351, N'S', N'PL', N'Cost Of Sales', N'0', 1, 1, N'Y', N' ', N'AC86', 0, N'NULL', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, CAST(0x0000A089014F4A94 AS DateTime), NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (353, 352, 351, N'A', N'PL', N'Goods - for resale', N'0', 1, 1, N'Y', NULL, N'AC87', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (354, 351, 351, N'S', N'PL', N'Overheads', N'0', 1, 2, N'Y', N' ', N'AC88', 0, N'NULL', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, CAST(0x0000A089014ED467 AS DateTime), NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (355, 354, 351, N'A', N'PL', N'Director''s Salaries', N'0', 1, 1, N'Y', NULL, N'AC89', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (356, 354, 351, N'A', N'PL', N'Gross Wages', N'0', 1, 1, N'Y', NULL, N'AC90', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (357, 354, 351, N'A', N'PL', N'Employers N.I.', N'0', 1, 1, N'Y', NULL, N'AC91', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (358, 354, 351, N'A', N'PL', N'Post Office Locum', N'0', 1, 1, N'Y', NULL, N'AC92', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (359, 354, 351, N'A', N'PL', N'Rent', N'0', 1, 1, N'Y', NULL, N'AC93', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (360, 354, 351, N'A', N'PL', N'Rates - Water', N'0', 1, 1, N'Y', NULL, N'AC94', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (361, 354, 351, N'A', N'PL', N'Rates - Council', N'0', 1, 1, N'Y', NULL, N'AC95', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (362, 354, 351, N'A', N'PL', N'Insurance', N'0', 1, 1, N'Y', NULL, N'AC96', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (363, 354, 351, N'A', N'PL', N'Heat & Light', N'0', 1, 1, N'Y', NULL, N'AC97', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (364, 354, 351, N'A', N'PL', N'Telephone', N'0', 1, 1, N'Y', NULL, N'AC98', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (365, 354, 351, N'A', N'PL', N'Stationery', N'0', 1, 1, N'Y', NULL, N'AC99', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (366, 354, 351, N'A', N'PL', N'Advertising', N'0', 1, 1, N'Y', NULL, N'AC100', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (367, 354, 351, N'A', N'PL', N'Travel & Subsistence', N'0', 1, 1, N'Y', NULL, N'AC101', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (368, 354, 351, N'A', N'PL', N'Motor Expenses', N'0', 1, 1, N'Y', NULL, N'AC102', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (369, 354, 351, N'A', N'PL', N'Licenses, Donations and Subscriptions', N'0', 1, 1, N'Y', NULL, N'AC103', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (370, 354, 351, N'A', N'PL', N'Repairs and Renewals', N'0', 1, 1, N'Y', NULL, N'AC104', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (371, 354, 351, N'A', N'PL', N'Staff Welfare & Cleaning', N'0', 1, 1, N'Y', NULL, N'AC105', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (372, 354, 351, N'A', N'PL', N'Accountancy', N'0', 1, 1, N'Y', NULL, N'AC106', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (373, 354, 351, N'A', N'PL', N'Bookkeeping', N'0', 1, 1, N'Y', NULL, N'AC107', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (374, 354, 351, N'A', N'PL', N'Legal & Professional', N'0', 1, 1, N'Y', NULL, N'AC108', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (375, 354, 351, N'A', N'PL', N'Penalties & Charges', N'0', 1, 1, N'Y', NULL, N'AC109', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (376, 354, 351, N'A', N'PL', N'Entertainment', N'0', 1, 1, N'Y', NULL, N'AC110', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (377, 354, 351, N'A', N'PL', N'Depreciation', N'0', 0, 1, N'Y', N'', N'AC111', 1, NULL, 0, N'null', N'null', N'null', 2, 4, 0, 31, NULL, CAST(0x0000A0890134F194 AS DateTime), NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (378, 393, 351, N'A', N'PL', N'Bank Charges & Interest', N'0', 1, 1, N'Y', N'', N'AC112', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 4, 0, 31, NULL, CAST(0x0000A08901452F3B AS DateTime), NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (379, 393, 351, N'A', N'PL', N'Bank Loan Interest', N'0', 1, 1, N'Y', N'', N'AC113', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 4, 0, 31, NULL, CAST(0x0000A08901454457 AS DateTime), NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (380, 351, 351, N'S', N'PL', N'Suspense & Mispostings', N'0', 1, 3, N'Y', N' ', N'AC114', 0, N'NULL', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, CAST(0x0000A089014F6CEC AS DateTime), NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (381, 380, 351, N'A', N'PL', N'Suspense & Mispostings', N'0', 1, 1, N'Y', NULL, N'AC115', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (383, 354, 351, N'A', N'PL', N'Asset Disposal', N'0', 1, 1, N'Y', NULL, N'DS123', 0, N'DESP', 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (384, 354, 351, N'A', N'PL', N'Sundry Expenses', N'0', 1, 1, N'Y', NULL, N'AC115', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (385, 354, 351, N'A', N'PL', N'Delivery Charges', N'0', 1, 1, N'Y', NULL, N'AC115', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (387, 351, 351, N'S', N'PL', N'Depreciation', N'0', 1, 4, N'Y', N'', NULL, 0, N'null', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, CAST(0x0000A089014F7C6B AS DateTime), NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (388, 387, 351, N'A', N'PL', N'Goodwill Amortisation', N'0', 1, 1, N'Y', N'', N'PL123', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (389, 387, 351, N'A', N'PL', N'Computer Equipment Depreciation', N'0', 1, 1, N'Y', N'', N'PL123', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (390, 387, 351, N'A', N'PL', N'Fixtures and Fittings Depreciation', N'0', 1, 1, N'Y', N'', N'PL123', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (391, 387, 351, N'A', N'PL', N'Computer Software Depreciation', N'0', 1, 1, N'Y', N'', N'PL123', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, NULL, NULL)
INSERT [dbo].[My_Test_Temp] ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (393, 351, 351, N'S', N'PL', N'Bank Charges & Interest', NULL, 1, 5, N'Y', N'For Bank Charges and interst', NULL, 0, N'null', 0, N'null', N'null', N'null', 2, 0, 0, 0, N'0011-002-09072012-074225-ANA', CAST(0x0000A089014F24CD AS DateTime), N'none')
SET IDENTITY_INSERT [dbo].[My_Test_Temp] OFF
and this is my current query
eclare @Root int;
declare @company_ID int;
set @company_ID=2;
set @Root=351;
with cteDemo as
(
select [d].[AccountID]
, [d].[RootId]
, [d].[CategoryID]
, [d].[TRNType]
, [d].[AcType]
, [d].[AcName]
, convert(varchar(500), [d].[TRNType] + '-' + [d].[AcName]) as [sort_column]
, 4 as [level]
,d.Sort_Order
from Tbl_NominalAC as d
where [d].[CategoryID] = 0 and [d].Active=1
union all
select [cd].[AccountID]
, [cd].[RootId]
, [cd].[CategoryID]
, [cd].[TRNType]
, [cd].[AcType]
, [cd].[AcName]
, convert(varchar(500), [pd].[sort_column] + '-' + case [cd].[TRNType] when 'S' then '2S' when 'A' then '1A' end + '-' + [cd].[AcName])
, 4 + [pd].[level]
,cd.Sort_Order
from Tbl_NominalAC as cd
inner join [cteDemo] as pd on [cd].[CategoryID] = [pd].[AccountID]
where [cd].Active=1
)
select [cteDemo].[AccountID]
, [cteDemo].[AcName]
, replicate('-', [cteDemo].[level]) + [cteDemo].[AcName] + '--' as [graph]
, [cteDemo].[TRNType]
, [cteDemo].[AcType]
,[cteDemo].[RootId]
,[cteDemo].[CategoryID]
,Sort_Order,sort_column,level
from [cteDemo]
where [cteDemo].[RootId] = @Root
order by [cteDemo].[sort_column] ;
i want order like this
Expenditure
Cost Of Sales
all acount
Overheads
all acount
Suspense & Mispostings
all acount
Depreciation
all acount
Bank Charges & Interest
all acount

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

dilip.aim11 Mr or Mrs. 500 Points: 574 More actions · Answer 1

i done it ...

just a small change

here

, convert(varchar(500), [pd].[sort_column] + '-' + case [cd].[TRNType] when 'S' then convert(varchar,cd.Sort_Order)+ '2S' when 'A' then '1A' end + '-' + [cd].[AcName])

subbu1 Hall of Fame Points: 3281 More actions · Answer 2

pl try below code..

declare @My_Test_Temp table(

[AccountID] [bigint] NULL,

[CategoryID] [bigint] NULL,

[RootID] [bigint] NULL,

[TRNType] [varchar](1) NULL,

[AcType] [varchar](2) NULL,

[AcName] [nvarchar](max) NULL,

[CrDr] [varchar](2) NULL,

[Active] [int] NULL,

[Sort_Order] [int] NULL,

[HasParent] [varchar](1) NULL,

[Descr] [ntext] NULL,

[account_Code] [varchar](5) NULL,

[IsTaxExempt] [bit] NULL,

[PostingType] [varchar](50) NULL,

[isReconcile] [bit] NULL,

[Bank_Name] [varchar](150) NULL,

[Sort_Code] [varchar](50) NULL,

[Account_number] [varchar](150) NULL,

[company_ID] [int] NULL,

[Default_DPID] [int] NULL,

[Default_TaxType] [int] NULL,

[Default_TaxID] [int] NULL,

[URN] [nvarchar](max) NULL,

[LAST_Updated] [datetime] NULL,

[new_Status] [nvarchar](50) NULL

)

--SET IDENTITY_INSERT [dbo].[My_Test_Temp] ON

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (351, 0, 351, N'G', N'PL', N'Expenditure', N'0', 1, 5, N'N', N' ', N'AC85', 0, N'NULL', 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (352, 351, 351, N'S', N'PL', N'Cost Of Sales', N'0', 1, 1, N'Y', N' ', N'AC86', 0, N'NULL', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, CAST(0x0000A089014F4A94 AS DateTime), NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (353, 352, 351, N'A', N'PL', N'Goods - for resale', N'0', 1, 1, N'Y', NULL, N'AC87', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (354, 351, 351, N'S', N'PL', N'Overheads', N'0', 1, 2, N'Y', N' ', N'AC88', 0, N'NULL', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, CAST(0x0000A089014ED467 AS DateTime), NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (355, 354, 351, N'A', N'PL', N'Director''s Salaries', N'0', 1, 1, N'Y', NULL, N'AC89', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (356, 354, 351, N'A', N'PL', N'Gross Wages', N'0', 1, 1, N'Y', NULL, N'AC90', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (357, 354, 351, N'A', N'PL', N'Employers N.I.', N'0', 1, 1, N'Y', NULL, N'AC91', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (358, 354, 351, N'A', N'PL', N'Post Office Locum', N'0', 1, 1, N'Y', NULL, N'AC92', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (359, 354, 351, N'A', N'PL', N'Rent', N'0', 1, 1, N'Y', NULL, N'AC93', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (360, 354, 351, N'A', N'PL', N'Rates - Water', N'0', 1, 1, N'Y', NULL, N'AC94', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (361, 354, 351, N'A', N'PL', N'Rates - Council', N'0', 1, 1, N'Y', NULL, N'AC95', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (362, 354, 351, N'A', N'PL', N'Insurance', N'0', 1, 1, N'Y', NULL, N'AC96', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (363, 354, 351, N'A', N'PL', N'Heat & Light', N'0', 1, 1, N'Y', NULL, N'AC97', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (364, 354, 351, N'A', N'PL', N'Telephone', N'0', 1, 1, N'Y', NULL, N'AC98', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (365, 354, 351, N'A', N'PL', N'Stationery', N'0', 1, 1, N'Y', NULL, N'AC99', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (366, 354, 351, N'A', N'PL', N'Advertising', N'0', 1, 1, N'Y', NULL, N'AC100', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (367, 354, 351, N'A', N'PL', N'Travel & Subsistence', N'0', 1, 1, N'Y', NULL, N'AC101', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (368, 354, 351, N'A', N'PL', N'Motor Expenses', N'0', 1, 1, N'Y', NULL, N'AC102', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (369, 354, 351, N'A', N'PL', N'Licenses, Donations and Subscriptions', N'0', 1, 1, N'Y', NULL, N'AC103', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (370, 354, 351, N'A', N'PL', N'Repairs and Renewals', N'0', 1, 1, N'Y', NULL, N'AC104', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (371, 354, 351, N'A', N'PL', N'Staff Welfare & Cleaning', N'0', 1, 1, N'Y', NULL, N'AC105', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (372, 354, 351, N'A', N'PL', N'Accountancy', N'0', 1, 1, N'Y', NULL, N'AC106', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (373, 354, 351, N'A', N'PL', N'Bookkeeping', N'0', 1, 1, N'Y', NULL, N'AC107', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (374, 354, 351, N'A', N'PL', N'Legal & Professional', N'0', 1, 1, N'Y', NULL, N'AC108', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (375, 354, 351, N'A', N'PL', N'Penalties & Charges', N'0', 1, 1, N'Y', NULL, N'AC109', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (376, 354, 351, N'A', N'PL', N'Entertainment', N'0', 1, 1, N'Y', NULL, N'AC110', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (377, 354, 351, N'A', N'PL', N'Depreciation', N'0', 0, 1, N'Y', N'', N'AC111', 1, NULL, 0, N'null', N'null', N'null', 2, 4, 0, 31, NULL, CAST(0x0000A0890134F194 AS DateTime), NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (378, 393, 351, N'A', N'PL', N'Bank Charges & Interest', N'0', 1, 1, N'Y', N'', N'AC112', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 4, 0, 31, NULL, CAST(0x0000A08901452F3B AS DateTime), NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (379, 393, 351, N'A', N'PL', N'Bank Loan Interest', N'0', 1, 1, N'Y', N'', N'AC113', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 4, 0, 31, NULL, CAST(0x0000A08901454457 AS DateTime), NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (380, 351, 351, N'S', N'PL', N'Suspense & Mispostings', N'0', 1, 3, N'Y', N' ', N'AC114', 0, N'NULL', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, CAST(0x0000A089014F6CEC AS DateTime), NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (381, 380, 351, N'A', N'PL', N'Suspense & Mispostings', N'0', 1, 1, N'Y', NULL, N'AC115', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (383, 354, 351, N'A', N'PL', N'Asset Disposal', N'0', 1, 1, N'Y', NULL, N'DS123', 0, N'DESP', 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (384, 354, 351, N'A', N'PL', N'Sundry Expenses', N'0', 1, 1, N'Y', NULL, N'AC115', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (385, 354, 351, N'A', N'PL', N'Delivery Charges', N'0', 1, 1, N'Y', NULL, N'AC115', 0, NULL, 0, N'NULL', N'NULL', N'NULL', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (387, 351, 351, N'S', N'PL', N'Depreciation', N'0', 1, 4, N'Y', N'', NULL, 0, N'null', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, CAST(0x0000A089014F7C6B AS DateTime), NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (388, 387, 351, N'A', N'PL', N'Goodwill Amortisation', N'0', 1, 1, N'Y', N'', N'PL123', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (389, 387, 351, N'A', N'PL', N'Computer Equipment Depreciation', N'0', 1, 1, N'Y', N'', N'PL123', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (390, 387, 351, N'A', N'PL', N'Fixtures and Fittings Depreciation', N'0', 1, 1, N'Y', N'', N'PL123', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (391, 387, 351, N'A', N'PL', N'Computer Software Depreciation', N'0', 1, 1, N'Y', N'', N'PL123', 0, N'EBIT', 0, N'null', N'null', N'null', 2, 0, 0, 0, NULL, NULL, NULL)

INSERT @My_Test_Temp ([AccountID], [CategoryID], [RootID], [TRNType], [AcType], [AcName], [CrDr], [Active], [Sort_Order], [HasParent], [Descr], [account_Code], [IsTaxExempt], [PostingType], [isReconcile], [Bank_Name], [Sort_Code], [Account_number], [company_ID], [Default_DPID], [Default_TaxType], [Default_TaxID], [URN], [LAST_Updated], [new_Status]) VALUES (393, 351, 351, N'S', N'PL', N'Bank Charges & Interest', NULL, 1, 5, N'Y', N'For Bank Charges and interst', NULL, 0, N'null', 0, N'null', N'null', N'null', 2, 0, 0, 0, N'0011-002-09072012-074225-ANA', CAST(0x0000A089014F24CD AS DateTime), N'none')

--SET IDENTITY_INSERT [dbo].[My_Test_Temp] OFF

--select * from @My_Test_Temp

Declare @Root int;

declare @company_ID int;

set @company_ID=2;

set @Root=351;

with cteDemo as

(

select [d].[AccountID]

--, [d].[RootId]

, [d].[CategoryID]

, [d].[TRNType]

, [d].[AcType]

, [d].[AcName]

, convert(varchar(500), [d].[TRNType] + '-' + [d].[AcName]) as [sort_column]

, 4 as [level]

,d.Sort_Order

from @My_Test_Temp as d

where [d].[CategoryID] = 0 and [d].Active=1

union all

select [cd].[AccountID]

-- , [cd].[RootId]

, [cd].[CategoryID]

, [cd].[TRNType]

, [cd].[AcType]

, [cd].[AcName]

, convert(varchar(500), [pd].[sort_column] + '-' + case [cd].[TRNType] when 'S' then '2S' when 'A' then '1A' end + '-' + [cd].[AcName])

, [pd].[level]+4

,cd.Sort_Order

from @My_Test_Temp as cd

inner join [cteDemo] as pd on [cd].[CategoryID] = [pd].[AccountID]

where [cd].Active=1

)

select [cteDemo].[AccountID]

, [cteDemo].[AcName]

, replicate('-', [cteDemo].[level]) + [cteDemo].[AcName] + '--' as [graph]

, [cteDemo].[TRNType]

, [cteDemo].[AcType]

--,[cteDemo].[RootId]

,[cteDemo].[CategoryID]

,Sort_Order,sort_column,level

from [cteDemo]

where [level] in(4,8,12)

order by [level];