Help with t-sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with t-sql

Author

Topic

ARG
Starting Member

2 Posts

Posted - 2009-02-23 : 12:34:43

I have a table with people's certification "to" and "from" date for different test types (they are certified for 4 years at a time). I need to pull the results in a way that if there is no gap in the certification, pull the initial "From" date and the most recent "To" date. If there is a gap, then pull whatever the most recent "From" date is.

Here is an example of the table with data:

create table #Results
(ID varchar(10),
Test_Type int,
Certified_From datetime, Certified_To datetime )

insert into #Results
select '100',1,'1996-02-01 00:00:00','2000-12-31 00:00:00' UNION
select '100',1,'2000-11-15 00:00:00','2004-12-31 00:00:00' UNION
select '100',1,'2005-10-01 00:00:00','2009-12-31 00:00:00' UNION
select '100',4,'2005-06-14 00:00:00','2009-12-31 00:00:00' UNION
select '200',1,'2004-02-01 00:00:00','2008-12-31 00:00:00' UNION
select '200',1,'2008-09-14 00:00:00','2012-12-31 00:00:00'

--the result should be like this
ID TestType CertifiedFrom CertifiedTo
100 1 2005-10-01 2009-12-31
100 4 2005-06-14 2009-12-31
200 1 2004-02-01 2012-12-31

Any suggestions on how to pull the resultset like this?

Thank you.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-23 : 20:09:56

Here is one way of doing it.

with A as
(
  select
     row_number() over (partition by id, test_type order by id,certified_to desc) as rownum,
     *
  from
     #Results
),
B as
(
  select
     a.*,
     b.prev_certified_from
  from a
  outer apply
  (
     select
        case when a.certified_from <= b.Certified_to
        then a.certified_from end as prev_certified_from
     from
        a b where b.id = a.id and b.rownum = a.rownum+1
  )b
)
  select
     id,test_type,
     max(case when prev_certified_from is null then certified_from end) as certified_from,
     max(certified_to) as certified_to
  from
     B
  group by
     id, test_type

ARG
Starting Member

2 Posts

Posted - 2009-02-23 : 22:51:07

Thank you. I was trying to do it with a cursor and comparing the current Certified_To with the previous Certified_To and if the difference is more than 4 years, delete the previous records etc...

This looks good. Thank you very much for your help.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-24 : 00:06:46

Try this also,

declare @temp table
(ID varchar(10),
Test_Type int,
Certified_From datetime, Certified_To datetime )

insert into @temp
select '100',1,'1996-02-01 00:00:00','2000-12-31 00:00:00' UNION
select '100',1,'2000-11-15 00:00:00','2004-12-31 00:00:00' UNION
select '100',1,'2005-10-01 00:00:00','2009-12-31 00:00:00' UNION
select '100',4,'2005-06-14 00:00:00','2009-12-31 00:00:00' UNION
select '200',1,'2004-02-01 00:00:00','2008-12-31 00:00:00' UNION
select '200',1,'2008-09-14 00:00:00','2012-12-31 00:00:00'

select
ID,
Test_Type,
case when id = 100 then max(Certified_From)
when id = 200 then min(Certified_From) end as Certified_From,
max(Certified_To)
from @temp group by id,test_type order by id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources