flagging last 60days data

Question

flagging last 60days data

Sezam

Ten Centuries

Points: 1232
More actions
June 3, 2010 at 8:34 pm

#222320

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

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

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

WayneS SSC Guru Points: 95409 More actions · Answer 1

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

Sezam Ten Centuries Points: 1232 More actions · Answer 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.

Abhijit More SSCertifiable Points: 6897 More actions · Answer 3

Can you confirm the Data Type of the field DateID?

Abhijit - http://abhijitmore.wordpress.com

Sezam Ten Centuries Points: 1232 More actions · Answer 4

Sezam

Ten Centuries

Points: 1232

June 4, 2010 at 2:07 am

#1176380

It's int, Abhijit.

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 5

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]

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 6

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) ?

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 7

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 SSC-Dedicated Points: 39971 More actions · Answer 8

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..

Sezam Ten Centuries Points: 1232 More actions · Answer 9

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.

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 10

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]