| Author |
Topic |
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
 Posted - 2011-05-02 : 09:26:15
|
| We have a table that is timelined which means each customer record has many associated records in the table.Among all those customer records in the table only one customer record is the current one and all the other customer records are historical customer records.We identify current and historical records with the help of startdate and Enddate columns in the same tableExRowid CustomerId State StartDate EndDate 1 123456 CO 1/1/2009 12/31/2009 2 123456 IL 12/31/2009 12/31/2010 3 123456 CA 12/31/2010In the Above example Rowids 1 and 2 are historical and 3 is the current record.I need help to write a query to delete all the historical records in the table which do not have a current record.ThanksJim |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-05-02 : 09:37:14
|
Please specify "historical records in the table which do not have a current record".How do you know if a record is "a current record"?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2011-05-02 : 09:50:54
|
| We know historical records when a record has StartDate and EndDateWe know a current record is when a record has only StartDateThanksJim |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-05-02 : 10:09:29
|
Try this:delete t1from yourTable t1where (StartDate is not null and EndDate is not null)and not exists(select * from YourTable t2 where t2.CustomerId = t1.CustomerId and t2.EndDate is null)
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2011-05-02 : 10:47:44
|
| Wow.It worked greatIam trying to understand the logic so I can have this implemented in two other places.It would be of great help if you can help me with the below1)How do I modify the same query to see what are the customerId's that will be deleted - I replaced the Delete with select and getting an errorError Message: Invalid column name 'T1'.2)I have another Table where the deletion is based on CustomerId and CustRefId.How Do I modify your query to do the deletion.ThanksJim |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-05-02 : 12:50:57
|
1)delete t1select t1.*from yourTable t1where (StartDate is not null and EndDate is not null)and not exists(select * from YourTable t2 where t2.CustomerId = t1.CustomerId and t2.EndDate is null)2)Isn't clear to me, sorry.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2011-05-03 : 08:37:04
|
| 2)I have a table where I need to delete historical records from the table if there are no current records.This table has two key columns in it and they are CustomerId and CustRefIdso how do I substitute both these keys in your below delete query?and not exists(select * from YourTable t2 where t2.CustomerId = t1.CustomerId and t2.EndDate is null) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-05-03 : 09:28:13
|
Do you mean this?quote:
Originally posted by jim_jim
2)I have a table where I need to delete historical records from the table if there are no current records.This table has two key columns in it and they are CustomerId and CustRefIdso how do I substitute both these keys in your below delete query?and not exists(select * from YourTable t2 where t2.CustomerId = t1.CustomerId and t2.CustRefId = t1.CustRefId and t2.EndDate is null)
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2011-05-04 : 08:17:42
|
| I will have to try testing your latest one to see whether this is doing what I wanted.Thank You so much |
|
|
|
|
Query to Check and Delete stuff from