Hi everyone, :)
I have a Subject Query that is returning duplicate records based on a Car ID.
Unfortunately, in my Subject Query, the following statement results to:
select CAR ID, REPAIR DATE, STATUS
from CARS
CAR ID REPAIR DATE STATUS
11111 10/25/1995 Fixed
11111 10/19/1995 NEEDS REPAIR
Even after I use the following approaches, I'm still getting dups:
select DISTINCT CAR ID, REPAIR DATE, STATUS
from CARS
I'm trying to get the oldest record from the above for this Subject Q.
11111 10/19/1995 NEEDS REPAIR
But no luck!
Any help on this, I'll greatly appreciate it.
Thx in advance! :( :(
This one is resolved!!! :) :)
Jolly good. Are you at liberty to explain the solution for those who might be curious?
Sure, Lynn actually provided suggestions also :)
1) Created the Data Item as a part of the Report Query.
2) Added Case Statement shown below,
case
when [View].[CAR].[CD] = '9'
then [View].[CAR].[JAGUAR_AUTO_REC_ID]
else null
end
3) Established the Aggregation Func. to Count for the Data Item.
The Report now shows the correct counts as a part of the Crosstab.
Thx for all Lynn's and everybody's help.
Hope this helps! 8)