Solved

MS SQL Most Recent Date

Posted on 2009-07-14
5
286 Views
Last Modified: 2012-05-07
Hi, I would like to select a number of tables based on the most recent date and where it is inactive.  The current problem is that if the dates are equal multiple tables come up (I just want one; the first one that it finds).  I tried using DISTINCT, but it didn't seem to work.


Here's how it's set up:
Table1:  Number
Table2:  id, Table1$Number, Table2$id
Table3:  id, IsActive

Table1 has many Table2.
Table2 has one Table1.
Table2 has one Table3.
Table3 has many Table2.


Here's what I have:
Table1.Number - Table2.Date - Table3.IsActive
123 - 7/17/2009 - Y
123 - 7/13/2009 - N
123 - 7/14/2009 - N
475 - 7/14/2009 - Y
475 - 7/14/2009 - Y
475 - 7/14/2009 - N
475 - 7/14/2009 - N


Here's what I want to output:
Table1.Number - Table2.Date - Table3.IsActive
123 - 7/14/2009 - N
475 - 7/14/2009 - N

Here's what I get:
Table1.Number - Table2.Date - Table3.IsActive
123 - 7/14/2009 - N
475 - 7/14/2009 - N     ' I'd like to have just one
475 - 7/14/2009 - N


Also if I can do the query below a better way, please show how.  I want to use MAX though.
I'm using Microsoft SQL Server 2005.
Thanks.
SELECT  Table1.Name

        CONVERT(varchar,Table2.Date,101) as Date

FROM    Table1                                          LEFT OUTER JOIN

        Table2 ON Table1.Name      = Table2.Table1$Name LEFT OUTER JOIN

        Table3 ON Table2.Table3$id = Table3.id

WHERE   Table2.Date IN (

          SELECT MAX(Date)

          FROM   Table2 x

          WHERE  Table1.name     = x.Table1$name

             AND x.Table3$id     = Table3.id

             AND Table3.IsActive = 'N'

        )

Open in new window

0
Comment
Question by:no1leftypitcher
  • 4
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Since you are specifically looking for IsActive = 'N', you can do this since the other values can be grouped on.
SELECT t1.Number, MAX(t2.Date) AS Date, t3.IsActive

FROM Table1 t1

INNER JOIN (

	Table2 t2 INNER JOIN Table3 t3

		ON t3.id = t2.Table3$id

) ON t2.Table1$Number = t1.Number

WHERE t3.IsActive = 'N'

GROUP BY t1.Number, t3.IsActive

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
If there is more to it than that, you can use an analytical statement with OVER keyword and ROW_NUMBER function to get the results you need given I have the joins correct.
SELECT t1.Number

, CONVERT(varchar, t2.Date, 101) AS Date

, t3.IsActive

FROM (

	SELECT t1.Number, t2.Date, t3.IsActive

	, ROW_NUMBER() OVER (PARTITION BY t1.Number ORDER BY t2.Date DESC) AS row

	FROM Table1 t1

	INNER JOIN (

		Table2 t2 INNER JOIN Table3 t3

			ON t3.id = t2.Table3$id

	) ON t2.Table1$Number = t1.Number

	WHERE t3.IsActive = 'N'

) t

WHERE t.row = 1;

Open in new window

0
 

Author Comment

by:no1leftypitcher
Comment Utility
Thanks for the comments, but when I ran the query, it said that OVER is not recognized/supported and that ROW_NUMBER() is not a valid function.  I guess my version of MS SQL is too old for these; is there another way?
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
These should work in MS SQL Server 2005, but in either case I provided this method http:#24855112, does it not work?
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
Comment Utility
For MS SQL Server 2000, you can simulate ROW_NUMBER() like this:
SELECT t1.Number

, CONVERT(varchar, t2.Date, 101) AS Date

, t3.IsActive

FROM (

        SELECT t1.Number, t2.Date, t3.IsActive

        , (SELECT COUNT(*) FROM Table1 INNER JOIN Table2 ON Table1$Number = Number WHERE Number = t1.Number AND Date >= t2.Date) AS row

        FROM Table1 t1

        INNER JOIN (

                Table2 t2 INNER JOIN Table3 t3

                        ON t3.id = t2.Table3$id

        ) ON t2.Table1$Number = t1.Number

        WHERE t3.IsActive = 'N'

) t

WHERE t.row = 1;

Open in new window

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now