get triggers latest update date

Is there a way in MSSQL 2000 to get trigger's latest update date?
sysobject table only has creation date of a trigger and I've been using ALTER TRIGGER command to modify it.
Thanks,
IgorUnfortunately, no. You can't get the last modified date for any SQL Server objects. You might instead do a DROP and CREATE when modifying your triggers.

Terri

Get the recent records

i have a datetime field in the post tables.

I would like to get the records within the latest 7 days.

Are there any functions for doing something like this?

my current query is something like

select * from post where creation_time ...

Thank you

try something like this:

create

table #test(datedatetime)

insert

into #test

values

('01/01/2007')

insert

into #test

values

('02/01/2007')

insert

into #test

values

('02/04/2007')

select

*from #test

where

date>dateadd(day,-7,getdate())

drop

table #test

I think that it will point you in correct direction, or maybe it is your solution?

|||

Could you not just do...

select

*from post

where

creation_time >dateadd(day,-7,getdate())

jpazgier, i am not following the reason for creating the additional table.

|||

I just try to provide working example in my answer so I created temporary table with my test data to show that it works and for future testing.

But in this case my example only points your how you can try to solve problem, you maybe would like to take care about not only day but also minutes?
This example if you run it at 12:31 today will show records inserted after 12:31 7 days ago so records inserted at 12:30 will be not visible and maybe author of the post would like to take care about this himself, I do not know if time of the day is important for him or not.

Thanks

|||Both answers are great!|||

Both answers are great!

Thank you

Get the closest date

Hello,

I need help in writing a SQL statement in MS SQL Server 2000 to select
the latest date (i.e., the date closest to or equal to the current date)
for a given date.

For example, in a table I have the following records:
Date Exchange-Rate
01/Sep/03 0.55
05/Sep/03 0.59

If the given date is 02/Sep/03, then the rate 0.55 should be return.
If the given date is 03/Sep/03, then the rate 0.55 should be return.
If the given date is 04/Sep/03, then the rate 0.59 should be return.

Thanks in advanced,

Benny

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Benny Chow (benny@.stg.net.nz) writes:
> I need help in writing a SQL statement in MS SQL Server 2000 to select
> the latest date (i.e., the date closest to or equal to the current date)
> for a given date.
> For example, in a table I have the following records:
> Date Exchange-Rate
> 01/Sep/03 0.55
> 05/Sep/03 0.59
> If the given date is 02/Sep/03, then the rate 0.55 should be return.
> If the given date is 03/Sep/03, then the rate 0.55 should be return.
> If the given date is 04/Sep/03, then the rate 0.59 should be return.

Next time, please include CREATE TABLE statements for the tables you
are working with and INSERT statements with sample data. This makes it
possible to post a tested solution.

Thus, this solution is untested:

SELECT exchangerage, date
FROM rates
WHERE date = (SELECT MAX(date)
FROM rates
WHERE date <= @.date)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CREATE TABLE ExchangeRates (rdate DATETIME PRIMARY KEY, exchangerate
DECIMAL(10,2) NOT NULL)
INSERT INTO ExchangeRates VALUES ('20030901',0.55)
INSERT INTO ExchangeRates VALUES ('20030905',0.59)

DECLARE @.dt DATETIME
SET @.dt = '20030902'

Here's one method:

SELECT exchangerate
FROM ExchangeRates
WHERE rdate =
(SELECT MIN(rdate)
FROM ExchangeRates
WHERE ABS(DATEDIFF(DAY,@.dt,rdate))=
(SELECT MIN(ABS(DATEDIFF(DAY,@.dt,rdate)))
FROM ExchangeRates))

Or you can use TOP:

SELECT TOP 1 exchangerate
FROM ExchangeRates
ORDER BY ABS(DATEDIFF(DAY,@.dt,rdate)), rdate

Personally, I would avoid TOP because it's a MS proprietary extension to
SQL.

--
David Portas
----
Please reply only to the newsgroup
--|||Benny wants the closest, before or after the specified date according to his
example.

--
David Portas
----
Please reply only to the newsgroup
--|||Thanks David, this is exactly what I needed. :)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Benny,

This might be a little more efficient than other
solutions, but it's not as simple:

select top 1 exchangerate
from (
select exchangerate, pref
from (
select top 1 exchangerate, 1 as pref
from (
select top 3 rdate, exchangerate
from ExchangeRates E1
where E1.rdate >= (
select max(rdate) as lastBefore
from ExchangeRates E2
where E2.rdate < @.dt
)
order by rdate
) X
order by case when rdate < @.dt then @.dt - rdate else rdate - @.dt end
) X1
union all
select exchangerate, pref
from (
select top 1 exchangerate, 2 pref
from ExchangeRates
order by rdate
) Y
) T
order by pref

-- Steve Kass
-- Drew University
-- Ref: 250CBB08-57AE-45C7-97F2-AF26AFC368ED

Benny Chow wrote:
> Hello,
> I need help in writing a SQL statement in MS SQL Server 2000 to select
> the latest date (i.e., the date closest to or equal to the current date)
> for a given date.
> For example, in a table I have the following records:
> Date Exchange-Rate
> 01/Sep/03 0.55
> 05/Sep/03 0.59
> If the given date is 02/Sep/03, then the rate 0.55 should be return.
> If the given date is 03/Sep/03, then the rate 0.55 should be return.
> If the given date is 04/Sep/03, then the rate 0.59 should be return.
> Thanks in advanced,
> Benny
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Benny wants the closest, before or after the specified date according to
> his example.

Funny guy. :-) Some of our tables for prices and rates are sparse in a
similar manner, but we always assume that a value applies until a new
value comes in. So I assume he wanted the same.

But had Benny included CREATE TABLE and sample data in INSERT statements,
I would have seen that my solution was wrong!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Funny guy. :-) Some of our tables for prices and rates are sparse in a
> similar manner, but we always assume that a value applies until a new
> value comes in. So I assume he wanted the same.

I agree that it seems like an unusual requirement. Although I suppose if you
wanted to calculate the value of a currency deal retrospectively it might
make sense to take the closest rate as the best approximation. But IANAA.

> But had Benny included CREATE TABLE and sample data in INSERT statements,
> I would have seen that my solution was wrong!

I know that feeling! :|

--
David Portas
----
Please reply only to the newsgroup
--|||Thanks for all your guys help ^^.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Get rows for latest date

Hello!

I have a table something like this:

ID INTEGER

Info VARCHAR (actually several columns but that is not important here)

DAT DateTime

For each ID there are several dates and for each of these dates there are several rows with different info. I would like to select the latest info for each ID. For example:

ID - DAT - Info

1 - 2007-02-01 - Info1

1 - 2007-02-01 - Info2

1 - 2006-02-01 - Info3

2 - 2007-05-05 - Info4

2 - 2007-02-01 - Info5

2 - 2006-02-01 - Info6

I would like to get:

Info1

Info2

Info4

This has to be done in one Query. Can anybody help me?

Here it is,

Code Block

Create Table #data (

[ID] int ,

[DAT] datetime ,

[Info] Varchar(100)

);

Insert Into #data Values('1','2007-02-01','Info1');

Insert Into #data Values('1','2007-02-01','Info2');

Insert Into #data Values('1','2006-02-01','Info3');

Insert Into #data Values('2','2007-05-05','Info4');

Insert Into #data Values('2','2007-02-01','Info5');

Insert Into #data Values('2','2006-02-01','Info6');

select main.info from #data main

join (select ID,max(dat) dat from #data group by ID) as latest

on latest.ID=main.ID and latest.dat=main.dat

|||

A couple of options:

Code Block

create table testdata

(ID int, Dat DATETIME, Nm CHAR(5))

INSERT INTO testdata

SELECT 1, '1 feb 2007', 'Info1'

UNION ALL

SELECT 1, '1 feb 2007', 'Info2'

UNION ALL

SELECT 1, '1 feb 2006', 'Info3'

UNION ALL

SELECT 2, '5 may 2007', 'Info4'

UNION ALL

SELECT 2, '1 feb 2007', 'Info5'

UNION ALL

SELECT 2, '1 feb 2006', 'Info6'

--SQL2005

WITH cte

AS

(SELECT ID, Nm, RANK() OVER (PARTITION BY ID ORDER BY Dat DESC) AS D

FROM testData)

SELECT Nm

FROM cte

WHERE D = 1

--SQL2000

SELECT Nm

FROM

(SELECT ID, MAX(Dat) AS Dt

FROM testData

GROUP BY ID) AS Bob

INNER JOIN testData t

ON Bob.Dt = t.Dat AND Bob.ID = t.ID

HTH!|||

One more trick..

Code Block

--SQL2005

;with cte

as

(select id, nm,dat,max(dat) over (partition by id) as latestdat from testdata)

select nm

from cte

where dat = latestdat

Code Block

--SQL Server 2000

select

main.info

from

#data main

where

exists

(

select * from

(

select

d

,max(dat) dat

from

#data

group by D

) data

where

data.d = main.d

and data.dat=main.dat

)

|||

Thank You guys!!

...for the fast and helpful response. I would never figure that out.

get record with latest date

if there are 2 records with different date

how to write query for --> get record with latest date

Hi,

use Order By clause.

For example,

Select top 1 * from YourTable Order By YourDateFieldName Desc

|||

Use something like this:

SELECT TOP 1 *
FROM MyTable
ORDER BY DateField DESC

Regards,
Martin

|||

Here is an example based on the NorthWinds database:

Select TOP 1 *from OrdersOrder by OrderDateDesc

|||

Here is another way to do it:

select *
from MyTable
where timestampfield = (select max(timestampfield)
from MyTable)