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)
 CTE displays repeated query results?

Author  Topic 

adbasanta
Posting Yak Master

120 Posts
Posted - 2014-01-06 : 11:05:21
Good day!

I just want a little help on why is the result of my cte keeps repeating several times? I've also notice that running Balance keeps displaying incorrect value?

Here is my cte:

USE [Financials]
GO
/****** Object:  StoredProcedure [dbo].[Balance_Sheet_Detail]    Script Date: 01/06/2014 21:21:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		None
-- Create date: <Create Date,,>
-- Description:	<CTE manipulation for displaying account details>
-- =============================================
ALTER PROCEDURE [dbo].[Balance_Sheet_Detail]
	-- Add the parameters for the stored procedure here
	@AccntNumber as varchar(10)
	--@fromdate as VARCHAR(50),
	--@todate as VARCHAR(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
;with BL_Detail as
(
	select	row_no = row_number() over (partition by [Account Number] order by [Date]),
	[Date],[Account Number],Accnt_Name,Debit,Credit,[Journal Entry Type],CustName,AccntTypeID
	from	
	(
		select	[Date], 
				[Account Number], 
				Accnt_Name=(SELECT [Account Name] from Account a
				            WHERE a.[Account Number]=@AccntNumber), 
		        Debit=(CASE WHEN [Debit or Credit]='Debit' THEN Amount ELSE 0 END),
			    Credit=(CASE WHEN [Debit or Credit]='Credit' THEN Amount ELSE 0 END),
				[Journal Entry Type],
				CustName =(SELECT [Last Name] + ', ' + [First Name] + ' ' + [Middle Name] from tbl_costumers l
		                   WHERE l.[ID No.]=[Customer ID]),
		        AccntTypeID=(SELECT [Account Type ID] from Account at
		                     WHERE at.[Account Number]=@AccntNumber)
		from	[Journal Entry] je
		where [Account Number]=@AccntNumber
	) t
)

select * FROM BL_Detail s

	cross apply
	(
		select	Balance=(CASE WHEN AccntTypeID=1 THEN isnull(Debit,0)-isnull(Credit,0)
						      WHEN AccntTypeID=2 THEN isnull(Credit,0)-isnull(Debit,0)
							  WHEN AccntTypeID=3 THEN isnull(Credit,0)-isnull(Debit,0)
							  WHEN AccntTypeID=4 THEN isnull(Credit,0)-isnull(Debit,0)
							  WHEN AccntTypeID=5 THEN isnull(Debit,0)-isnull(Credit,0) ELSE 0 END)
		from	BL_Detail x
		where x.[Account Number]=@AccntNumber
		group by row_no,AccntTypeID,Debit,Credit
	) r
END


And here here is the result:



It should only displays the result inside the red rectangle. Notice that it repeats showing several times.
And the Balance columns seems not displaying the correct running total base on AccntTypeID.

Thank you for helping!



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

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2014-01-06 : 11:51:59
Looks to me like the technique you used for "running balance" is not correct. First, to prove the point, test the statement outside the context of the stored procedure. Declare and set @accntNumber the run the statement with the CROSS APPLY code commented out. You should get the correct rows and then you know it is the cross apply that is messing you up. There are several techniques for generating a running total. Try a search on this site for plenty of good examples on how to do a running total. Let us know if you can't get it yourself and someone will post a potential solution - if you include some sample DDL/DML then it help ensure you get a working, tested solution.

Be One with the Optimizer
TG
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts
Posted - 2014-01-06 : 12:02:34
Hard to say as not sure exactly what you are trying to acheive.

Since you are grouping by several columns in r, you can get several records in r. Then with the Cross Apply, you will get repetition. Also there is nothing to say that this is a running total.

Try working with this simpler query first:

with BL_Detail as
	(
	select 1 RowNo, '2013-1-17' [Date], 1101 AccountNumber, 50000 Debit, 0 Credit  
	union all
	select 2, '2013-1-13', 1101, 0, 25000 
	union all
	select 2, '2013-1-13', 1101, 0, 30000 
	)
select * FROM BL_Detail s

	cross apply
	(
		select	Balance = isnull(Debit,0) - isnull(Credit,0)
		from	BL_Detail x
		where x.AccountNumber = 1101
		group by RowNo, Debit ,Credit
	) r
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2014-01-06 : 12:14:58
I would say that the simplest change/fix to your original query would be this:

	cross apply
	(
		select	Balance=(CASE WHEN AccntTypeID=1 THEN isnull(Debit,0)-isnull(Credit,0)
						      WHEN AccntTypeID=2 THEN isnull(Credit,0)-isnull(Debit,0)
							  WHEN AccntTypeID=3 THEN isnull(Credit,0)-isnull(Debit,0)
							  WHEN AccntTypeID=4 THEN isnull(Credit,0)-isnull(Debit,0)
							  WHEN AccntTypeID=5 THEN isnull(Debit,0)-isnull(Credit,0) ELSE 0 END)
		from	BL_Detail x
		where x.[Account Number]=@AccntNumber
              and    x.row_no <= s.row_no
              --group by RowNo, Debit ,Credit

	) r

This makes your technique what is referred to as a "triangular join". That is potentially very inefficient. But because you're only working with one account at a time it may be acceptable for you.

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2014-01-06 : 12:19:38
Using Denis_the_Thief's sample code my change seems to work. with two slight corrections (adding SUM and setting the third row to RowNo=3):

with BL_Detail as
	(
	select 1 RowNo, '2013-1-17' [Date], 1101 AccountNumber, 50000 Debit, 0 Credit  
	union all
	select 2, '2013-1-13', 1101, 0, 25000 
	union all
	select 3, '2013-1-13', 1101, 0, 30000 
	)
select * FROM BL_Detail s

	cross apply
	(
		select	Balance = sum(isnull(Debit,0) - isnull(Credit,0))
		from	BL_Detail x
		where x.AccountNumber = 1101
              and x.rowNo <= s.rowNo
	) r

OUTPUT:

RowNo       Date      AccountNumber Debit       Credit      Balance
----------- --------- ------------- ----------- ----------- -----------
1           2013-1-17 1101          50000       0           50000
2           2013-1-13 1101          0           25000       25000
3           2013-1-13 1101          0           30000       -5000



Be One with the Optimizer
TG
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts
Posted - 2014-01-06 : 12:55:32
Thank you TG, denis_the_thief!
Ive modified the query base on both of your suggestions..And it works as expected. Adding the sum function on cross apply and the where clause row_no evaluation. I ended up grouping also by AccntTypeID.

Here is the final code:
[CODE]
cross apply
(
select Balance=(CASE WHEN AccntTypeID=1 THEN sum(isnull(Debit,0)-isnull(Credit,0))
WHEN AccntTypeID=2 THEN sum(isnull(Credit,0)-isnull(Debit,0))
WHEN AccntTypeID=3 THEN sum(isnull(Credit,0)-isnull(Debit,0))
WHEN AccntTypeID=4 THEN sum(isnull(Credit,0)-isnull(Debit,0))
WHEN AccntTypeID=5 THEN sum(isnull(Debit,0)-isnull(Credit,0)) ELSE 0 END)
from BL_Detail x
where x.[Account Number]=@AccntNumber
and x.row_no <= s.row_no
group by AccntTypeID

) r
[/CODE]

Thank you very much..!

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

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2014-01-06 : 13:39:43
I think grouping by AccntTypeID is a mistake. I still think you only want one row per account number, right? Now perhaps it doesn't matter if all [journal entry] rows for a given accntNo are all the same AccntTypeID.

Be One with the Optimizer
TG
Go to Top of 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