>>I looked at your SQL and I can not figure this out - you're doing something completely strange.
>>
>>Looks like all you need is to use ROW_NUMBER() approach to get row with latest datetime per each date and other criteria. But I can not exactly understand your problem. I suggest you to post DDL of your both tables, your current input and desired output - this is what I always try to request in T-SQL forum in MSDN where I am the top answerer and a moderator.
>
>Version contains records such as
>
>2013-01-16 00:08:04.000
>2013-01-16 00:07:57.000
>2013-01-16 00:07:56.000
>2013-01-15 23:04:44.000
>2013-01-15 23:04:44.000
>2013-01-10 00:04:31.000
>2013-01-10 00:02:41.000
>2013-01-10 00:02:41.000
>2013-01-10 00:02:41.000
>2013-01-10 00:02:40.000
>2013-01-10 00:02:40.000
>2013-01-10 00:02:40.000
>
>VersionProduction contains records such as
>
>2013-01-16 00:00:00.000
>2013-01-15 00:00:00.000
>2013-01-10 00:00:00.000
>
>So, as you can see, for each record in VersionProduction, we may have one or many related records for the same day in Version. The goal is to plug Version.NoVersionProduction the related primary key.
>
>This is a one shop script. As VersionProduction table did not exist before and we are creating a parent to all those records in Version now for the same day.
;with cte as (select NoVersionProduction, cast(Version.[Date] as Date) as [Date],
row_number() over (partition by cast(Version.[Date] as Date) ORDER BY Version.[Date] DESC) as Rn
from dbo.Version)
select NoVersionProduction, [Date]
from cte where Rn = 1
If it's not broken, fix it until it is.
My Blog