SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Return rows between 2 dateadds
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 07/23/2013 :  09:23:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 07/23/2013 :  09:27:07  Show Profile  Visit webfred's Homepage  Reply with Quote
Sorry, did you try your code?
What is your problem?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 07/23/2013 :  09:29:45  Show Profile  Reply with Quote
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...!
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 07/23/2013 :  09:35:32  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 07/23/2013 :  09:38:36  Show Profile  Reply with Quote
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...!
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 07/23/2013 :  09:51:12  Show Profile  Visit webfred's Homepage  Reply with Quote
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.

Edited by - webfred on 07/23/2013 09:52:23
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 07/23/2013 :  10:29:49  Show Profile  Reply with Quote
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...!
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 07/23/2013 :  10:29:49  Show Profile  Reply with Quote
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...!
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 07/23/2013 :  17:25:26  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/23/2013 :  18:09:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000