Retrieving records from last 7 days

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)

Retrieving records from last 7 days

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2008-04-22 : 09:12:51

I want to retrieve all the records in a table which were added in the last 7 days. When a record is added the days date is recorded in the record as Date_Added field.

I am using the following code:

SELECT * FROM Table1
WHERE date_added = GetDate() - 7

The above does not work.

Anyone any ideas?
macca

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-22 : 09:14:20

SELECT * FROM Table1
WHERE date_added >=dateadd(day,datediff(day,0,GetDate())- 7,0)

Madhivanan

Failing to plan is Planning to fail

macca
Posting Yak Master

146 Posts

Posted - 2008-04-22 : 09:30:57

Thanks Madhivnan that works a treat.

I started using the following code but did not return same results as yourself:

SELECT * FROM paapplic
WHERE grant_date >= GetDate() - 7 AND grant_date <= GetDate()
Order By grant_date

macca

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-22 : 09:35:58

quote:
Originally posted by macca

Thanks Madhivnan that works a treat.

I started using the following code but did not return same results as yourself:

SELECT * FROM paapplic
WHERE grant_date >= GetDate() - 7 AND grant_date <= GetDate()
Order By grant_date

macca

You need to take care of time part

Madhivanan

Failing to plan is Planning to fail

macca
Posting Yak Master

146 Posts

Posted - 2008-04-22 : 10:26:38

Madhivnan,

I have another query. I have 3 tables, one called 'Table1' another called 'Point' and another called 'Base'.

'Table 1' contains records I have described above. Once I have all the records using the above query which could
be 100 records I have to do a query using each record, each record has unique ID number. I have to query each
record individually to get the data from a number of fields within the record. One field is called 'Town' and
another is called 'Area' and it contains a letter either 'A' or 'B', if it contains 'A' then we must get whatever
is another field called 'Unit', if 'B' is present then we do not query the 'Unit' field.

The table called 'Point' has 2 fields called ID which corresponds to the ID of the records in 'Table1' and a field called Town.
I need to use the ID number field from each record to read the coresponding Id field in the table called 'Point' and therefore
to get the appropriate Townland for each record in 'table1'.

The table called 'Base' has 2 fields one called Town which corresponds to the Town field in the 'Point' table and another called
Unit which muct be updated with the data from the unit field in 'Table1'.

Basically I am linking between 3 tables to update one with the information from another. I must do this for all 100 records
individually gotten using the query above.
I know this involves some looping but do not have a clue how to do it.

maybe you may know how to do this advanced sql.

Thanks,
macca

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-23 : 02:34:02

Do you want to update one table by joining two other tables?

Madhivanan

Failing to plan is Planning to fail

macca
Posting Yak Master

146 Posts

Posted - 2008-04-23 : 04:21:16

yes, that's what I want to do.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-23 : 08:43:44

General syntax

Update t1
set t1.col=t2.col,
t1.othercol=t3.othercol
from table1 t1 inner join table2 t2 on t1.keycol=t2.keycol
inner join table3 t3 on t1.keycol=t3.keycol

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources