Return rows between 2 dateadds

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Return rows between 2 dateadds

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 09:23:01

Can this be done? to return the rows between these 2 week periods 9 & 10?

When f.CreatedDateTime >= DATEADD(wk,DATEDIFF(wk,-9,GETDATE()),0)
And f.CreatedDateTime <= DATEADD(wk,DATEDIFF(wk,-10,GETDATE()),0)

Or this to only return rows for week 9

When f.CreatedDateTime >= DATEADD(wk,DATEDIFF(wk,-9,GETDATE()),0)
And f.CreatedDateTime <= DATEADD(wk,DATEDIFF(wk,-9,GETDATE()),0)

Many thanks

SZ1
to learn is to show the universe that you care...!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-23 : 09:27:07

Sorry, did you try your code?
What is your problem?

Too old to Rock'n'Roll too young to die.

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 09:29:45

Sorry do you mean from the previous post or this post? the code works well from other post but wanted to know if the row search can be returned this way.
Thanks

SZ1
to learn is to show the universe that you care...!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-23 : 09:35:32

I don't know anything about any other posts.
I only wanted to know why you don't just try instead of asking here or if there is a problem...

Too old to Rock'n'Roll too young to die.

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 09:38:36

I am tring but I dont see any results with this code, I know there are rows in week 9 but none return, so I was asking if the code is correct?

Select Distinct f.IncidentID, i.IncidentNumber, f.DateKey,
Case
When f.CreatedDateTime >= DATEADD(wk,-9,GETDATE()) --start return rows for week 9 only
And f.CreatedDateTime <= DATEADD(wk,-9,GETDATE()) --end return rows for week 9 only
And f.[priority] = 1
And f.SnapShotDateKey = DATEADD(wk,DATEDIFF(d,-1,GETDATE()),0)
--SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
Then 1 Else 0
End as P1s
From FACT_INCIDENT f
Join DIM_INCIDENT i
On f.IncidentID = i.IncidentID
order by P1s Desc

SZ1
to learn is to show the universe that you care...!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-23 : 09:51:12

do this:

select
DATEADD(wk,-9,GETDATE())

to see the result (for example '2013-05-21 15:45:44.477')

then ask yourself if there is CreatedDateTime in your table which greater or equal AND less or equal

you will see the only match could be if the column is equal because the value can't be greater AND less

Then do this:

select
DATEADD(wk,-9,convert(date,GETDATE()))

and try if it works for you

edit: typo

Too old to Rock'n'Roll too young to die.

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 10:29:49

so the convert(date,GETDATE())) strips out the time then...

Im looking for records where the createddatetime falls in between a dateadd period such as last week, so the date could be less or more during that period. It must be me going mad I thought I could calculate a specified interval using the dateadd with a start and an end range.

Select Distinct f.IncidentID, i.IncidentNumber, f.DateKey,
Case
When f.CreatedDateTime >= DATEADD(week,-9,convert(date,GETDATE())) --start return rows for week 9 only
And f.CreatedDateTime <= DATEADD(week,-10,convert(date,GETDATE())) --end return rows for week 9 only
And f.[priority] = 1
And f.SnapShotDateKey = DATEADD(week,DATEDIFF(d,0,GETDATE()),0)
--SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
--SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
--SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
Then 1 Else 0
End as P1s
From FACT_INCIDENT f
Join DIM_INCIDENT i
On f.IncidentID = i.IncidentID
order by P1s Desc

SZ1
to learn is to show the universe that you care...!

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-07-23 : 10:29:49

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-07-23 : 17:25:26

Your code is puzzling. From your text you appear to be looking for items later than 9 weeks ago AND earlier than 10 weeks ago. This is a contradiction; nothing is later than June AND earlier than May in the same year. However, your logic is in the SELECT clause instead of the WHERE clause so you should be getting records but the value of P1s should always be 0.

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 18:09:41

WHEN f.CreatedDateTime >= DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()) - 10, '19000101')
AND f.CreatedDateTime <= DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()) - 9, '19000107')

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources