Query based on latest date per user 1

[jay24k]

I'm trying to pull some information from a SQL database.

I'll explain a bit what the table is comprised of to better explain my problem.

I have data inputed into the table from an audit that runs on a PC. Each PC has an workstation number and it records the date it was audited. Because each workstation could be audited 1 or more times a day or 5 or 6 a month, I'm not sure how to get only the last audit date information off the PC. I can't think of the SQL statement that would benefit me.

For example. Lets say first audit shows office. 2nd shows it has an extra and the third shows it was uninstalled. How do I pull just the latest date of information. Now there is multiple WSID in this table. Thanks!
WSID DATE Software
123 1/1/04 Office
123 1/2/04 Office,PcAnywhere,Ghost
123 1/3/04 Ghost

 

[manjarikosaraju]

Try this.. Hope this is what you are looking for.

select a.*
from Table1 a
join
(select wsid, max(date) as date from Table1 group by wsid)b
on a.wsid = b.wsid and a.date = b.date

Thanks,
Manjari.

 

[SQLBill]

What type of data type is DATE?
Your column design for Software is a poor design. Each different Software should have it's own row.

ie.
WSID DATE Software
123 1/1/04 Office
123 1/2/04 Office
123 1/2/04 PcAnywhere
123 1/2/04 Ghost
123 1/3/04 Ghost

By the way, your SQL database IS a Microsoft SQL Server database correct? If not, you are posting in the wrong forum.

-SQLBill

 

[jay24k]

Yes it is SQL running on server 03. That was a made up example. Actually it is too tables that I have joined. One has history of audits with WSID and the other is WSID with other fields like FileCapture that I do. I join the two tables and then am trying to do a listing of latest date of each WSID and only show the latest file capture. It's not my software. It's a software called Track-It.

I was trying to do it in crystal reports but can't figure out where to do SQL statements.

Do you all think distinct will work while using the MAX command?

 

[SQLBill]

Why do you want to use DISTINCT with MAX? MAX already makes it DISTINCT.

If I ask for the row with the MAX(DATE), I will only get one row back.

If I ask for the rows with the MAX(DATE) GROUPed BY WSID, I will get one row for each WSID.

Without know what your tables look like and what you really want, I would suggest you doing a subSELECT to get the MAX([Date]) value for each WSID. Then in the outer SELECT, pull the records based on the MAX([Date]) retrieved in the subSELECT.

As for the actual script, why not make a stored procedure in SQL Server and run it from Crystal Reports?

-SQLBill

 

[jay24k]

Ok I appreciate the help. I will give that a try and see what I can do. Thanks for your help.

 

[dbomrrsm]

Bill

I found out today that MAX doesnt always make things distinct - select max date by ID and if there are two exact dates then MAX isnt always distinct.

[blue]DBomrrsm[/blue]

 

[SQLBill]

Good point that I didn't think of. Although with DATETIME datatype finding an exact match would be difficult.

-SQLBill

 

[donutman]

But DISTINCT isn't going to help either. You need another rule to decide which of the duplicate MAX's to take. Jay hasn't said what he wants to do in that case.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]