Extract latest date and time

Question

Extract latest date and time

jn93 671

Hi lets say I have the data like below. Based on the desired output, how can I extract latest date and time based on the primary key of UKEY and PACODE? I'm using SSIS, please let me know of the solution. If there is solution using SQL code, also could be good since I'm going to transfer data from sql to another sql db, below in txt file is just for example. Thanks and Regards.

![251357-image.png ]1

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

ZoeHui-MSFT 41,551

Hi @jn93 ,

You may try with TSQL code to do that easily. It will be a little hard to do that in SSIS.

 select ukey,pacpde,date,time from (  
     select *,row_number() over(partition by ukey,pacpde order by date desc,time desc) as rn  
     from  
         test1018  
 ) t  
 where t.rn = 1

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

jn93 671 Reputation points

2022-10-18T06:46:06.873+00:00

Hi @ZoeHui-MSFT , thanks for the answer. It is working great. Since for real scenario, I got many column to select, is there any simple way to write code by select * except rn for above?
ZoeHui-MSFT 41,551 Reputation points

2022-10-18T06:57:53.977+00:00

Hi @jn93 ,
Fully understand your issue, what I do is copy the columns from 'SELECT TOP (1000) rows' like shown below.
jn93 671 Reputation points

2022-10-18T07:20:47.5+00:00

Thank you @ZoeHui-MSFT

Share via

Extract latest date and time

0 additional answers

Your answer