| Author |
Topic |
|
cyberGuy
Starting Member
3 Posts |
 Posted - 2006-08-24 : 15:25:16
|
| Could anyone help me with finding records which have been added a week before everytime I runmy query?Thanks |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-08-24 : 15:37:28
|
| Is there any date field (do u collect the date - time) in a field?Srinika |
 |
|
|
cyberGuy
Starting Member
3 Posts |
Posted - 2006-08-24 : 15:41:22
|
| Yes there is. I have a Column called "SubDate" that contain date on which users have subscibed. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-08-24 : 15:58:18
|
| [code]create table #t (a int, d datetime)Insert into #t values(1,getdate())Insert into #t values(2,getdate()-1)Insert into #t values(3,getdate()-4)Insert into #t values(4,getdate()-8)Insert into #t values(5,getdate()-13)Insert into #t values(6,getdate()+9)-- if u r need todays data as wellSelect * from #t where d between dateadd(day, 0, datediff(day, 0, getdate()-7)) and dateadd(day, 0, datediff(day, 0, getdate()+1))-- chk the following and adjust the above to any of ur requirementsSelect dateadd(day, 0, datediff(day, 0, getdate())) Select dateadd(day, 0, datediff(day, 0, getdate()-7))[/code]Srinika |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-08-24 : 17:05:14
|
| You should be able to just do this:SELECT * FROM yourtableWHERE datediff(d, SubDate, getdate()) < 7 |
|
|
|
cyberGuy
Starting Member
3 Posts |
Posted - 2006-08-24 : 17:11:16
|
| The reason why I don't want to use that is because, for example if I run my query on Friday, I want to get records from last Monday upto last sunday. Not from 7 days back from Friday. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-24 : 17:19:28
|
quote:
Originally posted by snSQL
You should be able to just do this:SELECT * FROM yourtableWHERE datediff(d, SubDate, getdate()) < 7
That solution can't make use of an index on SubDate, so therefore should not be used. Please see Srinika's solution. It does the same thing and can use an index.Tara Kizer |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-08-24 : 17:48:01
|
| Then this will do it - corrected to ensure use of index too :-)The -5 in the first expression will start it from Monday of the previous week and the 2 in the second expression will make it until last Sunday. You can change those numbers to change the range. The second expression will strictly speaking include the stoke of midnight on Monday this week, so you could subtract one second from that if you need to.SELECT * FROM yourtable WHERE SubDate between convert(varchar(10), dateadd(d, -5 - datepart(w, getdate()), getdate()), 101) and convert(varchar(10), dateadd(d, 2 - datepart(w, getdate()), getdate()), 101) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-24 : 18:20:39
|
quote:
Originally posted by snSQL
Then this will do it - corrected to ensure use of index too :-)
Thumbs up!  Tara Kizer |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-24 : 18:35:04
|
This will give you the exact range you need using the F_START_OF_WEEK function to find Monday last week and Monday this week.You can run it any day this week without changing the query and it will give you the results for last week.select *from MyTablewhere -- Start of Week Function is here -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307 -- -- Greater than or equal to last Monday SubDate >= dbo.F_START_OF_WEEK(getdate(),2)-7 and -- Before this Monday SubDate < dbo.F_START_OF_WEEK(getdate(),2) It is best to do date range queries in this form instead of using the BETWEEN operator to avoid problems with including a time that you shouldn't.where MyDate >= @StartDate and MyDate < @EndDate CODO ERGO SUM |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-08-24 : 18:36:53
|
quote:
Originally posted by cyberGuy
The reason why I don't want to use that is because, for example if I run my query on Friday, I want to get records from last Monday upto last sunday. Not from 7 days back from Friday.
The date range (and start date) not clear.U can modify my code and put it in a Stored Procedure and u can pass Start date & end date (or # of days) as parameter and achieve ur desired results.AndTara, Isn't the 2nd solution of snSQL is more inefficient (than mine)?because of using functions as convertig to varchar and datetime back & forth?(I remember, some time back, when I gave an answer to a question with this type of conversion, somebody answered that it is ineffieient!!) Srinika |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-24 : 18:40:36
|
| Srinika, yes. But at least his solution can now use an index. Both solutions can use indexes, but yours is more efficient.Tara Kizer |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-24 : 18:52:02
|
quote:
Originally posted by Srinika
quote:
Originally posted by cyberGuy
The reason why I don't want to use that is because, for example if I run my query on Friday, I want to get records from last Monday upto last sunday. Not from 7 days back from Friday.
The date range (and start date) not clear.U can modify my code and put it in a Stored Procedure and u can pass Start date & end date (or # of days) as parameter and achieve ur desired results.AndTara, Isn't the 2nd solution of snSQL is more inefficient (than mine)?because of using functions as convertig to varchar and datetime back & forth?(I remember, some time back, when I gave an answer to a question with this type of conversion, somebody answered that it is ineffieient!!) Srinika
Both solutions have an error due to the BETWEEN operator because they will select a time that should not be included.As I stated in my other post, the query should be in this form:where MyDate >= @StartDate and MyDate < @EndDate CODO ERGO SUM |
|
|
|
|
HELP ON SQL