Loop calendar table and delete rows

Question

Loop calendar table and delete rows

SQL_Kills

Hall of Fame

Points: 3145
More actions
September 16, 2010 at 2:55 pm

#97499

Hi,
I have two tables (SQL Server 2005) "PeoplesCalender" and "Leavers". The two tables look like the below:
PeoplesCalender
date PersonIdAREA
01/09/2010MARFEMSOUTH
02/09/2010MARFEMSOUTH
03/09/2010MARFEMSOUTH
04/09/2010MARFEMSOUTH
05/09/2010MARFEMSOUTH
01/09/2010DICKPKNORTH
02/09/2010DICKPKNORTH
03/09/2010DICKPKNORTH
04/09/2010DICKPKNORTH
05/09/2010DICKPKNORTH
01/09/2010JAMEPOSOUTH
02/09/2010JAMEPOSOUTH
03/09/2010JAMEPOSOUTH
04/09/2010JAMEPOSOUTH
05/09/2010JAMEPOSOUTH
Leavers
PersonId DateLeft
JAMEPO 04/09/2010
MARFEM 01/09/2010
I want to delete rows from the "PeoplesCalender" when the date for that personid is after the date they have left, so the "PeopleCalender" would now look like the below:
date PersonIdAREA
01/09/2010MARFEMSOUTH
01/09/2010DICKPKNORTH
02/09/2010DICKPKNORTH
03/09/2010DICKPKNORTH
04/09/2010DICKPKNORTH
05/09/2010DICKPKNORTH
01/09/2010JAMEPOSOUTH
02/09/2010JAMEPOSOUTH
03/09/2010JAMEPOSOUTH
04/09/2010JAMEPOSOUTH
Is this possible, not sure if I need to create a cursor to do this?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95405 More actions · Answer 1

You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Can you reformat your post to be like this?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 2

Try to write a query to select all rows from PeoplesCalender that should be deleted (use an inner join on PersonID and a WHERE clause to limit PeoplesCalender.date>Leavers.DateLeft).

Once you verified those are the rows to be deleted, change the SELECT statement into a DELETE statement.

If you'd like to see the coded version, please provide ready to use sample data like Wayne already asked for.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQL_Kills Hall of Fame Points: 3145 More actions · Answer 3

SQL_Kills

Hall of Fame

Points: 3145

September 16, 2010 at 4:03 pm

#1222677

Thanks LutzM