A bit stuck... xpert help needed! Joins and unions etc...
-
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
-
Hi Tim,
I understand your requirement as follows.
You wanted to delete all records having the "memberofgroup" field's value as 15.
You wanted to update the END-DATe to the maximum END-DATE possessed by any record whose memberofgroup is non 15.
I dont find the end date of the person ID "10762" updated to the maximum value in the example given by you.
Could you please help me understand your requirement better.
-
Is this what you mean ?
-- Begin Tran
-- update T
-- set end_date = MEdt.MaxEndDate
select T.*
, MEdt.MaxEndDate
, Mmog.Maxmemberofgroup
from mytable T
inner join
(
select person_id ,max( end_date ) as MaxEndDate
from mytable
group by person_id
) MEdt
on T.person_id = MEdt.person_id
and T.end_date < MEdt.MaxEndDate
inner join
(
select person_id ,max( memberofgroup ) as Maxmemberofgroup
from mytable t1
where memberofgroup < 15
and exists (select * from mytable t2 where t2.memberofgroup = 15 and t2.person_id = t1.person_id)
group by person_id
) Mmog
on T.person_id = Mmog.person_id
and T.memberofgroup = Mmog.Maxmemberofgroup
-- Delete mytable where t2.memberofgroup = 15
-- Commit Tran
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Ok.. Since your reply was delayed, I went through your statements a couple of times.
From what I have understood, I have formulated the SQL's below.--Creating a backup of the table
select *
into table_temp
from table1
go
--Updating end_date to maximum end-date
update table1
set end_date=(select max(end_date) from table1)
where end_date = (select max(end_date) from table1 where memberofgroup <> 15)
go
--deleting records where memberofgroup = 15
delete from table1 where memberofgroup = 15
go
Please let me know whether this is what you wanted.
-
Thanks all! I shall check the answers in a second... just to clarify the requirement....
------------------------------------------------------------
--I understand your requirement as follows.
--You wanted to delete all records having the "memberofgroup" field's value as 15.
--You wanted to update the END-DATe to the maximum END-DATE possessed by any record whose memberofgroup is non 15.
--I dont find the end date of the person ID "10762" updated to the maximum value in the example given by you.
--Could you please help me understand your requirement better.
------------------------------------------------------------
I want to set the highest year 14 record (highest end date) to the highest end date of all the records (14 AND 15) then afterwards remove the year 15 record. So whatever happens the highest date is kept for the last year 14 record.
Hope this is clear! Thanks, Tim
-
Untested, but something like this. Join your table to 2 virtual tables, which get the required max date per person, and identify which year 14 record to updaet
Update YourTable
Set end_date = vtable.DateToKeep
From YourTable
Inner Join
( -- virtual table 1 - get max date per person across 14 & 15
Select person_id, max(end_date) As DateToKeep
From YourTable
) vtable1
On ( vtable.person_id = YourTable.person_id )
Inner Join
( -- virtual table - identify max year 14 record to Update
Select person_id, max(end_date) As DateToUpdate
From YourTable
Where memberofgroup = 14
) vtable2
On ( vtable2.person_id = YourTable.person_id And
vtable2.DateToUpdate = YourTable.end_date )
Where memberofgroup = 14
After that, you need a simple DELETE to WHERE memberofgroup = 15
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply