Why this two query returns diffrent result??

Question

DECLARE @fyId int=1
;WITH t1 AS (

	SELECT 
		t1.BankId,
		t1.Date,
		CASE WHEN t1.InOutType=1 THEN t1.Price ELSE 0 END AS Positive,
		CASE WHEN t1.InOutType=2 THEN t1.Price ELSE 0 END AS Negative
	FROM rpt.v_BankBilling t1
	WHERE t1.FyId=@fyId
)
SELECT 
	tmp.BankId,
	tmp.Date,
	SUM(tmp.Positive-tmp.Negative) Remain 
FROM
	(SELECT 
		_t1.BankId,
		_t1.Date,
		_t2.Positive,
		_t2.Negative 
	FROM t1 _t1
		INNER JOIN t1 _t2 ON _t1.BankId=_t2.BankId AND _t2.Date<=_t1.Date
	GROUP BY _t1.BankId,_t1.Date,_t2.Negative,_t2.Positive) tmp
GROUP BY tmp.BankId,tmp.Date
ORDER BY tmp.BankId,tmp.Date

DECLARE @fyId int=1
;WITH t1 AS (

	SELECT 
		t1.BankId,
		t1.Date,
		CASE WHEN t1.InOutType=1 THEN t1.Price ELSE 0 END AS Positive,
		CASE WHEN t1.InOutType=2 THEN t1.Price ELSE 0 END AS Negative
	FROM rpt.v_BankBilling t1
	WHERE t1.FyId=@fyId
)
SELECT 
	_t1.BankId,
	_t1.Date,
	SUM(_t2.Positive-_t2.Negative ) Remain
FROM t1 _t1
	INNER JOIN t1 _t2 ON _t1.BankId=_t2.BankId AND _t2.Date<=_t1.Date
GROUP BY _t1.BankId,_t1.Date

Note that fisrt query return TRUE result

---

C# Is My World

Answer 1

Your two queries are different:

First query groups BankID, Date, Positive, and Negative columns in the derived table and again groups BakID, and Date column in the outer query

The second query has only one grouping i.e; with BankID, and Date column.

If you have duplicates in Positive and Negative values for a BankID, Date column grouping then, you will get adifferent results with the two queries: Just have a look at the below script. Hopw the example will explain the difference:

DECLARE @Test TABLE
(
BankID		INT,
ADate		DATETIME,
Positive	INT,
Negative	INT
)
INSERT INTO @Test VALUES (1, GETDATE(), 10, 5)
INSERT INTO @Test VALUES (1, GETDATE(), 10, 5)
INSERT INTO @Test VALUES (1, GETDATE(), 10, 5)
INSERT INTO @Test VALUES (1, GETDATE(), 20, 10)
INSERT INTO @Test VALUES (1, GETDATE(), 20, 10)
-- #1
SELECT
	T.BankID, T.ADate,
	SUM(T.Positive - T.Negative) AS Remaining
FROM
	(
	SELECT BankID, ADate, Positive, Negative FROM @Test
	GROUP BY BankID, ADate, Positive, Negative
	) AS T
GROUP BY
	 BankID, ADate
	 
-- #2
SELECT
	BankID, ADate,
	SUM(Positive - Negative) AS Remaining
FROM 
	@Test
GROUP BY
	BankID, ADate

---

Krishnakumar S

Answer 2

Your first query has a GROUP BY clause on the inner query that includes the Positive and Negative fields.  This would remove duplicates across the combination of those values before you perform a second aggregation in the outer query. 

Your second query performs a single aggregation, so may include some records that would have been removed in the first query.

I am surprised your first query is the correct one though, as the very fact that it removes duplicates on the inner query would suggest it is removing values you actually want to keep and include.

Answer 3

The other response are correct, but I like to highlight one point they seem to have missed. You have the condition:

  ON _t1.BankId=_t2.BankId AND _t2.Date<=_t1.Date

The condition on <= will produce a lot of duplicate rows. Say that there are rows in rtp.v_BankBilling for all days in January. Then you will get rows with t2.Date = 1 and t1.Date from the 1st to the 31st. For t2.Date you will get rows from the 2nd to the 31st and so on. By adding GROUP BY (which should have been DISTINCT) you get rid of these duplicates in the first query. In the second query you don't and you add the same value multiple times.

But what you do really want to achieve? And which version of SQL Server do you have?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

yeees, my two queries are wrong result. how can I write true query??

I want summarize Positive and Negative for each BANK and each DATE PLUS last days ?

THANK YOU

---

C# Is My World

Answer 5

yeees, my two queries are wrong result. how can I write true query??

I want summarize Positive and Negative for each BANK and each DATE PLUS last days ?

SQLServer 2008

THANK YOU

---

C# Is My World

Answer 6

There is probably something hidden there that I don't see, because from the queries you have posteed, I don't really ses a need to separate positive and negative value.

For this type of question, it is often a good idea to post:

1) CREATE TABLE statement for your table, preferably simplified to focus on the problem. Don't forget to indicate keys.
2) INSERT statements with sample data.
3) The expected result given the sample.
4) A short description of the business problem you are trying to solve.

Without this, we cannot really offer more than guesses. Also, with the script, we can easily copy and paste to a query window to develop a tested solution.

---

Erland Sommarskog, SQL Server MVP, [e-mail]