Here is the statement that I put together, but I am still getting duplicate VIN #s. Any suggestions would be greatly appreciated!
select pcard#, ccname, pplate, left(pvin#,17)
from crbs.crpcrd01 as c1
join crbs.crpcus01 on pcust# = ccust#
where psflag = 'A' and timestamp(pudate,putime) =
(select max(timestamp(pudate,putime))
from crbs.crpcrd01 as c2
where left(c2.pvin#,17) = left(c1.pvin#,17))
Please post sample data and expected result set.
Sorry...forgot that piece!
Here is what I am getting:
PCARD# CCNAME PPLATE VIN#
000775 Radio Cab HILL 1G1BL537214R13003
000782 Radio Cab ORESTE 2FAFP71WOXX145347
000784 Radio Cab FOX 1G1BL5378PR133755
000790 Broadway Cab Company MALIAA 2FALP7413SX205550
000802 Radio Cab HEYWOO 2FALP71W5TX170922
000803 Radio Cab GALLAG 2FALP71W5TX170922
000829 Broadway Cab Company CHMILY 2FAFP71W0WX129177
000840 Broadway Cab Company YOHANN 2FALP71W6TX146631
As you can see there are duplicates for the VIN#...I only want to get one distinct VIN. Hope that makes sense?
Which VIN do you want?select yc.*
from ( -- yc = Your Code (Plus a row number Grouped by VIN Ordered by something?)
select pcard#, ccname, pplate, left(pvin#,17) ,
row_number() over (partition by [VIN#] order by [PCARD#]) rn -- Order by ???
from crbs.crpcrd01 as c1
join crbs.crpcus01 on pcust# = ccust#
where psflag = 'A' and timestamp(pudate,putime) =
(select max(timestamp(pudate,putime))
from crbs.crpcrd01 as c2
where left(c2.pvin#,17) = left(c1.pvin#,17))
) yc
where
rn = 1
Either one...in some instances I actually get 5 of the records with the same VIN but only need any one of them. Thank you for the reply!
The code, as is, should suffice. If at some point you want to determine the outcome, just change the ORDER BY clause in the ROW_NUMBER function.