Help with date in sQL

Question

Post reply

Help with date in sQL

dhananjay.nagarkar

Default port

Points: 1496
More actions
February 4, 2014 at 7:51 pm

#285148

I have date field named "[Latest Filled Date]" - format smalltimedate.
I'm trying to do-If the "[Latest Filled Date]" equals the previous weeks Monday - Saturday dates then set flag a field to "YES" else "NO"
example: if the "[Latest Filled Date]" is Monday January 27th - then All records with a the "[Latest Filled Date]" of 1/20-1/25 would equal 'YES"
need help on how to do this.
Thanks
DJ

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Can you provide a little more information such as the table structure and 20 or so sample records for this table?

Having this kind of information is extremely helpful in being able to provide a satisfactory solution for you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

dhananjay.nagarkar (2/4/2014)
I have date field named "[Latest Filled Date]" - format smalltimedate.
I'm trying to do-If the "[Latest Filled Date]" equals the previous weeks Monday - Saturday dates then set flag a field to "YES" else "NO"
example: if the "[Latest Filled Date]" is Monday January 27th - then All records with a the "[Latest Filled Date]" of 1/20-1/25 would equal 'YES"
need help on how to do this.
Thanks
DJ

I make no guarantees for the following code because you didn't provide any readily consumable test data. Please see the first link in my signature line below under "Helpful Links" for how to correctly post such a problem in the future. Thanks.

I believe that this...

example: if the "[Latest Filled Date]" is Monday January 27th - then All records with a the "[Latest Filled Date]" of 1/20-1/25 would equal 'YES"

Should have been this...

example: if the a variable is Monday January 27th - then All records with a the "[Latest Filled Date]" of 1/20-1/25 would equal 'YES"

If that's correct, the following code should get you close to what you actually want. Read "Books Online" about DATEDIFF, DATEADD, and Integer Math to understand the code. If you don't know what "Books Online" actually is, press the {f1} key while in SSMS to get there.

DECLARE @SomeDate DATETIME;

SELECT @SomeDate = '2014-01-27'

WITH

cteBlockDate AS

(

SELECT [Latest Filled Date]

,CurrentMonday = DATEADD(dd,DATEDIFF(dd,0,@SomeDate)/7*7,0) --Monday of same week as @SomeDate

FROM dbo.YourTable

)

SELECT [Latest Filled Date]

,Flag = CASE

WHEN [Latest Filled Date] >= DATEADD(dd,-7,CurrentMonday ) --Monday of previous week

AND [Latest Filled Date] < DATEADD(dd,-1,CurrentMonday ) --Sunday of previous week

THEN 'Yes'

ELSE 'No'

END

FROM cteBlockDate

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dhananjay.nagarkar Default port Points: 1496 More actions · Answer 3

Dear Jason,

The Field name is "Latest Filled Date," and format is smalldate time - below are few values of this field:

Latest Filled Date

2013-11-20 13:43:46.000

Latest Filled Date

2013-12-10 13:42:45.000

Latest Filled Date

2013-11-22 07:11:21.000

Latest Filled Date

2013-11-06 17:51:48.000

Latest Filled Date

2013-11-13 07:45:12.000

and table name is "Filled"

--

select [Latest Filled Date]

FROM Taleo.dbo.Filled

--

Does that help?

Thanks

DJ

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

dhananjay.nagarkar (2/4/2014)
I have date field named "[Latest Filled Date]" - format smalltimedate.
I'm trying to do-If the "[Latest Filled Date]" equals the previous weeks Monday - Saturday dates then set flag a field to "YES" else "NO"
example: if the "[Latest Filled Date]" is Monday January 27th - then All records with a the "[Latest Filled Date]" of 1/20-1/25 would equal 'YES"
need help on how to do this.
Thanks
DJ

This makes no sense as it stands. Jeff's made a valiant stab at interpreting what you're trying to do but you haven't answered his questions or read the article he recommended. I'm sure that what you are seeking to do is straightforward - even if it isn't, folks who lurk around here are more than capable of dealing with it. Your best chance of a quick resolution is to read the article Jeff pointed out to you and follow the instructions in it. We need a sample data set to code against - and a results set to help describe what you are trying to do.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dhananjay.nagarkar Default port Points: 1496 More actions · Answer 5

Dear Jeff,

thanks for helping me with the code.

It worked accurately as per need.

Kind Regards

DJ

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

dhananjay.nagarkar (2/6/2014)
Dear Jeff,
thanks for helping me with the code.
It worked accurately as per need.
Kind Regards
DJ

You're welcome. I get lucky, sometimes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)