Use the most current date in a query? 1

[Krash878]

I have a query where a user is loging information on a monthly basis. We are monitoring changes in performance. the issue I am having is getting the query to show the most current date

Client Code DateChecked ASP Capacity ASP Percent
BI 4/5/2004 3.93G 85.8167
BI 5/6/2004 3.93G 87.2490
BI 6/14/2004 3.93G 87.2490
BI 7/14/2004 3.93 G 89.0868
CV 3/29/2004 17.18G 91.7392
CV 5/6/2004 17.18G 91.4153
CV 6/14/2004 17.18G 90.5943
LA 5/6/2004 16.77G 86.8870
LA 6/10/2004 16.77G 86.8870
LA 6/30/2004 16.77G 89.1083
LA 7/26/2004 16.77 G 89.4949
PEDC 3/29/2004 1389M 85.6482
PEDC 5/10/2004 1389M 93.5391

For Client BI through PEDC, I just want the most current date since it is the most current record of that client.

What would I put in the criteria to get this right?

Thanks,

Krash

 

[amorous]

Try this:

SELECT t1.*
FROM [MyTable] t1
WHERE t1.DateChecked In (select top 1 t2.DateChecked from [MyTable] t2 where t2.ClientCode = t1.ClientCode ORDER BY t2.DateChecked Desc)


-VJ

 

[Krash878]

ok, I just got confused.

this is the way my SQL looks

SELECT [Monthly Updates Query].[Client Code], [Monthly Updates Query].DateChecked, [Monthly Updates Query].[ASP Capacity], [Monthly Updates Query].[ASP Percent]
FROM [Monthly Updates Query]
ORDER BY [Monthly Updates Query].[Client Code], [Monthly Updates Query].DateChecked;

I am not sure what t1 and t2 are.

Krash

 

[Krash878]

I tried this and I get a syntax error

SELECT [Monthly Updates].[Client Code], [Monthly Updates].DateChecked, [Monthly Updates].Version, [Monthly Updates].[PTF Level], [Monthly Updates].[ASP Percent], [Monthly Updates].LNBU, [Monthly Updates].LFSS,t1.*
FROM [Monthly Updates]t1
WHERE t1.DateChecked In (select top 1 t2.DateChecked from [Monthly Updates] t2 where t2.Client Code = t1.Client Code ORDER BY t2.DateChecked Desc);

Krash

 

[Krash878]

I started over with a new query based on the table instead of a query to simplify things.

Krash

 

[amorous]

t1 and t2 are the aliases for your table

try this:

SELECT t1.[Client Code], t1.DateChecked, t1.Version,
t1.[PTF Level], t1.[ASP Percent], t1.LNBU, t1.LFSS
FROM [Monthly Updates] t1
WHERE t1.DateChecked In (select top 1 t2.DateChecked from [Monthly Updates] t2 where t2.[Client Code] = t1.[Client Code] ORDER BY t2.DateChecked Desc);

-VJ

 

[Krash878]

That worked perfect. Now I can build more queries based off of that one.

Thanks so much.

Krash

 

[amorous]

Queries depend on the kind of output you want...you cant just use one query as a base query...

but in the above example...instead of 1 recent date.. you can get 2, 3 , 5 0r 10 recent date records...

just change top 1 to top 2 and so on respectively...



-VJ

 

[Krash878]

I am checking up on our clients AS400 systems to see if they are doing nightly and full system backups and how full the harddrives are.

I have to generate 3 onscreen reports that tell us which clients are being bad and at the most risk. When the client then gets the backups done or frees up the space then I will see it and log it. I did not want to see all the clients past records even though the boss wants them for tracking purposes. I just want a "Who is a problem now?" and I got exactly that with your help. I do keep all these tips filed for future help.

Again I thank you,

Krash