Get the distinct records which having same date with different time stamp.

• kbhanu15

  SSCarpal Tunnel

  Points: 4498

  More actions

  March 19, 2019 at 1:13 am

  #409707

  Hi Team,
  Need some on the below requirement.

  I have the table and data like below.
  create table trend
  (
  keyinstn INT,
  ratingdate datetime,
  rating varchar(10))

  insert into trend VALUES
  (4000193,'2009-03-19 02:59:19.943','A+'),
  (4000193,'2018-10-15 09:42:08.000','AA'),
  (4000193,'2018-10-19 11:22:16.000','BB'),
  (4000193,'2018-11-01 04:30:56.000','B'),
  (4000193,'2018-11-01 04:15:11.000','A')

  Existing Data:
  select * from trend

  keyinstn    ratingdate                            rating
  4000193    2009-03-19 02:59:19.943    A+
  4000193    2018-10-15 09:42:08.000    AA
  4000193    2018-10-19 11:22:16.000    BB
  4000193    2018-11-01 04:30:56.000    B
  4000193    2018-11-01 04:15:11.000    A

  ExpectedData:
  Note :  remove the  records which having same date and keep the latest date record.
  select * from trend

  keyinstn    ratingdate                            rating
  4000193    2009-03-19 02:59:19.943    A+
  4000193    2018-10-15 09:42:08.000    AA
  4000193    2018-10-19 11:22:16.000    BB
  4000193    2018-11-01 04:30:56.000    B

  Thanks
  Bhanu

• kbhanu15

  SSCarpal Tunnel

  Points: 4498

  More actions

  March 19, 2019 at 2:35 am

  #2024867

  Hi Team,

  Support if i take only date in this table.
  is it possible to get the expected output.
  please help.

  create table trend1
  (
  keyinstn INT,
  ratingdate date,
  rating varchar(10))

  insert into trend1 VALUES
  (4000193,'2009-03-19','A+'),
  (4000193,'2018-10-15','AA'),
  (4000193,'2018-10-19','BB'),
  (4000193,'2018-11-01','B'),
  (4000193,'2018-11-01','A')

  --Existing Data:
  select * from trend1

  keyinstn ratingdate rating
  4000193 2009-03-19 A+
  4000193 2018-10-15 AA
  4000193 2018-10-19 BB
  4000193 2018-11-01 B
  4000193 2018-11-01 A

  ExpectedData:
  Note : remove the records which having same date and keep the latest date record.
  select * from trend1

  keyinstn ratingdate rating
  4000193 2009-03-19 A+
  4000193 2018-10-15 AA
  4000193 2018-10-19 BB
  4000193 2018-11-01 B

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  March 19, 2019 at 7:41 am

  #2024893

  Here's one way to do it:

  ;with cte as
(
    select distinct t.keyinstn,convert(date,ratingdate) ratingdate1
      from trend t
)
select c.keyinstn, x.ratingdate, x.rating
  from cte c
 cross apply(select top(1) ratingdate,rating
               from trend t
              where t.keyinstn = c.keyinstn
                and convert(date,t.ratingdate)=c.ratingdate1
              order by t.ratingdate desc) x
  

• kbhanu15

  SSCarpal Tunnel

  Points: 4498

  More actions

  March 19, 2019 at 7:46 am

  #2024895

  thank you so much.

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  March 19, 2019 at 7:46 am

  #2024897

  Here's another way to do it:
  ;with cte as
(
    select t.keyinstn,ratingdate, rating,
           ROW_NUMBER() OVER (PARTITION BY convert(date,ratingdate) ORDER BY ratingdate desc) RowNum
      from trend t
)
select c.keyinstn, c.ratingdate, c.rating
  from cte c
 where c.RowNum = 1

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  March 19, 2019 at 7:58 am

  #2024899

  Or using an inline table:
  select c.keyinstn, c.ratingdate, c.rating
from (select t.keyinstn,ratingdate, rating,
               ROW_NUMBER() OVER (PARTITION BY convert(date,ratingdate) ORDER BY ratingdate desc) RowNum
          from trend t) c
where c.RowNum = 1

• kbhanu15

  SSCarpal Tunnel

  Points: 4498

  More actions

  March 19, 2019 at 8:02 am

  #2024900

  thank you it is working fine.

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  March 19, 2019 at 8:43 am

  #2024907

  kbhanu15 - Tuesday, March 19, 2019 8:02 AM

  thank you it is working fine.

  This won't be the last time you run into this.  You're also the one that will need to support the code.  So, the question to you is... do you understand the code well enough to not only support it but to write something for a similar task in the future without having to get help?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  March 19, 2019 at 9:09 am

  #2024914

  Or you could use Jeff's favourite method: 😛
  
  declare @trend table (keyinstn INT, ratingdate datetime, rating varchar(10))

  declare myCursor cursor for
select distinct t.keyinstn,convert(date,ratingdate) ratingdate1
   from trend t

  declare @keyinstn int, @date datetime

  open myCursor
fetch next from myCursor into @keyinstn, @date
while @@FETCH_STATUS = 0 begin
    insert into @trend(keyinstn,ratingdate,rating)
    select top(1) keyinstn,ratingdate,rating
      from trend t
     where t.keyinstn = @keyinstn
       and convert(date,t.ratingdate) = @date
     order by t.ratingdate DESC
    fetch next from myCursor into @keyinstn, @date
end
close myCursor
deallocate myCursor
select * from @trend

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  March 19, 2019 at 10:43 am

  #2024923

  Jonathan AC Roberts - Tuesday, March 19, 2019 9:09 AM

  Or you could use Jeff's favourite method: 😛
  
  declare @trend table (keyinstn INT, ratingdate datetime, rating varchar(10))

  declare myCursor cursor for
  select distinct t.keyinstn,convert(date,ratingdate) ratingdate1
     from trend t

  declare @keyinstn int, @date datetime

  open myCursor
fetch next from myCursor into @keyinstn, @date
while @@FETCH_STATUS = 0 begin
    insert into @trend(keyinstn,ratingdate,rating)
    select top(1) keyinstn,ratingdate,rating
      from trend t
     where t.keyinstn = @keyinstn
       and convert(date,t.ratingdate) = @date
     order by t.ratingdate DESC
    fetch next from myCursor into @keyinstn, @date
end
close myCursor
deallocate myCursor
select * from @trend

  Jeff will be roflgobbing at that, or preparing the pork chop launcher 😀

  Far away is close at hand in the images of elsewhere.
  Anon.

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  March 20, 2019 at 5:27 am

  #2024991

  Jonathan AC Roberts - Tuesday, March 19, 2019 9:09 AM

  Or you could use Jeff's favourite method: 😛
  
  declare @trend table (keyinstn INT, ratingdate datetime, rating varchar(10))

  declare myCursor cursor for
  select distinct t.keyinstn,convert(date,ratingdate) ratingdate1
     from trend t

  declare @keyinstn int, @date datetime

  open myCursor
fetch next from myCursor into @keyinstn, @date
while @@FETCH_STATUS = 0 begin
    insert into @trend(keyinstn,ratingdate,rating)
    select top(1) keyinstn,ratingdate,rating
      from trend t
     where t.keyinstn = @keyinstn
       and convert(date,t.ratingdate) = @date
     order by t.ratingdate DESC
    fetch next from myCursor into @keyinstn, @date
end
close myCursor
deallocate myCursor
select * from @trend

  BWAAA-HAAAA!!!  At least it's nicely formatted for easy readability. :D:D:D  It's not often that I see someone else use the "river" format.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)