Using nested queries instead of procedures

A listed query that calculates a summary report from a General Ledger system.

This Content Component encountered an error

In a General Ledger (GL) system it is always critical to use procedures to compile frequent analysis reports like Cash/Bank Summary Reports, where amounts for corresponding accounts will appear instead of amounts for target accounts.

This report should also handle a situation where voucher (JV) has multiple accounts on both the debit and credit side. The business rules that have been incorporated to handle the above situation are:

  1. The weighted age of each account amount on the debit side with reference to the debit balance of the voucher is calculated.
  2. The above weighted age is applied to the amount of each individual account on the credit side and a proportionate amount is calculated.
  3. The above calculated amounts are shown with respective accounts as corresponding accounts of the account appearing on debit side.
  4. The usual practice is to write procedures to compile all the transactions. This needs to have one or more temporary tables where transactions will be placed for opening balances and running balances separately. Then we need to compute the opening balance for the report based on the date range requested. Procedures are a costly solution in this situation in terms of more round trips for disk I/O and time to compile the results.

    Instead, we can generate reports using a listed query. This query produces results in less than a minute on a data volume of 100,000 transactions within the fiscal period. I'm using MS-SQLSever 7.0 and TSQL on Windows NT 4.0:

    CREATE TABLE gl_chart (
           company_code	    varchar(4) NOT NULL,
           account_code         varchar(14) NOT NULL,
           currency_code        varchar(4) NOT NULL,
           level_no             varchar(1) NOT NULL,
           master_company       varchar(4) NULL,
           master_account       varchar(14) NULL,
           account_type         varchar(1) NOT NULL,
           account_category     varchar(1) NOT NULL,
           bspl_type            varchar(1) NOT NULL,
           account_name         varchar(80) NULL,
           opening_cr           numeric(14,2) NULL,
           opening_dr           numeric(14,2) NULL,
           current_cr           numeric(14,2) NULL,
           current_dr           numeric(14,2) NULL,
           opening_f_cr         numeric(14,2) NULL,
           opening_f_dr         numeric(14,2) NULL,
           current_f_cr         numeric(14,2) NULL,
           current_f_dr         numeric(14,2) NULL,
           shadow_cr            numeric(14,2) NULL,
           shadow_dr            numeric(14,2) NULL,
           shadow_f_cr          numeric(14,2) NULL,
           shadow_f_dr          numeric(14,2) NULL,
           Bank_Limit           numeric(14,2) NULL,
           prev_dr              numeric(14,2) NULL,
           prev_cr              numeric(14,2) NULL,
           prev_f_dr            numeric(14,2) NULL,
           prev_f_cr            numeric(14,2) NULL,
           doc                  datetime NULL,
           user_code            varchar(10) NULL,
           last_update          datetime NULL,
           update_by            varchar(8) NULL,
           active               varchar(1) NULL,
           account_distributed  varchar(1) NULL,
           check_limit          varchar(1) NULL,
           PRIMARY KEY (company_code,account_code) 
    )
    go
    
    
    CREATE TABLE gl_voucher (
           company_code         varchar(4) NOT NULL,
           voucher_type         varchar(4) NOT NULL,
           voucher_seq          varchar(6) NOT NULL,
           voucher_date         datetime NOT NULL,
           doc                  datetime NULL,
           posting_date         datetime NULL,
           voucher_remarks      varchar(240) NULL,
           voucher_amount       numeric(14,2) NULL,
           voucher_module       varchar(2) NULL,
           user_code            varchar(8) NULL,
           last_update          datetime NULL,
           update_by            varchar(8) NULL,
           checked              varchar(1) NULL,
           checked_by           varchar(8) NULL,
           checked_on           datetime NULL,
           approved             varchar(1) NULL,
           approved_by          varchar(8) NULL,
           approved_on          datetime NULL,
           verified             varchar(1) NULL,
           verified_by          varchar(8) NULL,
           verified_on          datetime NULL,
           voucher_reference    varchar(80) NULL,
           voucher_distributed  varchar(1) NULL,
           reconsile            varchar(1) NULL,
           reconsile_by         varchar(4) NULL,
           reconsile_date       datetime NULL,
           bank_ref             varchar(20) NULL,
           bank_date            datetime NULL,
           PRIMARY KEY (company_code,voucher_type, voucher_seq) 
    )
    go
    
    
    CREATE TABLE gl_voucher_accounts (
           company_code         varchar(4) NOT NULL,
           voucher_type         varchar(4) NOT NULL,
           voucher_seq          varchar(6) NOT NULL,
           seq_no               varchar(2) NOT NULL,
           account_code         varchar(14) NOT NULL,
           currency_dr          numeric(14,2) NULL,
           currency_cr          numeric(14,2) NULL,
           credit               numeric(14,2) NULL,
           debit                numeric(14,2) NULL,
           naration             varchar(80) NULL,
           account_distributed  varchar(1) NULL,
           amount_distributed   numeric(14,2) NULL,
           currency_code        varchar(4) NULL,
           currency_rate        numeric(10,6) NULL,
           PRIMARY KEY (company_code,voucher_type, voucher_seq, seq_no, 
                  account_code), 
           FOREIGN KEY (company_code, voucher_type, voucher_seq)
                                 REFERENCES gl_voucher, 
           FOREIGN KEY (company_code, account_code)
                                 REFERENCES gl_chart
    )
    go
    --
    ***********
    declare @account	varchar(14),			--	account code for which a reverse ledger is required
    	@company	varchar(4),			--	company code
    	@sd		datetime,			--	start date
    	@ed		datetime			--	end date
    
    select	@account	= 	'50101010001',		
    	@company	=	'C002',			
    	@sd		=	'01-feb-1999',		
    	@ed		=	'28-feb-1999'		
    
    --
    --
    
    select 	c.account_code,
    	unposted = case when (shadow_dr<>0 or shadow_cr <> 0) then '*' else ' ' end,
    	opening_balance	= (opening_dr + isnull(o.odb,0))- (opening_cr + isnull(o.ocr,0)),
    	account_name 	= upper(account_name),
    	r.unrecon,
    	r.voucher_date,
    	r.voucher_type,
    	r.voucher_seq,
    	r.account_code,
    	r.naration,
    	r.dr,
    	r.cr
      from 	gl_chart c, 	
    		(select 	v.company_code,
    				account_code,
    				odb=sum(debit),
    				ocr=sum(credit) 
    		   from 	gl_voucher_accounts a ,gl_voucher v
                      where		a.company_code = v.company_code and
    				a.voucher_type = v.voucher_type and
    				a.voucher_seq  = v.voucher_seq  and
    				v.voucher_date < @sd            and
    				v.company_code = @company       and
    				a.account_code = @account
      		  group  by 	v.company_code,account_code) O,
    
    --
    		(SELECT	v.company_code,
    			v.account_code,
    			unrecon	= case when reconsile = '0' then '*' else '' end,
    			voucher_date,
    			V.VOUCHER_TYPE,
    			V.VOUCHER_SEQ,
    			v.naration,
    			debit,
    			amountdr,
    			credit,
    			amountcr,
    			dr=Credit*AmountDr,
    			cr=Debit*amountCr
    		  FROM	GL_VOUCHER_ACCOUNTS V, 
    			(SELECT	COMPANY_CODE,VOUCHER_TYPE,VOUCHER_SEQ,
    				DB=SUM(DEBIT),CR=SUM(CREDIT),
    				AmountDr=abs(sum(case when account_code = @account then (debit)  else 0 end))/sum(debit),
    				AmountCr=abs(sum(case when account_code = @account then (credit) else 0 end))/sum(Credit)
    			   FROM GL_VOUCHER_ACCOUNTS
    			  GROUP BY COMPANY_CODE,VOUCHER_TYPE,VOUCHER_SEQ) A  ,
    				GL_VOUCHER H
    		 WHERE	v.company_code = a.company_code
    		   and  v.voucher_type = a.voucher_type
    		   and  v.voucher_seq  = a.voucher_Seq
    		   and	v.company_code = h.company_code
    		   and  v.voucher_type = h.voucher_type
    		   and  v.voucher_seq  = h.voucher_Seq
    		   and  (h.voucher_date >= @sd and h.voucher_date <=  @ed)
    		   and	v.ACCOUNT_CODE <> @account
     		   and	v.company_code = @company
    		   and	exists (select account_code from gl_voucher_accounts
    				where company_code = v.company_code
    				  and voucher_type = v.voucher_type
    				  and voucher_seq  = v.voucher_seq
    				  and account_code = @account) ) R
     where c.company_code *= O.company_code
       and c.account_code *= O.account_code
       and c.company_code *= R.company_code
       and c.account_code  = @account
       and c.company_code  = @company
       and c.account_code  = @account
       and (r.dr > 0 or r.cr > 0)
    order by c.account_code,voucher_date,voucher_type,voucher_seq
    

     

    For More Information


This was first published in May 2001

Dig deeper on SQL Server Stored Procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close