Click here to Skip to main content
15,919,422 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm Using Crystal Report 9
i have table xx
and Records
branch   number   val
1         50    200
5         2     150
1         1     200
3         4     300


i using group
and in Report footer i use running total sum
in sum i want to skip duplicate record like
branch   number   val
1         50      200
5         2       150
3         4       300
----------------------
total             650


What I have tried:

i tried
{number} = Previous ({number}) 
Posted
Updated 26-Apr-17 10:58am
v2
Comments
Maciej Los 26-Apr-17 16:44pm    
I do not see any "duplicates"! Why? The "number" field causes that branch no. 1 has two different records.
Please, be more specific and provide more details.

1 solution

Please, read my comment first. You have to modify your SQL statement to be able to get result as expected.

Take a look at below example:
SQL
DECLARE @tmp TABLE (branch INT, number INT, val INT)

INSERT INTO @tmp (branch, number, val)
VALUES(1, 50, 200),
(5, 2, 150),
(1, 1, 200),
(3, 4, 300)

SELECT DISTINCT [branch], MAX([number]) OVER (PARTITION BY [branch] ORDER BY number DESC) AS [number],  [val]
FROM @tmp

Result:
branch	number	val
1		50		200
3		4		300
5		2		150
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900