use Financials
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) 
declare @Debit			as nvarchar(50)
declare @Credit			as nvarchar(50)

set @FromDate='2013-10-26'
set @ToDate='2013-12-25'

set @Debit='Debit'
set @Credit='Credit'

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='2013-10-26' 
	
set @SQLtext=N';With CTE
AS
(
		SELECT al.[Account Number],
		       al.[Account Name],
		       acc.MonthDate,
		       coalesce(sum(Debit),0)-coalesce(SUM(Credit),0) as currentbalance
		FROM Account al
                INNER JOIN (SELECT [Debit Or Credit] as Debit_Credit,[Account Number] AS AccountID,CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[Date])+1,0)-1 < ''' + convert(nvarchar(11),@ToDate) + ''' THEN DATEADD(mm,DATEDIFF(mm,0,[Date])+1,0)-1 ELSE ''' + convert(nvarchar(11),@ToDate) + ''' END  AS MonthDate,
                SUM(Amount) as Debit,CAST(0 AS decimal(15,2)) AS Credit
                FROM [Journal Entry]
			    WHERE ([Date] > = ''' + convert(nvarchar(11),@FromDate) + ''' 
			    AND [Date] < DATEADD(dd,1,''' + convert(nvarchar(11),@ToDate) + '''))
			    AND [Debit Or Credit]='''+@Debit+'''
                GROUP BY [Debit Or Credit],[Account Number],DATEADD(mm,DATEDIFF(mm,0,[Date])+1,0)-1

                UNION ALL

                SELECT [Debit Or Credit],[Account Number],CASE WHEN DATEADD(mm,DATEDIFF(mm,0,[Date])+1,0)-1 < ''' + convert(nvarchar(11),@ToDate) + ''' THEN DATEADD(mm,DATEDIFF(mm,0,[Date])+1,0)-1 ELSE ''' + convert(nvarchar(11),@ToDate) + ''' END  AS MonthDate,
                SUM(Amount) AS Credit,CAST(0 AS decimal(15,2)) AS Debit
                FROM [Journal Entry]
			    WHERE ([Date] > = ''' + convert(nvarchar(11),@FromDate) + ''' 
			    AND [Date] < DATEADD(dd,1,''' + convert(nvarchar(11),@ToDate) + '''))
			    AND [Debit Or Credit]='''+@Credit+'''
                GROUP BY [Debit Or Credit],[Account Number],DATEADD(mm,DATEDIFF(mm,0,[Date])+1,0)-1
        )acc
       ON acc.AccountID = al.[Account Number]
       GROUP BY al.[Account Number], al.[Account Name],Debit_Credit,MonthDate
)

SELECT *
FROM CTE
PIVOT (SUM(currentbalance) FOR Monthdate IN (' + @MonthColumns + N'))p'

print @SQLtext				
EXEC Sp_executesql @SQLtext 
		
GO

quote:

---

Originally posted by adbasanta

thank you visah16!

The link is very informative and useful. But if I Will use cross apply, that means I need to rewrite the query again back to top?

thank you!

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

---