----------
Without distinct the result is:
311793 3:12
312184 3:13
312184 3:13
312373 3:04
Total: 12:42
.. but I want only values for distinct ID's to be added:
311793 3:12
312184 3:13
312373 3:04
Total 9:29
----------
Normally you would run some kind of distinct function on the dataset before passing to SSRS, as this would return the report you require.
If you needed all values to be shown but not to be summed then use something like below. Both will return the original rows (4), but allow the second and subsequent values to be converted to 0.
declare @SourceDatatable (OrderNumber int, Value money, Code char(1))
insert into @SourceData
SELECT 311793 AS OrderNumber, 3.12 AS Value, 'A' AS Code
UNION
SELECT 312184 AS OrderNumber, 3.13 AS Value, 'A' AS Code
UNION
SELECT 312184 AS OrderNumber, 3.13 AS Value, 'D' AS Code
UNION
SELECT 312373 AS OrderNumber, 3.04 AS Value, 'A' AS Code
select * from @SourceData;
--- Option 1 : SQL 2005+
with ComparedValues as (
select * , row_number() over (partition by OrderNumber order by Code) as RN
from @SourceData
)
select OrderNumber, Value, Code, case when RN=1 then Value else 0 end as ConvertedValue
from ComparedValues;
--- Option 1 : SQL 2012+
select OrderNumber, Value, Code,
case when OrderNumber = lag(OrderNumber,1,NULL) over
(partition by OrderNumber order by Code)
then 0 else Value end as ConvertedValue
from @SourceData;
Fitz