CODEWITHSUNDEEP
sqlsql-server
dave
dave

Reputation: 50

Compare dates in table where similar columns are in different rows

SQL Server 2008/12 question. I'll try to give a simplified explanation.

With a table of data

Like this:

create table #temp 
(
     id varchar (5),
     code varchar (3),
     dt varchar(8)
)

insert into #temp (id,code,dt)
values ('abc00', '2', '20180101'), ('abc01', '1', '20180201'),
       ('def00', '1', '20180201'), ('def01', '2', '20180101'),
       ('ghi00', '2', '20180101'), ('ghi01', '1', '20180102'),
       ('jkl00', '1', '20180101')

I need one or more select queries that do the following:

For the entire table, if the 1st 3 chars of id are equal they're "related" ids (left(id, 3) is the same)

So for each "set" of related ids find min(id) and max(id), then make sure that min(id).date < max(id).date

I've tried answers involving OVER(PARTITION id), CTEs self joins and temp tables, but I can't get my head around how to put it all together to make it work.

I seem to get stuck where I'm looking for the different lines of related ids.

I'm working on comparing the dates for each related id, find out which one is greater. If i can get that working I think I can bring it home.

I got some help from another thread (Compare dates between 2 different rows and columns), but the added complexity of limiting the search between the the current row and all "related" ids is stumping me, and that's where I think I'm bombing out.

Here's what I've got so far:

select * 
from #temp 
where cast(dt as date) > (select top 1 cast(dt as date) 
                          from #temp AS sub
                          where sub.id in (select id 
                                           from #temp 
                                           where (left(#temp.id, 3) = left(sub.id, 3)) 
                                             and (#temp.id <> sub.id)) 
                          order by id)

... but that's returning no rows. I've been banging my head on this all day.

Edit:

As an example of what I'm expecting here:

Using this stmt:

select * 
from #temp sub 
where 'abc' = left(sub.id, 3) and ('abc01' <> sub.id)

I get this result:

id    code dt
----- ---- --------
abc00 2    20170101

Thanks for the edit tip @Philip

Can anyone help?

Thanks for reading.

Upvotes: 1

Views: 105

Answers (2)

Ahmad Jamal
Ahmad Jamal

Reputation: 216

i think you want this ?

select * from #temp 
        where cast(dt as date) > 
            (select TOP 1 cast(dt as date) from #temp AS sub
                where (left(sub.id,3)) in 
                ( 
                    select (left(id,3)) AS Id from #temp where (left(#temp.id,3) = left(sub.id,3))  AND #temp.id  != sub.id 
                ) 
            )

Upvotes: 2

Xedni
Xedni

Reputation: 4695

Something like this?

select
    grp = left(id, 3),
    MinDt = min(dt),
    MaxDt = max(dt)
from #temp
group by left(id, 3)
having min(dt) < max(dt)

Upvotes: 0

Related Questions