query for items from last 30 days

[JimMuglia]

Greetings,

I wanted to add the clause "records from the last 30 days only" to the query below. And say PROT_COM_HEADER.SYSTEM_TIME rerprsented the date field. How would I say it?

The query presently is set up to:

1) Get the top 3000 records from the table in descending order (soas to get the last 3000 records very quickly, as opposed to having to scan through the first 57,000 records), and then

2) sort the records ascendingly (soas to have the last 3000 records displayed in ascending order).

Now, its amatter of filtering through those last 3000 ascending records for the alst 30 days. He doesn't want to have to change the date in the query every day. I'm thinking there would likely be a way to say "the computer's system time minus 30 days"; anid i'm just wondering if anyone here might know how i would say that and apply it to my query below.


SELECT
PROT_COM_HEADER.SYSTEM_TIME,
PROT_COM_HEADER.Machine,
PROT_COM_HEADER.RunOutErrorPinion,
PROT_COM_HEADER.RunOutErrorGear,
PROT_COM_HEADER.BestPos_Deviat_J,
PROT_COM_HEADER.[Description],
PROT_SFT_STD_Orders_Mesh_REF.REF_DMESH,
PROT_SFT_STD_Orders_Mesh_REF.REF_CMESH
FROM
PROT_COM_HEADER, PROT_SFT_STD_Orders_Mesh_REF
WHERE
PROT_COM_HEADER.SYSTEM_TIME = PROT_SFT_STD_Orders_Mesh_REF.SYSTEM_TIME
AND PROT_COM_HEADER.SYSTEM_TIME IN
(
SELECT TOP 3000 SYSTEM_TIME
FROM
PROT_COM_HEADER
ORDER BY SYSTEM_TIME DESC
)
ORDER BY PROT_COM_HEADER.SYSTEM_TIME ASC

Thanks!
Jim

[Lightning]

Asuming msSQL

SELECT 
PROT_COM_HEADER.SYSTEM_TIME,
PROT_COM_HEADER.Machine,
PROT_COM_HEADER.RunOutErrorPinion,
PROT_COM_HEADER.RunOutErrorGear,
PROT_COM_HEADER.BestPos_Deviat_J,
PROT_COM_HEADER.[Description],
PROT_SFT_STD_Orders_Mesh_REF.REF_DMESH,
PROT_SFT_STD_Orders_Mesh_REF.REF_CMESH
FROM 
PROT_COM_HEADER, PROT_SFT_STD_Orders_Mesh_REF 
WHERE 
PROT_COM_HEADER.SYSTEM_TIME = PROT_SFT_STD_Orders_Mesh_REF.SYSTEM_TIME
AND abs(datediff(d,PROT_COM_HEADER.SYSTEM_TIME,getdate())) <= 3
ORDER BY PROT_COM_HEADER.SYSTEM_TIME ASC