0

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!

Edited by blocker

2
Contributors
1
Reply
4
Views
5 Years
Discussion Span
Last Post by BitBlt
0

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!

Edited by BitBlt

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.