Post reply

Assistance On A Delete Statement

  • August 4, 2009 at 6:53 pm

    #71748

    Need some help on writing a delete statement.

    Currently I have the following delete statement that deletes all the dates in my table that arent in the current month. I want to modify this statement to not delete my data if a new month starts on a weekend while my company is closed. I have a dates table with an open/close flag if that helps.

    Delete tmp3_f_sales_Detail

    where date_id not in

    (Select fiscal_period from d_dates where dt = CONVERT(datetime, FLOOR(CONVERT(float(24), dateadd (dd,-1,getdate()))))))

    Hope I am not being to vague.

  • August 4, 2009 at 8:03 pm

    #1034583

    correct me if I'm wrong, but i think you want to find the first Monday of the month, and delete anything prior to that?

    is that right? or do you want last day of LAST month, and delete prior to that?

    here's examples, does this get you closer?

    --find the first business day (Monday) of this month

    select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

    --results: 2009-08-03 00:00:00.000

    --find the last day of the prior month

    select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

    --results: 2009-07-31 23:59:59.997

    --last business day(Friday) of the prior month...

    select dateadd(ms,-3,dateadd(dd,-3,DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)))

    --results 2009-07-30 23:59:59.997

    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!

  • August 5, 2009 at 7:49 pm

    #1035154

    Thanks for the idea. Something in your code just got me thinking. And I was trying to make things more difficult then they are.

    Here is all I needed to do

    All I wanted to do is keep my current period in my temp table.

    delete tmp3_f_sales_detail where date_id not in (select dt from d_dates where cal_period in (select cal_period from d_dates where dt in (select max(date_id) from tmp3_f_sales_detail)))

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

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