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!

The problem is that a PIVOT clause can only provide aggregations on a base column, not on a calculated or aggregated column alias.

Try this. After line 98, add the clause "into #temp1" to push the results into a temp table. Then, after line 129, put the following:

select * from #temp1
Pivot (Sum(CurrentBalance) For Monthdate IN ([2011-11-30],[2012-05-31],[2012-06-30],[2012-07-27])) CrossTab

That should do the trick. I tested this on SQL2008. If you'd like to see my table defs and sample data, let me know and I'll post it.

Hope this helps. Good luck!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.