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 2000 Forums
 SQL Server Development (2000)
 A week previous Date question

Author  Topic 

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-07-23 : 11:59:57
Hi there, I have a date issue that I cant figure out what the solution is and wonder if someone can help please ?


select distinct saledate, storenum, prodcode, right(dept,2),costvalue
from sales inner join productmaster
on (sales.prodcode = productmaster.productcode)
where saledate > (getdate() - datepart(dw, getdate())) - 8
and saledate < (getdate() - datepart(dw, getdate())) - 1
order by storenum, right(dept,2)


Every week on a wednesday I run the following code for the week previous ie: If I run it on wednesday 25/07/2007 I need the above selected records for and including the saledate between the 15/07/2007 (Sunday) to the 21/07/2007 (Saturday)

What I would like is for me to run the above code on any day this week and get the same saledate between dates ie if i run this on any day between 23/07/2007 and the 27/07/2007 I will still get the query returning sales back for 15/07/2007 (Sunday) to the 21/07/2007 (Saturday).

Please advise how I could change my saledate information in the above where clause to allow for this.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 12:04:17
Use the Datediff function.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-23 : 13:20:55
[code]
where
-- Greater than or equal Sunday of last week
saledate >= dateadd(dd,((datediff(dd,-53684,getdate())-7)/7)*7,-53684) and
-- Less than Sunday of this week
saledate < dateadd(dd,(datediff(dd,-53684,getdate())/7)*7,-53684)

[/code]

You can look at how to find the Start of the Week on this link.

Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

CODO ERGO SUM
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 13:43:31
sorry I didnt mean to be rude by posting a one-liner. I assumed you could just look up books on line for the datediff (or Dateadd) function and just plug it into your WHERE condition.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-07-24 : 03:38:09
No problem Dinakar I did have a look but couldnt quite work it out, MVJ thank you kindly that is what I was looking for , will test and let you know, thank you again.
Go to Top of Page

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-08-01 : 11:12:35
thanks Colonel this is working gr8.
Go to Top of Page
   

- Advertisement -