January 23, 2011 at 7:26 pm
Hi All,
I have table with Identity column with seed of 1. And i need to find the records inserted for a last one week which are inserted into the table how do we find this ?
Table structure
columnname datatype
ID int IDENTITY column(1,1)
DateModifieddatetime
LegacyID nvarchar
Category_IDint
Title_ID int
NameGiven nvarchar
NameMiddle nvarchar
NameFamily nvarchar
But i can't use the date modified column as this will get updated if there is a modifications to table record.
Please suggest.
Thanks,
Gangadhar
January 23, 2011 at 9:12 pm
Gangadhar,
Whenever you design a table always keep two columns named created_date and modified_date. Whenever a record is inserted into a table then created_date and modified_date will be getdate(). However when a record gets modified then only the modified_date column will be updated.
Satnam
January 24, 2011 at 7:59 am
given your current table structure, you can't reliably find records inserted for any time period.
January 25, 2011 at 1:12 pm
As others have already pointed out, your current table design won't allow you to reliably query only those rows inserted as of a specific date. For what it's worth, just to get you out of this jam, the following query will return those rows that were inserted past a specific date. Also, the 2nd condition, the one with the subquery on max(id), will probably weed out those rows that were inserted before the given date and then modified afterward. This method assumes the identity column is always being inserted sequentially, which it most probably is unless you've been using "set identity_on on". Going forward, you really need to add a column containing the date inserted.
select * from MyTable
where DateModified >= '2011-01-23'
and id >
(
select max(id)
from MyTable
where DateModified < '2011-01-23'
);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply