Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete From Scripting Help Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 10:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:58 AM
Points: 92, Visits: 679
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
Post #1476702
Posted Tuesday, July 23, 2013 10:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:30 PM
Points: 12,918, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1476710
Posted Wednesday, July 24, 2013 6:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:34 AM
Points: 135, Visits: 250
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.
Post #1477026
Posted Wednesday, July 24, 2013 10:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:58 AM
Points: 92, Visits: 679
Thank you for the responses. I will try and post with my results when we try it out.

Regards,
David
Post #1477149
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse