如何编写一个过程来动态获取分层流程 [英] How to write a Procedure to get the Hierarchical flow Dynamically
问题描述
对于Accounts表中的每条记录，我有两个表，如Account和Grouptxt，我需要在Grouptxt表中找到Hierarchical流，下面是这两个表的示例数据
样本数据： -
账户： -
＃AccountName，ShortName，类型，OpBal，ParentGroup，Move4Report，DebitLimit，CreditLimit
' C' Mannto，' C' Mannto，客户/供应商， 0 。 0 ，Debtors Belgaum，True， 0 。< span class =code-digit> 0 ， 0 。 0
2M工程师，2M工程师，客户/供应商， 260 。 0 ，Debtors Belgaum，True， 0 。 0 ， 0 。 0
A. A. Engineering，A。 A.工程，客户/供应商， 114 。 0 ，Debtors Belgaum，False， 0 。 0 ， 0 。 0
AB 精确工具列兵。有限公司，A.B。精确工具列兵。有限公司，客户/供应商， 0 。 0 ，其他债务人，False， 0 。 0 ， 0 。 0
AC Enterprises，AC企业，客户/供应商， 0 。 0 ，债权人 for 材料，True， 0 。 0 ， 0 。 0
AC Sales Corporation，A。 C.销售公司，客户/供应商， 0 。 0 ，债权人 for Materials，True， 0 。 0 ， 0 。 0
AD Steel，AD钢铁，客户/供应商， 0 。 0 ，债权人 for 材料，True， 0 。 0 ， 0 。 0
AM工程师，A.M。工程师，客户/供应商， 260 。 0 ，Debtors Pune，True， 0 。 0 ， 0 。 0
AN Kalghatgi，A。 N. Kalghatgi，客户/供应商， 0 。 0 ，债权人 for 材料，True， 0 。 0 ， 0 。 0
Grouptxt： -
< pre lang =SQL>＃GroupName，ShortName，ParentGroup，isSystem，IsTradingGroup
Asset，Asset ,, True，False
APPLICATION OF FUNDS ，APPLICATION OF FUNDS，Asset，False，False
固定资产，固定资产，申请 OF FUNDS，False，False
总块数，总块数，固定资产，虚假，虚假
折旧，折旧，固定资产，虚假，虚假
净当前资产，净当前资产，APPLICATION OF FUNDS，False，False
当前 Assets\u001贷款和进展，当前 Assets \ u001贷款和进展，净当前资产，错误，错误
杂项债务人，杂项债务人，当前 Assets \ u001贷款和预付款，虚假，虚假
其他债务人，其他债务人，杂项债务人，虚假，虚假
债务人CBE，债务人CBE，其他债务人，虚假，真实
债务人浦那，债务人浦那，其他债务人，虚假，虚假
债务人NDL，债务人NDL，其他债务人，虚假，虚假
债务人班加罗尔，债务人班加罗尔，其他债务人，虚假，虚假
债务人KoMiSa，债务人KoMiSa，其他债务人，虚假，真实
债务人欧洲，债务人欧洲，其他债务人，虚假，真实
Debtors USA，Debtors USA，Other Debtors，False，True
Debtors Global，Debtors Global，Other Debtors，False，True
Debtors Chennai，Debtors Chennai，Other Debtors，False，Tr ue
债务人Belgaum，债务人Belgaum，其他债务人，虚假，虚假
债务人Kolkatta，债务人Kolkatta，其他债务人，虚假，虚假
债务人孟买，债务人孟买，其他债务人，虚假，虚假
债务人古吉拉特，债务人古吉拉特，其他债务人，虚假，虚假
债务人奥兰加巴德，债务人奥兰加巴德，其他债务人，虚假，真实
债务人印多尔，债务人印多尔，其他债务人，虚假，真实
债务人旁遮普邦，债务人旁遮普邦，其他债务人，虚假，虚假
债务人海得拉巴，债务人海德拉巴，其他债务人，虚假，虚假
债务人DWR HBL，债务人DWR HBL，其他债务人，虚假，虚假
债务人 - 孟买，债务人 - 孟买，其他债务人，虚假，虚假
债务人马哈拉施特拉邦，债务人马哈拉施特拉邦，其他债务人，虚假，虚假
债务人Uttarakhand，债务人Uttarakhand，其他债务人，虚假，真实
Debtors Hosur，Debtors Hosur，其他债务人，虚假，虚假
债务人Ahmednagar，债务人Ahmednagar，其他债务人，虚假，虚假
债务人，债务人，其他债务人，虚假，真实
现金&安培;银行余额，现金和银行余额，当前资产\ u001贷款和预付款，错误，错误
现金&邮票 手，现金&邮票 手，现金&银行余额，错误，错误
存款 银行，存款 银行，现金&银行余额，假，错误
其他当前资产，其他当前资产，当前 Assets \ u001贷款和进展，错误，错误
贷款&进步，贷款和Advances，当前 Assets \ u001贷款和进展，错误，错误
所得税，所得税，贷款和贷款进展，错误，错误
存款使用政府。 &安培;其他，存款使用政府。 &安培;其他，贷款和进展，虚假，虚假
其他进展，其他进展，贷款和Advances，False，False
Advance to Employees for Expenses，Advance to 员工 费用，其他预付款，错误，错误
预付至董事，高级 董事，其他进展，错误，错误
高级薪资 to 员工，高级薪资 员工，其他进展，错误，错误
晋级 供应商\ u001承包商&其他，推进 供应商\ u001承包商&其他，贷款和Advances，False，True
当前资产，当前资产，当前 Assets \ u001贷款和进展，错误，错误
存货，存货，当前 Assets \ u001贷款和进展，错误，错误
当前负债和条款，当前负债和条款，Net 当前资产，False，False
当前负债，当前负债，当前负债和条款，False，False
杂项债权人，杂项债权人，当前负债，Fal se，False
债权人 材料，债权人 材料，杂项债权人，虚假，错误
债权人用于服务，债权人用于服务，杂项债权人，虚假，错误
优秀费用，未支出的费用，当前负债，错误，错误
Provosions 对于费用，条款用于费用，未付费用，假，假
卡纳塔克邦销售税，卡纳塔克邦销售税，未付费用，虚假，真实
中央销售税，中央销售税，未付费用，假，错误
所得税来源 公司，扣除所得税在来源 按公司，未付费用，错误，真实
责任 利益相关者，责任 利益相关者，未付费用，虚假，虚假
VAT& CST账户，增值税和CST账户，未付费用，虚假，错误
中央消费税，中央消费税，未付费用，虚假，错误
预付款来自客户，高级来自客户，当前负债，错误，真
佣金代理，佣金代理，当前负债，False，True
Advance 来自客户&其他，Advance 来自客户&其他，当前负债，False，True
Advance 来自其他，Advance from Others，当前负债，False，True
条款，规定，当前负债和条款，False，False
投资，投资，APPLICATION OF 资金，虚假，虚假
投资 - 长期，投资 - 长期，投资，虚假，虚假
非贸易投资 政府。证券，非贸易投资 政府。证券，投资 - 长期，虚假，虚假
股票，股票，投资 - 长期，虚假，虚假
杂项支出，杂项支出，申请 OF FUNDS，False，False
净无形资产，净无形资产，应用 OF 资金，虚假，真实
负债，负债，真，False
SOURCES OF 资金，来源 OF 资金，负债，虚假，虚假
共享持有人' 基金，股份持有人基金，来源 OF FUNDS，False，False
股本，股本，股份持有人' 基金，虚假，假
储备&盈余，储备和盈余，持股人的基金，虚假，虚假
资本储备，资本储备，储备和&盈余，虚假，虚假
贷款资金，贷款资金，来源 OF 资金，虚假，虚假
抵押贷款，抵押贷款，贷款资金，虚假，False
定期贷款，定期贷款，抵押贷款，虚假，虚假
现金贷方来自银行，现金贷方来自银行，抵押贷款，虚假，虚假
无抵押贷款，无抵押贷款，贷款，虚假，虚假
长期贷款，长期贷款，无抵押贷款，虚假，虚假
长期贷款来自董事，来自董事，长期贷款，虚假，错误
长期贷款来自其他，来自其他，长期贷款，错误，错误
长期贷款来自亲属 长期贷款来自 Diretor的亲属 s，长期贷款，虚假，真实
临时贷款来自董事，临时贷款来自董事，无抵押贷款，虚假，真实
净递延税务责任，净递延税务责任，来源 OF 资金，虚假，真实
收入，收入， ，True，False
销售，销售，收入，虚假，错误
客户产品，客户产品，销售，虚假，错误
PTP产品，PTP产品，销售，假，假
出口销售，出口销售，销售，虚假，虚假
其他收入，其他收入，收入，虚假，虚假
杂项收入，杂项收入，其他收入，虚假，错误
利息银行帐户&中的class =code-keyword> 来自客户，兴趣 银行帐户& 来自客户，其他收入，假，真
工作，工作，收入，虚假，错误
支出，支出，真，假
材料消耗，材料消耗，支出，假，假
原材料&组件，原料&组件，材料消耗，假，假
原材料，原材料，原材料和组件，假，假
带出组件，带出组件，原材料&组件，假，错误
工作在过程中，工作在过程，原材料&组件，假，假
成品，成品，原材料&组件，假，假
商店&备件，商店和备件，材料消耗，虚假，真实
资金 和条款 for 员工，薪酬 和条款 for 员工，支出，虚假，错误
薪水\u001Wages \ u001Allowances \ u001bonus等，Salary \u001Wages \ u001Allowances \ u001bonus等，薪酬 和条款 员工，False，False
贡献至 PF&其他基金，贡献 PF&其他资金，薪酬 和条款 员工，错误，错误
福利费用，福利费用，薪酬 和条款 for 员工，False，False
薪酬 董事，薪酬 导演，薪酬 和条款 for 员工，虚假，错误
制造费用，制造费用，支出，虚假，虚假
子合同费用，分包合同费用，制造费用，虚假，虚假
Power&燃料，动力和电力燃料，制造费用，假，假
保险，保险，制造费用，假，假
运费&运输，货运和运输，制造费用，虚假，虚假
开发费用，开发费用，制造费用，虚假，虚假
销售拒绝，销售拒绝，制造费用，虚假，虚假
管理&销售费用，行政和销售费用，支出，虚假，真实
旅行&运送，旅行和运输运输，行政和销售费用，虚假，真实
Printing&文具，印刷&文具，行政&出售费用，虚假，虚假
邮资&电话，邮资和信使，行政和销售费用，虚假，虚假
促销&广告，促销和广告广告，行政和出售费用，虚假，虚假
杂项费用，杂项费用，行政费用销售费用，虚假，真实
银行手续费，银行手续费，行政费用销售费用，虚假，虚假
折扣&佣金，折扣&委员会，行政和销售费用，错误，错误
付款 审核员，付款 审核员，行政和&销售费用，虚假，虚假
专业&咨询费，专业&咨询费，行政和出售费用，虚假，虚假
附带福利账户，附带福利账户，行政和出售费用，虚假，真实
旅行转账，旅行转账，附带福利账户，虚假，虚假
摊销费用，摊销费用，行政和费用销售费用，虚假，真实
房价&税，税率和税率税收，行政和销售费用，虚假，真实
利息，利息，支出，虚假，错误
利息 定期存款，利息 on 定期存款，利息，错误，错误
利息 无抵押贷款，利息 on 无抵押贷款，利息，虚假，虚假
折旧支出，折旧支出，支出，虚假，错误
所以现在我的问题是对于Account表中的每条记录，我需要从Grouptxt表中获取Hierarchical流，直到Grouptxt表中的isSystem字段为True（isSystem = True）。如何通过使用Procedures动态获取？
下面的查询给出了o / p，但o / p是静态o / pi需要动态o / p通过使用程序，任何人都可以帮我怎么做？
SELECT A. *
，G1.ParentGroup
，G2.ParentGroup
，G3.ParentGroup
，G4.ParentGroup
，G5.ParentGroup
，G6.ParentGroup
FROM 帐户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
先谢谢请测试一下：
DECLARE @ account TABLE （＃AccountName VARCHAR （ 150 ），ShortName VARCHAR （ 150 ），[类型] 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 ， ' 债务人Belgaum'，' True'， 0 。 0 ， 0 。 0
UNION ALL SELECT ' 2M工程师，' 2M工程师，' Customer / Supplier'， 260 。 0 ，' 债务人Belgaum'，' 是的'， 0 。 0 ， 0 . 0
UNION ALL SELECT ' A. A. Engineering'，' A. A.工程'，' 客户/供应商， 114 。 0 ，' 债务人Belgaum'，' False'， 0 。 0 ， 0 。 0
UNION ALL SELECT ' ABPrecision Toolings Pvt。有限公司'，' A.B.Precision Toolings Pvt。有限公司'，' 客户/供应商， 0 。 0 ，' 其他债务人，' False'， 0 。 0 ， 0 。 0
UNION ALL SELECT ' AC企业，' A.C。企业，' 客户/供应商， 0 。 0 ，' 债权人材料'，' True'， 0 。 0 ， 0 。 0
UNION ALL SELECT ' A. C. Sales Corporation'，' A. C. Sales Corporation'，' 客户/供应商， 0 。 0 ，' 材料债权人，' True'， 0 。 0 ， 0 。 0
UNION ALL SELECT ' AD Steel'，' A.D。钢'，' 客户/供应商， 0 。 0 ，' 债权人材料'，' True'， 0 。 0 ， 0 。 0
UNION ALL SELECT ' AM工程师，' A.M。工程师，' 客户/供应商， 260 。 0 ，' Debtors Pune '，' True'， 0 . 0 ， 0 。 0
UNION ALL SELECT ' A. N. Kalghatgi'，' A. N. Kalghatgi'，' 客户/供应商， 0 。 0 ，' 材料的债权人，' True'， 0 。 0 ， 0 。 0
DECLARE @ GroupTxt TABLE （＃GroupName VARCHAR （ 150 ），ShortName VARCHAR （ 150 ），ParentGroup VARCHAR （ 150 ），isSystem VARCHAR （ 50 ），IsTradingGr oup VARCHAR （ 50 ））
INSERT INTO @ GroupTxt （＃GroupName，ShortName，ParentGroup，isSystem，IsTradingGroup）
SELECT ' 资产'，' 资产'， NULL ，' True'，' 错误
UNION 所有 SELECT ' 资金申请，' 资金申请，' 资产'，' False'，' False'
UNION ALL SELECT ' 固定资产'，' 固定资产'，' 资金申请，' 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 \t\t\t\tMember\t\t\t\t\tLevel
Asset\t\t\t\t\tAPPLICATION OF FUNDS\t\t\t0
APPLICATION OF FUNDS\t\t\tFixed Assets\t\t\t\t1
APPLICATION OF FUNDS\t\t\tNet Current Assets\t\t\t1
Net Current Assets\t\t\tCurrent Assets\u001 Loans and Advances\t2
Fixed Assets\t\t\t\tGross Block\t\t\t\t2
Fixed Assets\t\t\t\tDepreciation\t\t\t\t2
Current Assets\u001 Loans and Advances\tSundry Debtors\t\t\t\t3
Sundry Debtors\t\t\t\tOther Debtors\t\t\t\t4
Is this what you want to achieve?
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:-
#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:-
#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?
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
Please, test it:
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?
这篇关于如何编写一个过程来动态获取分层流程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
