Delete From Scripting Help

Question

Delete From Scripting Help

david.ostrander

SSCommitted

Points: 1828
More actions
July 23, 2013 at 10:16 am

#277784

Hello –
I currently have an SSIS package running and the first part of it deletes all data in (Table A) but saves the rows with the PIT of Friday at 4:00 PM for reference reasons.
DELETE FROM dbo.BackLogLaborOld
WHERE (datename(dw,PIT)<>'Friday' or datepart(hh,PIT) <> 16)
Now they want to change the day and time to Saturday at 12:00 PM to save but don't want to have the script delete all data from the past Friday’s at 4:00 PM data.
So I want to save the old Friday data but going forward change to the new day and time.
Could anyone suggest how I would go about doing that? Is that possible?
Regards,
David

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

david.ostrander (7/23/2013)
Hello –
I currently have an SSIS package running and the first part of it deletes all data in (Table A) but saves the rows with the PIT of Friday at 4:00 PM for reference reasons.
DELETE FROM dbo.BackLogLaborOld
WHERE (datename(dw,PIT)<>'Friday' or datepart(hh,PIT) <> 16)
Now they want to change the day and time to Saturday at 12:00 PM to save but don't want to have the script delete all data from the past Friday’s at 4:00 PM data.
So I want to save the old Friday data but going forward change to the new day and time.
Could anyone suggest how I would go about doing that? Is that possible?
Regards,
David

could you add to the WHERE statement to also check the date going forward?

say if today is the cutoff day, it might be something like this:

--DELETE

SELECT *

FROM dbo.BackLogLaborOld

WHERE

(DW < CONVERT(DATETIME,'2013-07-23')

AND (DATENAME(dw,PIT) <> 'Friday'

OR DATEPART(hh,PIT) <> 16))

OR

(DW >= CONVERT(DATETIME,'2013-07-23')

AND (DATENAME(dw,PIT) <> 'Saturday'

OR DATEPART(hh,PIT) <> 12))

edited to fix the goofy date and the datepart of PIT, thanks to lshanahan for pointing that out!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

shoestringdba SSCertifiable Points: 6226 More actions · Answer 2

2103? Lowell: You're ahead of your time. 🙂

Also, it should be DATEPART(hh,PIT) <> 12 in the second part of the WHERE condition.

____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.

david.ostrander SSCommitted Points: 1828 More actions · Answer 3

Thank you for the responses. I will try and post with my results when we try it out.

Regards,

David