Good day. I have this running query that I want to pivot the sum of CurrentBalance where in Monthdate =[2011-11-30],[2012-12-31]...etc. But everytime I try to put the Pivot, it cannot detect the CurrentBalance column alias.
Here is my query so far.
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
GROUP BY Level,C.AccountID,acc.SubAccountID,C.Display,C.AccountType,C.AccountCategoryID,C.IsSubAccnt,C.HasSub,C.ParentID,Monthdate,Debit,Credit
--Pivot (Sum(CurrentBalance) For Monthdate IN ([2011-11-30],[2012-05-31],[2012-06-30],[2012-07-27])) CrossTab
ORDER BY Level
Whats wrong with my pivot? Where should I position it? When I uncomment it, the CurrentBalance column does not detected.
Thank you!
