| Author |
 Topic  |
|
|
adbasanta
Posting Yak Master
108 Posts |
 Posted - 07/22/2012 : 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 |
Edited by - adbasanta on 07/22/2012 10:49:25
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 07/22/2012 : 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
108 Posts |
Posted - 07/23/2012 : 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 |
Edited by - adbasanta on 07/23/2012 05:15:52 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 08/05/2012 : 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 |
Edited by - adbasanta on 08/05/2012 21:22:06 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 08/05/2012 : 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
108 Posts |
Posted - 08/05/2012 : 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 |
Edited by - adbasanta on 08/05/2012 21:26:49 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 08/05/2012 : 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 |
Edited by - adbasanta on 08/05/2012 22:08:56 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 08/05/2012 : 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 |
Edited by - adbasanta on 08/06/2012 00:09:56 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 08/07/2012 : 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 |
Edited by - adbasanta on 08/07/2012 21:09:00 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 08/08/2012 : 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
108 Posts |
Posted - 08/08/2012 : 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 |
Edited by - adbasanta on 08/08/2012 20:35:34 |
|
|
|
adbasanta
Posting Yak Master
108 Posts |
Posted - 08/11/2012 : 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 |
Edited by - adbasanta on 08/11/2012 23:04:57 |
|
|
| |
Topic |
|
|
|
Financial Reporting(Balance Sheet, etc.) ?
