Evil sql... can anyone help, I'm stuck?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Evil sql... can anyone help, I'm stuck??

Author

Topic

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2005-02-02 : 05:38:51

Hi all,

I'm getting lost in unions and joins here! Speed is not an issue with this script.....Thanks!

I need to delete the year 15 from the table but first I need to replace the HIGHEST non year 15 end_date
with the highest end_date that person has.

person_id start_date end_date memberofgroup
10762 2001-09-01 2004-07-21 14
10762 2004-07-22 2004-09-21 14
10762 2002-09-22 2005-01-01 15
10111 2001-01-01 2002-12-31 14
10111 2002-01-01 2003-12-31 15
10111 2003-01-01 2004-12-31 14

Should look like this, note for the second person the year 15 didn't have the highest end_date so is just
deleted.

person_id start_date end_date memberofgroup
10762 2001-09-01 2004-07-21 14
10762 2004-07-22 2005-01-01 14
10111 2001-01-01 2002-12-31 14
10111 2003-01-01 2004-12-31 14

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2005-02-02 : 06:47:30

declare @tbl table (person_id int,start_date datetime,end_date datetime ,memberofgroup int)
insert into @tbl
select 10762, cast('2001-09-01' as datetime),cast('2004-07-21' as datetime), 14 union
select 10762, cast('2004-07-22' as datetime),cast('2004-09-21' as datetime), 14 union
select 10762, cast('2002-09-22' as datetime),cast('2005-01-01' as datetime), 15 union
select 10111, cast('2001-01-01' as datetime),cast('2002-12-31' as datetime), 14 union
select 10111, cast('2002-01-01' as datetime),cast('2003-12-31' as datetime), 15 union
select 10111, cast('2003-01-01' as datetime),cast('2004-12-31' as datetime), 14

update @tbl set end_date = case when a.end_Date < (select max(end_date) from @tbl b where a.person_id=b.person_id and b.memberofgroup=15) then (select max(end_date) from @tbl b where a.person_id=b.person_id and b.memberofgroup=15)
else a.end_Date
end

from
@tbl a,
(select person_id, max(end_date) end_date from @tbl where memberofgroup <> 15
group by person_id ) updDate
where a.person_id=upddate.person_id
and a.end_date = upddate.end_date

delete @tbl where memberofgroup=15
select * from @tbl

Enjoy working

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2005-02-02 : 07:53:42

You can also try this one,
-----------------------
declare @tbl table (person_id int,start_date datetime,end_date datetime ,memberofgroup int)
insert into @tbl
select 10762, cast('2001-09-01' as datetime),cast('2004-07-21' as datetime), 14 union
select 10762, cast('2004-07-22' as datetime),cast('2004-09-21' as datetime), 14 union
select 10762, cast('2002-09-22' as datetime),cast('2005-01-01' as datetime), 15 union
select 10111, cast('2001-01-01' as datetime),cast('2002-12-31' as datetime), 14 union
select 10111, cast('2002-01-01' as datetime),cast('2003-12-31' as datetime), 15 union
select 10111, cast('2003-01-01' as datetime),cast('2004-12-31' as datetime), 14

update @tbl set end_Date = (select max(end_Date) end_Date
from @tbl c where c.person_id = a.person_id )

from @tbl a,
(select person_id,max(end_Date) end_Date
from @tbl where memberofgroup <> 15 group by person_id) upd
where a.person_id=upd.person_id and a.end_Date=upd.end_Date

delete @tbl where memberofgroup=15
select * from @tbl

Enjoy working

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-02 : 08:03:28

What about this?

Declare @t table(person_id numeric,start_date smalldatetime,end_date smalldatetime,memberofgroup int)
Declare @date1 smalldatetime
Declare @date2 smalldatetime
insert into @t
select 10762, '2001-09-01 00:00:00', '2004-07-21 00:00:00', 14 union all
select 10762, '2004-07-22 00:00:00', '2004-09-21 00:00:00', 14 union all
select 10762, '2002-09-22 00:00:00', '2005-01-01 00:00:00', 15 union all
select 10111, '2001-01-01 00:00:00', '2002-12-31 00:00:00', 14 union all
select 10111, '2002-01-01 00:00:00', '2003-12-31 00:00:00', 15 union all
select 10111, '2003-01-01 00:00:00', '2004-12-31 00:00:00', 14

select @date1=Max(End_date) from @t where memberofgroup=15
select @date2=Max(End_date) from @t where memberofgroup=14 and
person_id=(select max(person_id) from @t where memberofgroup=14)

update @t set end_date=@Date1 where memberofgroup=14
and end_date=@date2
delete from @t where memberofgroup=15
Select * from @t

Madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources