flagging last 60days data

  • Hi Forum, I modified following script from last12month. Supposed it flags last 60 days, but it starts flagging first day of last second month. Could you help me to modify it to work properly, thanks.

    DECLARE @YestCurrentDate DATETIME

    DECLARE @Last2MonthDate DATETIME

    DECLARE @FromLast2Month INT

    SET @YestCurrentDate = DATEADD(dd,-1,GETDATE())

    SET @Last2MonthDate = DATEADD(dd,-60,@YestCurrentDate)

    SET @FromLast2Month = substring(convert(varchar,@Last2MonthDate,112),1,6)+'01'

    BEGIN

    UPDATE dbo.DimDateTime

    SET Last2Month = 1

    WHERE DateID >= @FromLast2Month and DateID <= convert(varchar,@YestCurrentDate,112)

    END

  • I think the last part of the where clause needs to be <, not <=.

    Also, you might want to look at the "Common Date/Time Routines" link in my signature for more efficient ways of doing date manipulations.

    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

  • That didn't help Wayne, just starts flagging second day of the month. In your note all of them are giving first day of month, week... What I need here, is last 60 days only. For ex. today 5 June, it has to flag the data starting from 5 april.

    Anyway, thanks for response.

  • Can you confirm the Data Type of the field DateID?

    Abhijit - http://abhijitmore.wordpress.com

  • It's int, Abhijit.

  • Change

    SET @FromLast2Month = substring(convert(varchar,@Last2MonthDate,112),1,6)+'01'

    to

    SET @FromLast2Month = substring(convert(varchar,@Last2MonthDate,112),1,8)

    That should do the trick

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • For ex. today 5 June, it has to flag the data starting from 5 april

    Dehqon, from this requirement, i see that you are interested in the same day of the previous 2nd month..so why dont you change DATEADD(dd,-60,@date) to DATEADD(mm,-2,@date) ?

  • ColdCoffee, the OP said 60 days, not 2 months, right?

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • ColdCoffee, the OP said 60 days, not 2 months, right?

    Initially, the OP said it is 60 days, but if u could see the quote i my previous post, the OP has said the if the date is 5 june, then all he needs is data from 5 april! Thats where i got confused and had put that code.. probably lets wait for the OP to post back..

  • Thanks to Jan Van der Eecken, it's working exactly as I wanted :-):-)

    ColdCoffee, that one i tried already, that flags the first day of last 2nd month also. If it had flagged to the same day of last 2nd month, it would be ok for me as well.

    Thanks to All.

  • Glad it helped. And thanks for the feedback.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

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