SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 dynamic total from dynamic query..?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adbasanta
Posting Yak Master

120 Posts

Posted - 10/28/2013 :  05:12:55  Show Profile  Reply with Quote
Good day!

I have a running query that takes the total of an account base on given dates( from and to ). However Ive noticed that that total of the next succeeding month is only for that month which is not correct. It should also include the date from(fromdate value) the user has passed.

My Query below:

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


And here is the result with its table structure:

If you will look at the table structure the succeeding month total and so on(ex. 2013-11-30) for
Cash in Bank is supposed to be 200,000
Insurance is supposed to be 50,0000
Common Shares is supposed to be 50,000
Preferred Shares is supposed to be 100,000

It is the sum total of Amount of a given Account ID from start month to current rotation month base on the query.

The final result should be:


Thank you for helping!


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

Edited by - adbasanta on 10/28/2013 05:17:23

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/28/2013 :  06:57:16  Show Profile  Reply with Quote
I think what you need is a correlated subquery as in link below
http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 10/28/2013 :  19:07:49  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/28/2013 :  23:53:04  Show Profile  Reply with Quote
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


you may need to so that it works the way you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000