Click here to Skip to main content
15,885,546 members
Search within:


How to write a Procedure to get the Hierarchical flow Dynamically
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have two tables like Account and Grouptxt for each record in Accounts table i need to find the Hierarchical flow in Grouptxt Table,below is the sample data of those 2 tables

Sample Data:-

Accounts:-
SQL
#AccountName,ShortName,Type,OpBal,ParentGroup,Move4Report,DebitLimit,CreditLimit
'C' Mannto,'C' Mannto,Customer/Supplier,0.0,Debtors Belgaum,True,0.0,0.0
2M Engineers,2M Engineers,Customer/Supplier,260.0,Debtors Belgaum,True,0.0,0.0
A. A. Engineering,A. A. Engineering,Customer/Supplier,114.0,Debtors Belgaum,False,0.0,0.0
A.B.Precision Toolings Pvt. Ltd.,A.B.Precision Toolings Pvt. Ltd.,Customer/Supplier,0.0,Other Debtors,False,0.0,0.0
A.C. Enterprises,A.C. Enterprises,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0
A. C. Sales Corporation,A. C. Sales Corporation,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0
A.D. Steel,A.D. Steel,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0
A.M. Engineers,A.M. Engineers,Customer/Supplier,260.0,Debtors Pune,True,0.0,0.0
A. N. Kalghatgi,A. N. Kalghatgi,Customer/Supplier,0.0,Creditors for Materials,True,0.0,0.0

Grouptxt:-
SQL
#GroupName,ShortName,ParentGroup,isSystem,IsTradingGroup
Asset, Asset, ,True,False
APPLICATION OF FUNDS,APPLICATION OF FUNDS,Asset,False,False
Fixed Assets,Fixed Assets,APPLICATION OF FUNDS,False,False
Gross Block,Gross Block,Fixed Assets,False,False
Depreciation,Depreciation,Fixed Assets,False,False
Net Current Assets,Net Current Assets,APPLICATION OF FUNDS,False,False
Current Assets\u001 Loans and Advances,Current Assets\u001 Loans and Advances,Net Current Assets,False,False
Sundry Debtors,Sundry Debtors,Current Assets\u001 Loans and Advances,False,False
Other Debtors,Other Debtors,Sundry Debtors,False,False
Debtors CBE,Debtors CBE,Other Debtors,False,True
Debtors Pune,Debtors Pune,Other Debtors,False,False
Debtors NDL,Debtors NDL,Other Debtors,False,False
Debtors Bangalore,Debtors Bangalore,Other Debtors,False,False
Debtors KoMiSa,Debtors KoMiSa,Other Debtors,False,True
Debtors Europe,Debtors Europe,Other Debtors,False,True
Debtors USA,Debtors USA,Other Debtors,False,True
Debtors Global,Debtors Global,Other Debtors,False,True
Debtors Chennai,Debtors Chennai,Other Debtors,False,True
Debtors Belgaum,Debtors Belgaum,Other Debtors,False,False
Debtors Kolkatta,Debtors Kolkatta,Other Debtors,False,False
Debtors Mumbai,Debtors Mumbai,Other Debtors,False,False
Debtors Gujrat,Debtors Gujrat,Other Debtors,False,False
Debtors Aurangabad,Debtors Aurangabad,Other Debtors,False,True
Debtors Indore,Debtors Indore,Other Debtors,False,True
Debtors Punjab,Debtors Punjab,Other Debtors,False,False
Debtors Hyderabad,Debtors Hyderabad,Other Debtors,False,False
Debtors DWR HBL,Debtors DWR HBL,Other Debtors,False,False
Debtors-Mumbai,Debtors-Mumbai,Other Debtors,False,False
Debtors Maharashtra,Debtors Maharashtra,Other Debtors,False,False
Debtors Uttarakhand,Debtors Uttarakhand,Other Debtors,False,True
Debtors Hosur,Debtors Hosur,Other Debtors,False,False
Debtors Ahmednagar,Debtors Ahmednagar,Other Debtors,False,False
Debtors U.P.,Debtors U.P.,Other Debtors,False,True
Cash & Bank Balances,Cash & Bank Balances,Current Assets\u001 Loans and Advances,False,False
Cash & Stamps on Hand,Cash & Stamps on Hand,Cash & Bank Balances,False,False
Deposits with Bank,Deposits with Bank,Cash & Bank Balances,False,False
Other Current Assets,Other Current Assets,Current Assets\u001 Loans and Advances,False,False
Loans & Advances,Loans & Advances,Current Assets\u001 Loans and Advances,False,False
Income Tax Paid,Income Tax Paid,Loans & Advances,False,False
Deposit With Govt. & Others,Deposit With Govt. & Others,Loans & Advances,False,False
Other Advances,Other Advances,Loans & Advances,False,False
Advance to Employees for Expenses,Advance to Employees for Expenses,Other Advances,False,False
Advance to Directors,Advance to Directors,Other Advances,False,False
Advance Salary to Employees,Advance Salary to Employees,Other Advances,False,False
Advances to Suppliers\u001 Contractors & Others,Advances to Suppliers\u001 Contractors & Others,Loans & Advances,False,True
Current Assets,Current Assets,Current Assets\u001 Loans and Advances,False,False
Inventories,Inventories,Current Assets\u001 Loans and Advances,False,False
Current Liabilities and provisions,Current Liabilities and provisions,Net Current Assets,False,False
Current Liabilities,Current Liabilities,Current Liabilities and provisions,False,False
Sundry Creditors,Sundry Creditors,Current Liabilities,False,False
Creditors for Materials,Creditors for Materials,Sundry Creditors,False,False
Creditors for Services,Creditors for Services,Sundry Creditors,False,False
Outstanding Expenses,Outstanding Expenses,Current Liabilities,False,False
Provosions For Expenses,Provisions For Expenses,Outstanding Expenses,False,False
Karnataka Sales Tax,Karnataka Sales Tax,Outstanding Expenses,False,True
Central Sales Tax,Central Sales Tax,Outstanding Expenses,False,False
Income Tax Deducted at Source by Company,Income Tax Deducted at Source by Company,Outstanding Expenses,False,True
Liability To Stake Holders,Liability To Stake Holders,Outstanding Expenses,False,False
VAT & CST Account,VAT & CST Account,Outstanding Expenses,False,False
Central Excise Liability,Central Excise Liability,Outstanding Expenses,False,False
Advance From Customers,Advance From Customers,Current Liabilities,False,True
Commission Agents,Commission Agents,Current Liabilities,False,True
Advance From Customers & Others,Advance From Customers & Others,Current Liabilities,False,True
Advance from Others,Advance from Others,Current Liabilities,False,True
Provisions,Provisions,Current Liabilities and provisions,False,False
Investments,Investments,APPLICATION OF FUNDS,False,False
Investments- Long Term,Investments- Long Term,Investments,False,False
Non Trade Investments in Govt. Securities,Non Trade Investments in Govt. Securities,Investments- Long Term,False,False
Shares,Shares,Investments- Long Term,False,False
Miscellaneous Expenditure,Miscellaneous Expenditure,APPLICATION OF FUNDS,False,False
Net Intangible Asset,Net Intangible Asset,APPLICATION OF FUNDS,False,True
Liabilities, Liabilities, ,True,False
SOURCES OF FUNDS,SOURCES OF FUNDS,Liabilities,False,False
Share Holders' Funds,Share Holders' Funds,SOURCES OF FUNDS,False,False
Share Capital,Share Capital,Share Holders' Funds,False,False
Reserves & surplus,Reserves & surplus,Share Holders' Funds,False,False
Capital Reserve,Capital Reserve,Reserves & surplus,False,False
Loan Funds,Loan Funds,SOURCES OF FUNDS,False,False
Secured Loans,Secured Loans,Loan Funds,False,False
Term Loans,Term Loans,Secured Loans,False,False
Cash Credit From Bank,Cash Credit From Bank,Secured Loans,False,False
Unsecured Loans,Unsecured Loans,Loan Funds,False,False
Long Term Loans,Long Term Loans,Unsecured Loans,False,False
Long Term Loans From Directors,From Directors,Long Term Loans,False,False
Long Term Loans From Others,From Others,Long Term Loans,False,False
Long Term Loans from Relatives of Diretors,Long Term Loans from Relatives of Diretors,Long Term Loans,False,True
Temporary Loan from Directors,Temporary Loan from Directors,Unsecured Loans,False,True
Net Deferred Tax Liability,Net Deferred Tax Liability,SOURCES OF FUNDS,False,True
Income, Income, ,True,False
Sales,Sales,Income,False,False
Customer Products,Customer Products,Sales,False,False
PTP PRODUCTS,PTP PRODUCTS,Sales,False,False
Export Sale,Export Sale,Sales,False,False
Other Income,Other Income,Income,False,False
Miscellaneous Income,Miscellaneous Income,Other Income,False,False
Interest on Bank Account & from Customers,Interest on Bank Account & from Customers,Other Income,False,True
JOB WORK,JOB WORK,Income,False,False
Expenditure, Expenditure, ,True,False
Materials Consumed,Materials Consumed,Expenditure,False,False
Raw Material & Components,Raw Material & Components,Materials Consumed,False,False
Raw Materials,Raw Materials,Raw Material & Components,False,False
Brought Out Components,Brought Out Components,Raw Material & Components,False,False
Work In Process,Work In Process,Raw Material & Components,False,False
Finished Goods,Finished Goods,Raw Material & Components,False,False
Stores & Spares,Stores & Spares,Materials Consumed,False,True
Emoluments to and Provisions for Employees,Emoluments to and Provisions for Employees,Expenditure,False,False
Salary\u001Wages\u001Allowances\u001bonus etc.,Salary\u001Wages\u001Allowances\u001bonus etc.,Emoluments to and Provisions for Employees,False,False
Contribution to PF & Other Funds,Contribution to PF & Other Funds,Emoluments to and Provisions for Employees,False,False
Welfare Expenses,Welfare Expenses,Emoluments to and Provisions for Employees,False,False
Emoluments to Directors,Emoluments to Directors,Emoluments to and Provisions for Employees,False,False
Manufacturing Expenses,Manufacturing Expenses,Expenditure,False,False
Sub Contract Charges,Sub Contract Charges,Manufacturing Expenses,False,False
Power & Fuel,Power & Fuel,Manufacturing Expenses,False,False
Insurance,Insurance,Manufacturing Expenses,False,False
Freight & Transportation,Freight & Transportation,Manufacturing Expenses,False,False
Development Expenses,Development Expenses,Manufacturing Expenses,False,False
Sales Rejection,Sales Rejection,Manufacturing Expenses,False,False
Administrative & Selling Expenses,Administrative & Selling Expenses,Expenditure,False,True
Travelling & Conveyance,Travelling & Conveyance,Administrative & Selling Expenses,False,True
Printing & Stationery,Printing & Stationery,Administrative & Selling Expenses,False,False
Postage & Telephone,Postage & Courier,Administrative & Selling Expenses,False,False
Sales Promotion & Advertisement,Sales Promotion & Advertisement,Administrative & Selling Expenses,False,False
Miscellaneous Expenses,Miscellaneous Expenses,Administrative & Selling Expenses,False,True
Bank Charges,Bank Charges,Administrative & Selling Expenses,False,False
Discount & Commission,Discount & Commission,Administrative & Selling Expenses,False,False
Payment to Auditors,Payment to Auditors,Administrative & Selling Expenses,False,False
Professional & Consultancy Fees,Professional & Consultancy Fees,Administrative & Selling Expenses,False,False
Fringe Benefit Accounts,Fringe Benefit Accounts,Administrative & Selling Expenses,False,True
Travelling Conveyance,Travelling Conveyance,Fringe Benefit Accounts,False,False
Amortization Expenses,Amortization Expenses,Administrative & Selling Expenses,False,True
Rates & Taxes,Rates & Taxes,Administrative & Selling Expenses,False,True
Interest,Interest,Expenditure,False,False
Interest on Fixed Deposit,Interest on Fixed Deposit,Interest,False,False
Interest on Unsecured Loan,Interest on Unsecured Loan,Interest,False,False
Depreciation Expenditure,Depreciation Expenditure,Expenditure,False,False


So now my Question is for each record in Account tables i need to get the Hierarchical flow from the Grouptxt table until isSystem field in Grouptxt table is True(isSystem=True) .How to do get that dynamically by using Procedures?

Below query gives the o/p,but that o/p is static o/p i need dynamic o/p by using Procedures,Can any one help me how to do that?

SQL
SELECT A.*
      ,G1.ParentGroup
      ,G2.ParentGroup
      ,G3.ParentGroup
      ,G4.ParentGroup
      ,G5.ParentGroup
      ,G6.ParentGroup
FROM account A
LEFT OUTER JOIN Grouptxt G1 ON  A.ParentGroup=G1.#GroupName
LEFT OUTER JOIN Grouptxt G2 ON G1.ParentGroup=G2.#GroupName
LEFT OUTER JOIN Grouptxt G3 ON G2.ParentGroup=G3.#GroupName
LEFT OUTER JOIN Grouptxt G4 ON G3.ParentGroup=G4.#GroupName
LEFT OUTER JOIN Grouptxt G5 ON G4.ParentGroup=G5.#GroupName
LEFT OUTER JOIN Grouptxt G6 ON G5.ParentGroup=G6.#GroupName


Thanks in Advance
Posted
Updated 24-Jul-13 2:10am
v6
Add a Solution
Comments
Raja Sekhar S 24-Jul-13 2:40am    
What are the parameters you are planning for the Stored Procedure..?
Chakri Reddy 24-Jul-13 3:13am    
the i/p parameters is #AccountName and as o/p i shoulg get the o/p as for Ex:-

'C' Mannto,'C' Mannto,Customer/Supplier,0.0,Debtors Belgaum,True,0.0,0.0,(this is a record in Account table)
Expenditure,APPLICATION OF FUNDS,Assets (Hierarchical Flow) like this the o/p should be for all records
Maciej Los 24-Jul-13 3:31am    
Do not repost: How to write a Query to get the Hierarchical flow[^]! You have got your answer. Why don't you use it?
If you want our help, please, be more specific and provide more details (example data with column names and data types, expected output, etc.). Based on above details we can't help you.
Chakri Reddy 24-Jul-13 4:43am    
i have not re-posted the same Question Maciej there was a difference and one more thing by using previous suggestions i got that o/p,but same thing how we can do using a procedure dynamically that's what my question is why can't you observe the difference?

1 solution

Please, test it:
SQL
DECLARE @account TABLE(#AccountName VARCHAR(150), ShortName VARCHAR(150), [Type] VARCHAR(150), OpBal NUMERIC(8,2),
					ParentGroup VARCHAR(150), Move4Report VARCHAR(150), DebitLimit  NUMERIC(8,2),CreditLimit NUMERIC(8,2))

INSERT INTO @account(#AccountName,ShortName,[Type],OpBal,ParentGroup,Move4Report,DebitLimit,CreditLimit)
SELECT '''C'' Mannto','''C'' Mannto','Customer/Supplier',0.0,'Debtors Belgaum','True',0.0,0.0
UNION ALL SELECT '2M Engineers','2M Engineers','Customer/Supplier',260.0,'Debtors Belgaum','True',0.0,0.0
UNION ALL SELECT 'A. A. Engineering','A. A. Engineering','Customer/Supplier',114.0,'Debtors Belgaum','False',0.0,0.0
UNION ALL SELECT 'A.B.Precision Toolings Pvt. Ltd.','A.B.Precision Toolings Pvt. Ltd.','Customer/Supplier',0.0,'Other Debtors','False',0.0,0.0
UNION ALL SELECT 'A.C. Enterprises','A.C. Enterprises','Customer/Supplier',0.0,'Creditors for Materials','True',0.0,0.0
UNION ALL SELECT 'A. C. Sales Corporation','A. C. Sales Corporation','Customer/Supplier',0.0,'Creditors for Materials','True',0.0,0.0
UNION ALL SELECT 'A.D. Steel','A.D. Steel','Customer/Supplier',0.0,'Creditors for Materials','True',0.0,0.0
UNION ALL SELECT 'A.M. Engineers','A.M. Engineers','Customer/Supplier',260.0,'Debtors Pune','True',0.0,0.0
UNION ALL SELECT 'A. N. Kalghatgi','A. N. Kalghatgi','Customer/Supplier',0.0,'Creditors for Materials','True',0.0,0.0



DECLARE @GroupTxt TABLE (#GroupName VARCHAR(150), ShortName VARCHAR(150), ParentGroup VARCHAR(150), isSystem VARCHAR(50),IsTradingGroup VARCHAR(50))
INSERT INTO @GroupTxt(#GroupName,ShortName,ParentGroup,isSystem,IsTradingGroup)
SELECT 'Asset', 'Asset', NULL,'True','False'
UNION ALL SELECT 'APPLICATION OF FUNDS','APPLICATION OF FUNDS','Asset','False','False'
UNION ALL SELECT 'Fixed Assets','Fixed Assets','APPLICATION OF FUNDS','False','False'
UNION ALL SELECT 'Gross Block','Gross Block','Fixed Assets','False','False'
UNION ALL SELECT 'Depreciation','Depreciation','Fixed Assets','False','False'
UNION ALL SELECT 'Net Current Assets','Net Current Assets','APPLICATION OF FUNDS','False','False'
UNION ALL SELECT 'Current Assets\u001 Loans and Advances','Current Assets\u001 Loans and Advances','Net Current Assets','False','False'
UNION ALL SELECT 'Sundry Debtors','Sundry Debtors','Current Assets\u001 Loans and Advances','False','False'
UNION ALL SELECT 'Other Debtors','Other Debtors','Sundry Debtors','False','False'

--uncomment below lines if you want to show 
SELECT #GroupName, ParentGroup 
FROM @GroupTxt AS G 

--using CTE
;WITH Grps AS
(
    --top groups
    SELECT DISTINCT G.#GroupName AS GrpName, H.#GroupName AS Member, 0 AS [Level]
    FROM @GroupTxt AS G INNER JOIN @GroupTxt AS H ON G.#GroupName = H.ParentGroup
    WHERE G.ParentGroup IS NULL
    --recursive groups
    UNION ALL
    SELECT H.Member AS Grpname, G.#GroupName AS Member, H.[Level] + 1 AS [Level]
    FROM @GroupTxt AS G INNER JOIN Grps AS H ON G.ParentGroup = H.Member
    WHERE G.ParentGroup IS NOT NULL
)
SELECT *
FROM Grps
ORDER BY [Level]


GrpName 				Member					Level
Asset					APPLICATION OF FUNDS			0
APPLICATION OF FUNDS			Fixed Assets				1
APPLICATION OF FUNDS			Net Current Assets			1
Net Current Assets			Current Assets\u001 Loans and Advances	2
Fixed Assets				Gross Block				2
Fixed Assets				Depreciation				2
Current Assets\u001 Loans and Advances	Sundry Debtors				3
Sundry Debtors				Other Debtors				4


Is this what you want to achieve?
 
Share this answer
 
Posted
Comments
Chakri Reddy 24-Jul-13 8:00am    
No that was not the end result am trying to achieve, the end result should be like this

#AccountName,ShortName,Type,OpBal,ParentGroup,Move4Report,DebitLimit,CreditLimit,
row_id,ParentGroup,ParentGroup,ParentGroup,ParentGroup,ParentGroup,ParentGroup

C' Mannt,C' Mannt,Customer/Supplier,0,Debtors Belgaum,TRUE,0,0,2456,
Other Debtors,Sundry Debtors,Current Assets\u001\Loans and Advances,
Net Current Assets,APPLICATION OF FUNDS,Asset

A. A. Engineering,A. A. Engineering,Customer/Supplier,114,,Debtors Belgaum
,FALSE,0,0,2458,Other Debtors,Sundry Debtors,
Current Assets\u001\Loans and AdvancesNet Current Assets,APPLICATION OF FUNDS,Asset

Aravind Kukadalli,Aravind Kukadalli,Other,0,Advance Salary to Employees,FALSE
,0,0,151,Other Advances,LoansAdvances,Current Assets\u001\Loans and Advances,
Net Current Assets,APPLICATION OF FUNDS,Asset


the End result should be like this for each record,and i have provided entire data of "Grouptxt" with some of the records of "Accounts"
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
How to write a Query to get the Hierarchical flow
Handling Events in Hierarchical User Control
How to write stored procedure to get employee's years of experience using getdate() in SQL
PHP and code flow...
how to write stored procedure for reverse string dynamically
Hierarchical DataGridView
How do I write a stored procedure that get table name and a record anf insert record into the table
Retrieving hierarchical table data as simple table format for report? Help please!
Hierarchical Gridview Creation
How to write and execute the dynamic stored procedure in mysql database

Advertise
Privacy
Cookies
Terms of Use
Last Updated 24 Jul 2013
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web02 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900