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
 Old Forums
 CLOSED - General SQL Server
 Filter & Sort by a column containing dates

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-17 : 08:21:36
Russ writes "I've got a table that contains patching information on my workstations. For report gathering, we keep up to 1 weeks of current information in the table and everything else is archived off. The data is gathered three times per week, thus each machine reporting, provided it's online during the scanning process, could have muliple records for the same patch.

Example Data:
MACHINE_NAME__PRE_WINDOWS_2000_1 REPORT_RUN_DATE_TIME2 BULLETIN_ID4 QNUMBER5
ARFLCPSAS001 3/13/2006 8:36:43 AM MS04-011 Q835732
ARFLCPSAS001 Feb 28 2006 10:27AM MS04-011 Q835732

I need to build a view that only displays the records based on the most recent date indicated by the "report_run_date_time2" column. The date can't be hardcoded as it changes 3 times per week each week. I'm stumped on how to do this.

The table name is BV8SrvPatchesAll
Columns involved are:
MACHINE_NAME__PRE_WINDOWS_2000_1.
REPORT_RUN_DATE_TIME2
BULLETIN_ID4
QNUMBER5

Any help is appreciated.

Russ"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-17 : 08:52:28
Do you want to get latest three weeks data?

Select Columns from yourTable
where datecol>=dateadd(day,datediff(day,0,getdate()),-21)
and datecol<dateadd(day,datediff(day,0,getdate()),1)


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -