Financial Reporting(Balance Sheet, etc.) ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Financial Reporting(Balance Sheet, etc.) ?

Author

Topic

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-07-22 : 08:29:16

Good day!

I am having a problem where I want to display a hierarchical view of an accounts as Balance Sheet. I'm thinking to use replicate but I cant figure out on how to generate the output.

I need this output to be generated:


ASSETS
  Current Assets
     Checking/Savings
         ...all checking/savings account here
            ...if checking/savings account has sub account
         ...Total of account that has sub account
     Total Checking/Savings
  Total Current Assets

  Other Current Assets
     Loans Receivable
        ......all checking/savings account here
            ...if checking/savings account has sub account
        ...total of account that has sub account
     Total Loans Receivable
  Total Other Current Assets

TOTAL ASSETS

I currently have this working query that generates the balance of each accounts by month dynamically base on given dates. I need this to be hierarchical just like the above format.


use MFR_Merchandise
go
declare @MonthColumns	        as nvarchar(max)
declare @SQLtext		as nvarchar(4000)		
declare @IterationDate	        as date
declare @FromDate		as date
declare @ToDate			as date
declare @MonthDates		as Table(MonthsFilter date NOT NULL) 

set @FromDate='2011-11-23'
set @ToDate='2011-12-30'

while @FromDate < @ToDate
	begin
		insert into @MonthDates
		select dateadd(month, datediff(month, 0, @FromDate) + 1, -1)

		set @FromDate = (select min(MonthsFilter) 
                         from   @MonthDates 
                         where  MonthsFilter > @FromDate) 
                         
                set @FromDate = DATEADD(dd, 1, @FromDate) 
	end
	
		update @MonthDates set MonthsFilter = @ToDate 
		where MonthsFilter = (select MAX(MonthsFilter) from @MonthDates)
	
		set @IterationDate = (select Min(MonthsFilter) 
                              from @MonthDates) 
		set @MonthColumns = N''

while (@IterationDate IS NOT NULL) 
	begin 
		set @MonthColumns = @MonthColumns + N', ' + 
			QUOTENAME(Cast(@IterationDate AS nvarchar(20))) 
				
		set @IterationDate = (select Min(MonthsFilter) 
							  from @MonthDates 
                              where MonthsFilter > @IterationDate) 
	end
		set @MonthColumns = Substring(@MonthColumns,2,Len(@MonthColumns)) 
		
		--print @MonthColumns

--resetting @FromDate to its original passed value
set @FromDate='2011-11-23' 
	
set @SQLtext=N';With CTE
AS
(
	SELECT al.AccountID,
	       al.AccntNo_Name,
	       acc.MonthDate,
			   coalesce(sum(Debit),0)-coalesce(SUM(Credit),0) as currentbalance
	FROM tbl_accountlist al
		
        INNER JOIN (SELECT debit_accnt_ID AS AccountID, CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 < ''' + convert(nvarchar(11),@ToDate) + ''' 
                                                        THEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
                                                        ELSE ''' + convert(nvarchar(11),@ToDate) + '''
                                                        END AS MonthDate,sum(debit_amt) as Debit,
                                                        CAST(0 AS decimal(15,2)) AS Credit
        FROM tbl_account_transactions_detail
		WHERE (transdate > = ''' + convert(nvarchar(11),@FromDate) + ''' 
		AND transdate < DATEADD(dd,1,''' + convert(nvarchar(11),@ToDate) + '''))
        GROUP BY debit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1

        UNION ALL

        SELECT credit_accnt_ID,CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 < ''' + convert(nvarchar(11),@ToDate) + ''' 
							                            THEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 
							                            ELSE ''' + convert(nvarchar(11),@ToDate) + ''' 
							                            END  AS MonthDate,0 as Debit,
                                                        sum(credit_amt) AS Credit
        FROM tbl_account_transactions_detail
		WHERE (transdate > = ''' + convert(nvarchar(11),@FromDate) + ''' 
		AND transdate < DATEADD(dd,1,''' + convert(nvarchar(11),@ToDate) + '''))
        GROUP BY credit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
       )acc
       
        ON acc.AccountID = al.AccountID
        GROUP BY al.AccountID,al.AccntNo_Name,MonthDate
)

		SELECT *
		FROM CTE
		PIVOT (SUM(currentbalance) FOR Monthdate IN (' + @MonthColumns + N'))p'
print @SQLtext				
EXEC Sp_executesql @SQLtext 
		
GO

Here is the result


AccountID  AccntNo_Name	        2011-11-30        2011-12-30	
275	   Cash on Drawer	80000000.0000	  70000.0000	
285	   Shipping Income	-80000000.0000	  -70000.0000

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-07-22 : 17:45:03

This won't be enough to answer your question, but I hope it helps get you closer. You can use grouping functions (cube or rollup) to help, as well as the the GROUPING_ID function.


CREATE TABLE #t1 (col1 char(1),col2 char(2), col3 int)

INSERT INTO #t1
VALUES

('a','a',1),
('a','b',2),
('a','c',3),
('b','a',4),
('b','d',5),
('b','c',6),
('d','a',7),
('d','b',8)


select col1,col2,SUM(col3) as Total 
,GROUPING_ID(col1,col2) as TotalGroup
 from #t1
 
 group by col1,col2
 
 with cube

If you look at the TotalGroup column, you will see that the value of 2 is associated with the subtotal for the value of the 2nd column, a value of 1 is associated with the subtotals over the 1st column, and 3
is associated with the value of everything in the 3rd column. The 1,2 and 3 in totalgroup being associated with col1 1,2,3 is mostly coincidence. Try this on your dataset, after reading up on CUBE and GROUPING_ID!, and see if this can help you.

Jim

Everyday I learn something that somebody else already knew

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-07-23 : 05:15:30

Thank you jimf!

Ive got your idea. This is helpful to get that total of such an account or sub account.

I will modify the query and post here back soon for updates!

Any suggestions is highly appreciated!

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-05 : 21:13:46

Ive started to build the query to generate the balance sheet in hierarchical view. But I think one more thing Ive missed!

Here is my table structure:


AccountID  AccntNo_Name         subaccntID/parentID
  383      ASSETS                       NULL
  384      Current Assets               383
  385      Checking/Savings             384
  386      Cash on Drawer               385
  387      Cash in Bank                 385
  388      LBP                          387
  389      World Bank                   387

The result must be


AccountID  AccntNo_Name                 subaccntID/parentID
  383      ASSETS                              NULL
  384        Current Assets                    383
  385          Checking/Savings                384
  386            Cash on Drawer                385
  387            Cash in Bank                  385
  388              LBP                         387
  389              World Bank                  387     
  
....................and so on.....

Here is my query so far:


use MFR_Merchandise
go

; with ranked (AccountID, AccntNo_Name, subaccntid)
As (
	Select 
		AccountID,
		Display=convert(varchar(1000),AccntNo_Name),
		subaccntid
		
	From tbl_accountlist
), AccountPart1

AS
(
Select 
		Level=1,
		AccountID,
		Display=convert(varchar(1000),AccntNo_Name),
		subaccntid
		
From ranked A
Where A.subaccntid is NULL

Union All

Select 
		Level=B.Level + 1,
		A.AccountID,
		Display=convert(varchar(1000),replicate('  ', B.Level) + B.Display),
		A.subaccntid
		
	From ranked A
	Inner Join AccountPart1 B
	On B.subaccntid = A.subaccntid
	
)
Select * 
From AccountPart1
Order By ltrim(Display)

This query is running but it only gives the parent Account the subaccounts are missing! Whats wrong with this query?

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-05 : 21:19:52

it should be


use MFR_Merchandise
go

; with ranked (AccountID, AccntNo_Name, subaccntid)
As (
	Select 
		AccountID,
		Display=convert(varchar(1000),AccntNo_Name),
		subaccntid
		
	From tbl_accountlist
), AccountPart1

AS
(
Select 
		Level=1,
		AccountID,
		Display=convert(varchar(1000),AccntNo_Name),
		subaccntid
		
From ranked A
Where A.subaccntid is NULL

Union All

Select 
		Level=B.Level + 1,
		A.AccountID,
		Display=convert(varchar(1000),replicate('  ', B.Level) + B.Display),
		A.subaccntid
		
	From ranked A
	Inner Join AccountPart1 B
	On B.subaccntid = A.subaccntidA.AccountID
	
)
Select * 
From AccountPart1
Order By ltrim(Display)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-05 : 21:26:29

Thank you visakh16!

I've changed as you've said but still only the ASSETS account is displayed without its sub accounts!


.....
From ranked A
	Inner Join AccountPart1 B
	On B.subaccntid =A.AccountID

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-05 : 22:08:11

I think I got it. But the Account Name is still assets!


.........
From ranked A
	Inner Join AccountPart1 B
	On B.AccountID = A.subaccntid

here is the result:


Level          AccntNo_Name                         subaccntID
 383             ASSETS                                 NULL
 384               ASSETS                    383
 385                  ASSETS                 384

Color in red in the result is not correct. It must be


AccountID  AccntNo_Name                 subaccntID/parentID
  383      ASSETS                              NULL
  384        Current Assets                    383
  385          Checking/Savings                384

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-05 : 22:25:14

This make it works.But I need to merge this on the other CTE at the bottom.

here is the final query. Changes marked in red.


Select 
		Level=B.Level + 1,
		A.AccountID,
		Display=convert(varchar(1000),replicate('  ', B.Level) + AccntNo_Name),
		A.subaccntid
		
	From ranked A
	Inner Join AccountPart1 B
	On B.subaccntid = A.subaccntidA.AccountID
	
)
Select * 
From AccountPart1
Order By Level

Now I need to merge the above query tho is:


set @SQLtext=N';With CTE
AS
(
	SELECT al.AccountID,
	       al.AccntNo_Name,
	       acc.MonthDate,
	       coalesce(sum(Debit),0)-coalesce(SUM(Credit),0) as currentbalance
	FROM tbl_accountlist al
		
        INNER JOIN (SELECT debit_accnt_ID AS AccountID, CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 < ''' + convert(nvarchar(11),@ToDate) + ''' 
                                                        THEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
                                                        ELSE ''' + convert(nvarchar(11),@ToDate) + '''
                                                        END AS MonthDate,sum(debit_amt) as Debit,
                                                        CAST(0 AS decimal(15,2)) AS Credit
        FROM tbl_account_transactions_detail
		WHERE (transdate > = ''' + convert(nvarchar(11),@FromDate) + ''' 
		AND transdate < DATEADD(dd,1,''' + convert(nvarchar(11),@ToDate) + '''))
        GROUP BY debit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1

        UNION ALL

        SELECT credit_accnt_ID,CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 < ''' + convert(nvarchar(11),@ToDate) + ''' 
							                            THEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 
							                            ELSE ''' + convert(nvarchar(11),@ToDate) + ''' 
							                            END  AS MonthDate,0 as Debit,
                                                        sum(credit_amt) AS Credit
        FROM tbl_account_transactions_detail
		WHERE (transdate > = ''' + convert(nvarchar(11),@FromDate) + ''' 
		AND transdate < DATEADD(dd,1,''' + convert(nvarchar(11),@ToDate) + '''))
        GROUP BY credit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
       )acc
       
        ON acc.AccountID = al.AccountID
        GROUP BY al.AccountID,al.AccntNo_Name,MonthDate
)

		SELECT *
		FROM CTE
		PIVOT (SUM(currentbalance) FOR Monthdate IN (' + @MonthColumns + N'))p'
print @SQLtext				
EXEC Sp_executesql @SQLtext 
		
GO

and the result must be look like this


AccountID  AccntNo_Name           subaccntID   2011-11-30   2012-07-26
  383      ASSETS                     NULL        NULL         NULL
  384        Current Assets           383         NULL         NULL
  385          Checking/Savings       384         NULL         NULL
  386            Cash on Drawer       385        80,000.00     20.00
  386            Cash in Bank         385        60,000.00    -20.00
 .....................

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-07 : 21:08:18

Any body help me? Ive spend 2 whole night to get the result but I kept getting this error msg:

Msg 467, Level 16, State 1, Line 21
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'AccountPart1'.
Msg 205, Level 16, State 1, Line 8
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Here is my query so far:


use MFR_Merchandise
go

declare @FromDate		as date
declare @ToDate			as date

set @FromDate='2011-11-23'
set @ToDate='2012-07-26'

; with BalanceSheet (AccountID, AccntNo_Name,AccountType, AccountCategoryID, IsSubAccnt,HasSub,subaccntid)
As (
	Select 
		AccountID,
		Display=AccntNo_Name,
		AccountType,
		AccountCategoryID,
		IsSubAccnt,
		HasSub,
		subaccntid
		
	From tbl_accountlist
	
), AccountPart1

AS
(
	Select 
		Level=1,
		AccountID,
		Display=AccntNo_Name,
		AccountType,
		AccountCategoryID,
		IsSubAccnt,
		HasSub,
		subaccntid
		
	From BalanceSheet A
	Where A.subaccntid is NULL

Union All

	Select 
		Level=C.Level + 1,
		B.AccountID,
		Display=replicate('  ', C.Level) + AccntNo_Name,
		B.AccountType,
		B.AccountCategoryID,
		B.IsSubAccnt,
		B.HasSub,
		B.subaccntid
		
	From BalanceSheet B
	Inner Join AccountPart1 C
	On C.AccountID = B.subaccntid

Union All 

    Select
        D.*, 
		acc.MonthDate,
		coalesce(sum(Debit),0)-coalesce(SUM(Credit),0) as CurrentBalance
	from AccountPart1 D
	
	inner join (SELECT debit_accnt_ID AS AccountID, CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 < convert(nvarchar(11),@ToDate)
                                                        THEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
                                                        ELSE convert(nvarchar(11),@ToDate)
                                                        END AS MonthDate,sum(debit_amt) as Debit,
                                                        CAST(0 AS decimal(15,2)) AS Credit
        
				FROM tbl_account_transactions_detail
				WHERE (transdate > = convert(nvarchar(11),@FromDate) 
				AND transdate < DATEADD(dd,1,convert(nvarchar(11),@ToDate)))
				GROUP BY debit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1

				Union All

				SELECT credit_accnt_ID,CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 < convert(nvarchar(11),@ToDate) 
							                            THEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 
							                            ELSE convert(nvarchar(11),@ToDate) 
							                            END  AS MonthDate,0 as Debit,
                                                        sum(credit_amt) AS Credit
				
				FROM tbl_account_transactions_detail
				WHERE (transdate > = convert(nvarchar(11),@FromDate)
				AND transdate < DATEADD(dd,1,convert(nvarchar(11),@ToDate)))
				GROUP BY credit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
          ) acc
          
   ON acc.AccountID = D.AccountID
   GROUP BY D.Level,D.AccountID,D.Display,D.AccountType,D.AccountCategoryID,D.IsSubAccnt,D.HasSub,D.subaccntid,acc.MonthDate			
)

Select * 
From AccountPart1
Order By Level

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 09:53:04

its last part of CTE. as error suggests you cant use GROUP BY inside recursive part of CTE. what are you trying to achieve with last part?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-08 : 20:26:00

thank you visakh16!

here is the result that i want to achieve!



AccountID  AccntNo_Name           subaccntID   2011-11-30   2012-07-26
  383      ASSETS                     NULL        NULL         NULL
  384        Current Assets           383         NULL         NULL
  385          Checking/Savings       384         NULL         NULL
  386            Cash on Drawer       385        80,000.00     20.00
  386            Cash in Bank         385        60,000.00    -20.00
 .....................

The first part of the CTE generate the hierarchy view marked in color blue. And the last part of the CTE if you can remember from my last post it generates the balance of such account base on dynamic given @FromDate @ Todate marked in color red.


use MFR_Merchandise
go

declare @FromDate		as date
declare @ToDate			as date

set @FromDate='2011-11-23'
set @ToDate='2012-07-26'

; with BalanceSheet (AccountID, AccntNo_Name,AccountType, AccountCategoryID, IsSubAccnt,HasSub,subaccntid)
As (
	Select 
		AccountID,
		Display=AccntNo_Name,
		AccountType,
		AccountCategoryID,
		IsSubAccnt,
		HasSub,
		subaccntid
		
	From tbl_accountlist
	
), AccountPart1

AS
(
	Select 
		Level=1,
		AccountID,
		Display=AccntNo_Name,
		AccountType,
		AccountCategoryID,
		IsSubAccnt,
		HasSub,
		subaccntid
		
	From BalanceSheet A
	Where A.subaccntid is NULL

Union All

	Select 
		Level=C.Level + 1,
		B.AccountID,
		Display=replicate('  ', C.Level) + AccntNo_Name,
		B.AccountType,
		B.AccountCategoryID,
		B.IsSubAccnt,
		B.HasSub,
		B.subaccntid
		
	From BalanceSheet B
	Inner Join AccountPart1 C
	On C.AccountID = B.subaccntid

Union All 

    Select
        D.*, 
		acc.MonthDate,
		coalesce(sum(Debit),0)-coalesce(SUM(Credit),0) as CurrentBalance
	from AccountPart1 D
	
	inner join (SELECT debit_accnt_ID AS AccountID, CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 < convert(nvarchar(11),@ToDate)
                                                        THEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
                                                        ELSE convert(nvarchar(11),@ToDate)
                                                        END AS MonthDate,sum(debit_amt) as Debit,
                                                        CAST(0 AS decimal(15,2)) AS Credit
        
				FROM tbl_account_transactions_detail
				WHERE (transdate > = convert(nvarchar(11),@FromDate) 
				AND transdate < DATEADD(dd,1,convert(nvarchar(11),@ToDate)))
				GROUP BY debit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1

				Union All

				SELECT credit_accnt_ID,CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 < convert(nvarchar(11),@ToDate) 
							                            THEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 
							                            ELSE convert(nvarchar(11),@ToDate) 
							                            END  AS MonthDate,0 as Debit,
                                                        sum(credit_amt) AS Credit
				
				FROM tbl_account_transactions_detail
				WHERE (transdate > = convert(nvarchar(11),@FromDate)
				AND transdate < DATEADD(dd,1,convert(nvarchar(11),@ToDate)))
				GROUP BY credit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
          ) acc
          
   ON acc.AccountID = D.AccountID
   GROUP BY D.Level,D.AccountID,D.Display,D.AccountType,D.AccountCategoryID,D.IsSubAccnt,D.HasSub,D.subaccntid,acc.MonthDate			
)

Select * 
From AccountPart1
Order By Level

It's just maybe where I want to position the GROUP BY clause.

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-11 : 22:45:09

Good day! After a week of debugging I think I have now a running query. But I got error executing the pivot event. I want to pivot the sum of CurrentBalance for MonthDate in ([1],[2]....) CrossTab. When I uncomment the Pivot event I got an error:

Here is the Query:


use MFR_Merchandise
go

declare @MonthColumns	as nvarchar(max)
declare @SQLtext		as nvarchar(4000)		
declare @IterationDate	as date
declare @FromDate		as date
declare @ToDate			as date
declare @MonthDates		as Table(MonthsFilter date NOT NULL) 

set @FromDate='2011-11-23'
set @ToDate='2012-07-26'

while @FromDate < @ToDate
	begin
		insert into @MonthDates
		select dateadd(month, datediff(month, 0, @FromDate) + 1, -1)

		Set @FromDate = (select min(MonthsFilter) 
                         from   @MonthDates 
                         where  MonthsFilter > @FromDate) 
                         
        set @FromDate = DATEADD(dd, 1, @FromDate) 
	end
	
		update @MonthDates set MonthsFilter = @ToDate 
		where MonthsFilter = (select MAX(MonthsFilter) from @MonthDates)
	
		set @IterationDate = (select Min(MonthsFilter) 
                              from @MonthDates) 
		set @MonthColumns = N''

while (@IterationDate IS NOT NULL) 
	begin 
		set @MonthColumns = @MonthColumns + N', ' + 
			QUOTENAME(Cast(@IterationDate AS nvarchar(20))) 
				
		set @IterationDate = (select Min(MonthsFilter) 
							  from @MonthDates 
                              where MonthsFilter > @IterationDate) 
	end
		set @MonthColumns = Substring(@MonthColumns,2,Len(@MonthColumns)) 
		
		print @MonthColumns

--resetting @FromDate to its original passed value
set @FromDate='2011-11-23' 

; with BalanceSheet (AccountID, AccntNo_Name,AccountType, AccountCategoryID, IsSubAccnt,HasSub,ParentID)
As (
	Select 
		AccountID,
		Display=convert(varchar(1000),AccntNo_Name),
		AccountType,
		AccountCategoryID,
		IsSubAccnt,
		HasSub,
		subaccntid
		
	From tbl_accountlist
), AccountPart1

AS
(
Select 
		Level=1,
		AccountID,
		Display=convert(varchar(1000),AccntNo_Name),
		AccountType,
		AccountCategoryID,
		IsSubAccnt,
		HasSub,
		ParentID
		
From BalanceSheet A
Where A.ParentID is NULL

Union All

Select 
		Level=B.Level + 2,
		A.AccountID,
		Display=convert(varchar(1000),replicate('  ', B.Level) + AccntNo_Name),
		A.AccountType,
		A.AccountCategoryID,
		A.IsSubAccnt,
		A.HasSub,
		A.ParentID
		
	From BalanceSheet A
	
	Inner Join AccountPart1 B
	On B.AccountID = A.ParentID       
)

Select C.*,
MonthDate,
coalesce(sum(Debit),0)-coalesce(SUM(Credit),0) as CurrentBalance 
From AccountPart1 C

Left Join (SELECT debit_accnt_ID AS SubAccountID, CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 < convert(nvarchar(11),@ToDate)  
                                                        THEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
                                                        ELSE convert(nvarchar(11),@ToDate)
                                                        END AS MonthDate,sum(debit_amt) as Debit,
                                                        CAST(0 AS decimal(15,2)) AS Credit
        FROM tbl_account_transactions_detail
		WHERE (transdate > = convert(nvarchar(11),@FromDate) 
		AND transdate < DATEADD(dd,1,convert(nvarchar(11),@ToDate)))
        GROUP BY debit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1

        UNION ALL

        SELECT credit_accnt_ID,CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 < convert(nvarchar(11),@ToDate) 
							                            THEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 
							                            ELSE convert(nvarchar(11),@ToDate) 
							                            END  AS MonthDate,0 as Debit,
                                                        sum(credit_amt) AS Credit
        FROM tbl_account_transactions_detail
		WHERE (transdate > = convert(nvarchar(11),@FromDate) 
		AND transdate < DATEADD(dd,1,convert(nvarchar(11),@ToDate)))
        GROUP BY credit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
       )acc
       
ON acc.SubAccountID = C.AccountID

--Pivot (Sum(CurrentBalance) For Monthdate IN ([1],[2],[3])) CrossTab
GROUP BY Level,C.AccountID,acc.SubAccountID,C.Display,C.AccountType,C.AccountCategoryID,C.IsSubAccnt,C.HasSub,C.ParentID,Monthdate,Debit,Credit
     
ORDER BY Level

And I got this result without the Pivot:


Level   AccountID  Display                       ParentID  MonthDate    CurrentBalance
1          308      ASSETS                          NULL    NULL             0.0000
3          309         Current Assets               308     NULL             0.0000
5          311            Checking/Savings/         309     NULL             0.0000
7          275              Cash on Drawer          311    2011-11-30      80000000.0000
7          275              Cash on Drawer          311    2011-12-30        0.0000
7          275              Cash on Drawer          311    2012-01-30      -20000000.0000
7          276              Cash on Drawer_POS1     311    2012-02-30      -60000000.0000
7          277              Cash in Bank            311    2012-03-29        0.0000
7          278                 World Bank           277    2012-04-31      10000000.0000     
7          279                 Union Bank           277    2012-05-30      20000000.0000

Now I need to Pivot by sum of CurrentBalance but why is the Pivot cannot detect the CurrentBalance column alias? Whats wrong on the last part of the query?

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources