Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 query with dynamic date filter?

Author  Topic 

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-04 : 22:18:03
Good day!

I have a query which produces the total currentbalance of some accounts. Now there was a suggestion if I can add a filter by date, or by month, or by quarter, or by year depending on the user input. Here is mo query so far:

USE [MFR_Merchandise]
GO
/****** Object:  StoredProcedure [dbo].[Show_All_Accnts_byType]    Script Date: 07/05/2012 09:47:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Show_All_Accnts_byType]
@accntcategoryID as int,
@accntypeid as bigint,
@isaccnt_current as bit,
@fromdate as date,
@todate as date,
@viewtype as varchar(15)

AS
  BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  if(@accntypeid<>'') and (@accntcategoryID<>'')
	begin
		SELECT 
			al.AccountID,
			al.AccntNo_Name,
			al.AccountCategoryID,
			al.AccountType,
			al.AccountCategoryID,
			al.Accntstatus,
			al.IsSubAccnt,
			al.HasSub,
			accnttypename,
			coalesce(Debit,0)-coalesce(Credit,0) as currentbalance

		FROM tbl_accountlist al

		INNER JOIN tbl_accnt_type at
		ON at.accnttypename=(select at.accnttypename where at.accnttypeid=al.AccountType)

		outer apply
		(
			select sum(debit_amt) as Debit
			from tbl_account_transactions_detail
			where debit_accnt_ID=al.AccountID
		)DEBIT

		outer apply
		(
			select sum(credit_amt) as Credit
			from tbl_account_transactions_detail
			where credit_accnt_ID=al.AccountID
		)CREDIT

		
		WHERE (al.Accntstatus=1 and al.AccountCategoryID=@accntcategoryID and al.AccountType= @accntypeid and al.IsCurrent=@isaccnt_current and al.IsSubAccnt=0)-- and HasSub=1 or HasSub=0
		ORDER BY AccntNo ASC
    end



If @viewtype='By Month', I want to add a result base on @fromdate and @todate.Example, if @fromdate=11/21/2011 and @todate=01/24/2012 the result should produce by month but the date filter of each months will be base on the last day of each month except the last month which is 01/24/2012 as base on the example.

The same result will be given If @viewtype='By Quarter',@viewtype='By Year'..

Thank you guys for helping!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-04 : 23:55:21
seems like what you need is GROUP BY based on CASE WHEN to handle various parameter values

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

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-05 : 00:06:00
thank you visakh16.

But how can I extract the filter base on this date @fromdate=11/21/2011 and @todate=01/24/2012.

And also, how can I loop the query and filter in WHERE CLAUSE by months(if @viewtype='By Month'): Ex. November 30, 2011, December 31, 2012, January 31, 2012, February 29, 2012? And display the result by month base on this filters.
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-05 : 00:06:01
thank you visakh16.

But how can I extract the date filter base on @fromdate=11/21/2011 and @todate=01/24/2012.

And also, how can I loop the query and filter in WHERE CLAUSE by months(if @viewtype='By Month'): Ex. November 30, 2011, December 31, 2012, January 24, 2012? And display the result by month base on this filters.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-05 : 00:10:50
you can do like

WHERE datefield > = @fromdate 
AND datefield < DATEADD(dd,1,@todate)
...
GROUP BY CASE WHEN DATEADD(mm,DATEDIFF(mm,0,datefield)+1,0)-1 > @todate THEN @todate ELSE DATEADD(mm,DATEDIFF(mm,0,datefield)+1,0)-1 END


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

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-05 : 06:07:04
Ive modified the query but its does not show any result base on the dates.

Here's what Ive got so far:

use MFR_Merchandise
go

declare @fromdate as date
declare @todate as date

set @fromdate='11-01-2011'
set @todate='02-24-2012'

SELECT 
			al.AccountID,
			al.AccntNo_Name,
			al.AccountCategoryID,
			al.AccountType,
			al.AccountCategoryID,
			al.Accntstatus,
			al.IsSubAccnt,
			al.HasSub,
			accnttypename,
			coalesce(Debit,0)-coalesce(Credit,0) as currentbalance

		FROM tbl_accountlist al
		
		INNER JOIN tbl_accnt_type at
		ON at.accnttypename=(select at.accnttypename where at.accnttypeid=al.AccountType)
		
		outer apply
		(
			select sum(debit_amt) as Debit
			from tbl_account_transactions_detail
			where debit_accnt_ID=al.AccountID and transdate>=@fromdate and transdate < DATEADD(dd,1,@todate)
			GROUP BY CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 > @todate THEN @todate ELSE DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 END  
		)DEBIT

		outer apply
		(
			select sum(credit_amt) as Credit
			from tbl_account_transactions_detail
			where credit_accnt_ID=al.AccountID and transdate>=@fromdate and transdate < DATEADD(dd,1,@todate)
			GROUP BY CASE WHEN DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 > @todate THEN @todate ELSE DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 END  
		)CREDIT

The sample result must be:

Account Name  11/30/2011   12/31/2012    1/31/2012    2/24/2012
Rural Bank       56,306.00     71,456.00       56,214.00     20,404.00
Trust Bank       56,455.00     71,222.00       56,333.00     20,777.00
Cash on Hand     56,633.00     71,885.00       56,885.00     20,743.00
Cash in Bank     -56,12.00     31,456.00       56,222.00     80,404.00
Petty Cash Fund  10,307.00     25,459.00       85,219.00     79,804.00

Can this be possible? What should I modify in the query?
Thank you for helping!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-05 : 10:01:49
you should be adding GROUP BY to main query and not inside subqueries and use PIVOT for pivotting them to columns

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

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-06 : 20:51:36
Thank you visakh16.

I have modified the query as below, but I got this error:
Msg 8120, Level 16, State 1, Line 28
Column 'tbl_account_transactions_detail.transdate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


And also you've mentioned PIVOT but I always got error when I use it. Which in the query should I put it?


USE [MFR_Merchandise]
GO
/****** Object:  StoredProcedure [dbo].[Show_All_Accnts_byType]    Script Date: 07/07/2012 07:46:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

declare @fromdate as date
declare @todate as date

set @fromdate='11-01-11'
set @todate='07-30-12'

		SELECT 
			al.AccountID,
			al.AccntNo_Name,
			al.AccountCategoryID,
			al.AccountType,
			al.AccountCategoryID,
			al.Accntstatus,
			al.IsSubAccnt,
			al.HasSub,
			Debit,Credit,
			accnttypename,
			coalesce(Debit,0)-coalesce(Credit,0) as currentbalance

		FROM tbl_accountlist al
		
		INNER JOIN tbl_accnt_type at
		ON at.accnttypename=(select at.accnttypename where at.accnttypeid=al.AccountType)
	
		outer apply
		(
			select transdate,sum(debit_amt) as Debit
			from tbl_account_transactions_detail
			where debit_accnt_ID=al.AccountID and (transdate > = @fromdate 
			AND transdate < DATEADD(dd,1,@todate))
			)DEBIT

		outer apply
		(
			select transdate,sum(credit_amt) as Credit
			from tbl_account_transactions_detail
			where credit_accnt_ID=al.AccountID and (transdate > = @fromdate 
			AND transdate < DATEADD(dd,1,@todate))
		)CREDIT
		
GROUP BY CASE WHEN DATEADD(mm,DATEDIFF(mm,0,Debit.transdate)+1,0)-1 > @todate THEN @todate 
              ELSE DATEADD(mm,DATEDIFF(mm,0,Debit.transdate)+1,0)-1 END

Thank you!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-06 : 21:21:53
you cant return the individual field values unless you apply group by. I think it would be better if you can post some sample data to illustrate what exactly you're trying to achieve.

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

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-07 : 02:42:10
I already have this result base on my query:

AccntNo_Name  currentbalance
Rural Bank       56,306.00     
Trust Bank       56,455.00     
Cash on Hand     56,633.00     
Cash in Bank     -56,12.00     
Petty Cash Fund  10,307.00

Now the user wants to add a date filter.
Ive setup a variable called @fromdate and @todate and @viewtype.

Example If @viewtype='By Month' and @fromdate='11-01-2011' and @todate='02/24/2012', the result will be group by month. So there must be a curremt balance base on that months. Base on the example of @fromdate and @todate value, the result must show currentbalance of each accounts like this:


Account Name     11/30/2011    12/31/2012      1/31/2012     2/24/2012
Rural Bank       56,306.00     71,456.00       56,214.00     20,404.00
Trust Bank       56,455.00     71,222.00       56,333.00     20,777.00
Cash on Hand     56,633.00     71,885.00       56,885.00     20,743.00
Cash in Bank     -56,12.00     31,456.00       56,222.00     80,404.00
Petty Cash Fund  10,307.00     25,459.00       85,219.00     79,804.00


Notice that the result date must be filter automatically to the last date of that month except the last month which will be base on @todate value.

This is what I want to achieve, and I hope we'll comeup with a solution.

Thank you!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-07 : 17:28:44
[code]
USE [MFR_Merchandise]
GO
/****** Object: StoredProcedure [dbo].[Show_All_Accnts_byType] Script Date: 07/07/2012 07:46:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

declare @fromdate as date
declare @todate as date

set @fromdate='11-01-11'
set @todate='07-30-12'
;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY al.AccountID,al.AccntNo_Name ORDER BY MonthDate) AS Rn,
al.AccountID,
al.AccntNo_Name,
acc.MonthDate,
coalesce(sum(Debit),0)-coalesce(SUM(Credit),0) as currentbalance
FROM tbl_accountlist al
INNER JOIN (SELECT debit_accnt_ID AS AccountID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 AS MonthDate,sum(debit_amt) as Debit,
CAST(0 AS decimal(15,2)) AS Credit
FROM tbl_account_transactions_detail
where (transdate > = @fromdate
AND transdate < DATEADD(dd,1,@todate))
GROUP BY debit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1

UNION ALL

SELECT credit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 AS MonthDate,0 as Debit,
sum(credit_amt) AS Credit
FROM tbl_account_transactions_detail
where (transdate > = @fromdate
AND transdate < DATEADD(dd,1,@todate))
GROUP BY credit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
)acc
ON acc.AccountID = al.AccountID
GROUP BY al.AccountID,al.AccntNo_Name,MonthDate
)

SELECT *
FROM CTE
PIVOT (SUM(CurrentBalance) FOR Rn IN ([1],[2],[3],...))p
[/code]

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

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-08 : 20:54:08
thank you visakh16.

I'm almost there.

Ive got this result when I execute your query.

Account Name  MonthDate          1               2        3
Rural Bank       12/31/2011     71,456.00       56,214.00    20,404.00
Trust Bank       11/30/2011     71,222.00       56,333.00    20,777.00
Cash on Hand     01/31/2012     71,885.00       56,885.00    20,743.00
Cash in Bank     02/29/2012     31,456.00       56,222.00    80,404.00
Petty Cash Fund  03/30/2012     25,459.00       85,219.00    79,804.00

Maybe a little modification in this area to pivot the report by date.

SELECT *
FROM CTE
PIVOT (SUM(CurrentBalance) FOR Rn IN ([1],[2],[3]))p

Instead of grouping it by date, Is it possible to pivot it by date base on @fromdate and @todate to come-up a result like below? Moving the filter at the end of the day of each month except the last month.


Account Name   11/30/2011  12/31/2012    1/31/2012   02/24/2012
Rural Bank       56,306.00     71,456.00       56,214.00     20,404.00
Trust Bank       56,455.00     71,222.00       56,333.00     20,777.00
Cash on Hand     56,633.00     71,885.00       56,885.00     20,743.00
Cash in Bank     -56,12.00     31,456.00       56,222.00     80,404.00
Petty Cash Fund  10,307.00     25,459.00       85,219.00     79,804.00


Thank you!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-08 : 23:03:39
[code]
USE [MFR_Merchandise]
GO
/****** Object: StoredProcedure [dbo].[Show_All_Accnts_byType] Script Date: 07/07/2012 07:46:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

declare @fromdate as date
declare @todate as date

set @fromdate='11-01-11'
set @todate='07-30-12'
;With CTE
AS
(
SELECT al.AccountID,
al.AccntNo_Name,
acc.MonthDate,
coalesce(sum(Debit),0)-coalesce(SUM(Credit),0) as currentbalance
FROM tbl_accountlist al
INNER JOIN (SELECT debit_accnt_ID AS AccountID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 AS MonthDate,sum(debit_amt) as Debit,
CAST(0 AS decimal(15,2)) AS Credit
FROM tbl_account_transactions_detail
where (transdate > = @fromdate
AND transdate < DATEADD(dd,1,@todate))
GROUP BY debit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1

UNION ALL

SELECT credit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 AS MonthDate,0 as Debit,
sum(credit_amt) AS Credit
FROM tbl_account_transactions_detail
where (transdate > = @fromdate
AND transdate < DATEADD(dd,1,@todate))
GROUP BY credit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1
)acc
ON acc.AccountID = al.AccountID
GROUP BY al.AccountID,al.AccntNo_Name,MonthDate
)

SELECT *
FROM CTE
PIVOT (SUM(CurrentBalance) FOR Rn IN ([11/30/2011],[12/31/2012],[1/31/2012],[02/24/2012]))p
[/code]

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

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-08 : 23:10:23
Thank you visakh16.

But what if the user changed the date filter value? Can we make it dynamic?

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-09 : 00:12:14
I'm planning I while loop to process the expected result. But I hope Pivot can make it..

I'm hoping for a pivot or any solution to get the expected result.


Thank you.

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-09 : 00:40:06
quote:
Originally posted by adbasanta

Thank you visakh16.

But what if the user changed the date filter value? Can we make it dynamic?

Thank you!

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

yep
see

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

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

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-09 : 01:22:23
thank you visakh16.

Ive also look at this site that uses dynamic sql pivoting.
http://www.sqlusa.com/bestpractices/dynamicsql/

Ill post back here when I came up a solution base on this examples..

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-09 : 09:58:29
ok...you're welcome

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

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-11 : 00:45:10
Hi visakh16, and to all SQLTeam contributors.

I have now the query that is 80% complete. I need a help on how to incorporate the query above to this query below. Preferably in @Sqltext variable. Here it is.

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='11-02-2011'
set @ToDate='07-24-2012'

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
		
		/* Result for @MonthColumns
		[2011-11-30], [2011-12-31], [2012-01-31], [2012-02-29], [2012-03-31], [2012-04-30], [2012-05-31], [2012-06-30], [2012-07-24] 
		*/ 

		SET @SQLtext = N'(QUERY MUST BE APPLIED HERE]) as MonthsHeader  
				PIVOT (SUM(CurrentBalance) FOR MonthColumns IN(' + @MonthColumns + N'))  
			        AS Pvt ORDER BY 1'
					   
		EXEC Sp_executesql   @SQLtext 
		GO

Hoping for a brighter solution. Thank you guys for helping.


-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-07-11 : 10:16:49
put the last suggested query into a intermediate table and just use select * from table inside dynamic string

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

adbasanta
Posting Yak Master

120 Posts
Posted - 2012-07-15 : 09:36:57
Hi visakh16, and to all SQLTeam contributors.

I have been longing to finish this topic as soon as possible, Now I only got one more problem executing the dynamic query in a variable. I get this error: "Msg 402, Level 16, State 1, Line 61
The data types nvarchar and date are incompatible in the add operator." When I click it, it points me to the code which I marked red. I maybe missing something for date. But I don't know what it is.

Here is the code 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='11-02-2011'
set @ToDate='07-24-2012'

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
		
		/* Result for @MonthColumns
		[2011-11-30], [2011-12-31], [2012-01-31], [2012-02-29], [2012-03-31], [2012-04-30], [2012-05-31], [2012-06-30], [2012-07-24] 
		*/ 
	
set @SQLtext=N';With CTE

		AS
		(
			SELECT  ROW_NUMBER() OVER (PARTITION BY al.AccountID,al.AccntNo_Name ORDER BY MonthDate) AS Rn,
			    al.AccountID,
			    al.AccntNo_Name,
			    acc.MonthDate,
			    coalesce(sum(Debit),0)-coalesce(SUM(Credit),0) as currentbalance
			
			FROM tbl_accountlist al
                        INNER JOIN (SELECT debit_accnt_ID AS AccountID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 AS MonthDate,sum(debit_amt) as Debit,
                            CAST(0 AS decimal(15,2)) AS Credit
                            FROM tbl_account_transactions_detail
			    where (transdate > =' + @FromDate +  ' 
			    AND transdate < DATEADD(dd,1, ' + @ToDate + '))
                            GROUP BY debit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1

                            UNION ALL

                            SELECT credit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1 AS MonthDate,0 as Debit,
                            sum(credit_amt) AS Credit
                            FROM tbl_account_transactions_detail
			    where (transdate > = ' + @FromDate +  ' 
			    AND transdate < DATEADD(dd,1,' + @ToDate + '))
                            GROUP BY credit_accnt_ID,DATEADD(mm,DATEDIFF(mm,0,transdate)+1,0)-1)acc
                           
                            ON acc.AccountID = al.AccountID
                            GROUP BY al.AccountID,al.AccntNo_Name,MonthDate
		)

			    SELECT *
			    FROM CTE
			    PIVOT (SUM(currentbalance) FOR Rn IN (' + @MonthColumns + N'))p'
				
EXEC Sp_executesql @SQLtext 
		
GO

Thank you for helping!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site