1

I have a table which looks like this:

  • id
  • create_date (stored as dateTime)
  • industry

Example entries:

1,  '2012-12-01 00:00:00', 'retail'
1,  '1969-12-31 19:00:00', 'leisure and tourism'
1,  '2010-10-01 00:00:00', 'telesales'
2,  '2010-2-01 00:00:00', 'finance'
2,  '2010-2-01 00:00:00', 'retail'
2,  '2009-08-01 00:00:00', 'finance'

Now the create date is the most recent if, the create date is '1969-12-31' OR if that does not exist then it it the most recent date. Hence I change any dates '1969-12-31' to todays date.

However now I want to look at only the recent date value.

This seems a trickier problem, because you have to deal with conversion as well as order after conversion to then choose the first rank. Also some people you don't even need to convert their most recent date, such as 2.

I want the data to look like the following:

1,  '1969-12-31 19:00:00', ['leisure and tourism']
2,  '2010-2-01 00:00:00', ['finance', 'retail']

I've been looking at examples, here, but it doesn't deal with the data transform. Also another issue is the case when a single id, has two identical most recent create_date's, as shown with id = 2, where we have to concatenate the results.

I've also attempted to create a case statement to assist in conversion:

CASE WHEN create_date = '1969-12-31 19:00:00' 
  THEN CONVERT(VARCHAR(19), GETDATE(),120)
  ELSE create_date --Format: yyyy-mm-dd hh:mi:ss
END AS createDate

Please note, I am using SQL Server.

Community
  • 1
  • 1
redrubia
  • 2,256
  • 6
  • 33
  • 47

1 Answers1

0
declare @t table(id int, create_date datetime, industry varchar(50))

insert @t values(1,'20121201', 'retail')
insert @t values(1,'19691231', 'leisure and tourism')
insert @t values(1,'20101001', 'telesales')
insert @t values(2,'20100201', 'finance')
insert @t values(2,'20100201', 'retail')
insert @t values(2,'20090801', 'finance')



;with a as
(
  select id, industry, case when create_date = convert(datetime, '31/12/1969', 104)
  then current_timestamp else create_date end create_date, min(create_date) over (partition by id) chk,
  row_number() over (partition by id order by case when create_date = convert(datetime, '31/12/1969', 104) then 1 else 2 end, create_date desc) rn
  from @t
), b as
(
  select * from a
  where rn = 1 or chk <> convert(datetime, '31/12/1969', 104)
)
select b.id, max(create_date) create_date
,STUFF(( 
    select distinct  ',' + industry
    from b b1 
    where b1.id = b.id
    for xml path(''), type 
).value('.', 'varchar(max)'), 1, 1, '') [industries] 
from b
group by b.id 

id  create_date             industries
1   2014-01-21 16:36:41.010 leisure and tourism
2   2010-02-01 00:00:00.000 finance,retail
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92