CODEWITHSUNDEEP
sqlsql-serversubquerymaxaggregate
tonytone
tonytone

Reputation: 81

SQL Select Statement for Charge Code with Max Date and Second Most Max Date

Pretty simple query I'm trying to do.

I'm trying to select charge codes with two dates:

1) the most recent/MAX effective date and 2) the date that occurs before the most recent/MAX date in a table.

The charge codes are listed numerous times in the table with various corresponding effective dates. Some charge codes are listed once with only one effective date and others are listed 8 times with 8 effective dates.

Here's what I'm trying to do:

select 
         BP.[mnemonic] [Charge Code]
        ,MAX (BP.[std_effective_date]) [Max date] 
        ,BP2.[Date Prior to Max]

from
    [TABLE1] BP
    left outer  join
    (select distinct [mnemonic], MAX ([std_effective_date]) [Max Date] 
         from 
         [TABLE1] 
    where [std_effective_date] < BP.[std_effective_date] group by [mnemonic]) BP2
    ON BP.[mnemonic] = BP2.[mnemonic]  

where    
         BP.[mnemonic] IN ('38066','38073','38080')
         group by BP.[mnemonic]

I know I can't reference the table in the outer query in the subquery in the join (even though it's the same table) but how would I do something similar? Or how would you suggest getting the 2nd max date?

There are no row numbers in the table unfortunately.

Please help. Thanks.

Sample Results

Charge Code Max date Date Prior to Max

38066 2013-02-01 2013-02-01

38073 2013-02-01 2013-02-01

Upvotes: 0

Views: 1836

Answers (3)

Lamak
Lamak

Reputation: 70668

Assuming SQL Server (the syntax on your question seems like you are using it), you can use OUTER APPLY:

SELECT  A.[mnemonic] [Charge Code], 
        A.[Max date], 
        B.[std_effective_date] [Date Prior to Max]
FROM (  SELECT  [mnemonic],
                MAX([std_effective_date]) [Max date] 
        FROM TABLE1
        GROUP BY [mnemonic]) A
OUTER APPLY (SELECT TOP 1 [std_effective_date]
             FROM TABLE1
             WHERE [mnemonic] = A.[mnemonic]
             AND [std_effective_date] < A.[Max date]
             ORDER BY [std_effective_date] DESC) B

Here is a sqlfiddle with a demo of this.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146557

Try this 

 Select b.mnemonic ChargeCode,
    b.std_effective_date Maxdate,
    b.DatePriortoMax
 from TABLE1 b
 Where std_effective_date =
     (Select Max(std_effective_date) From Table1
      Where mnemonic = b.mnemonic) 
  Or std_effective_date =
     (Select Max(std_effective_date) From Table1
      Where mnemonic = b.mnemonic
         And std_effective_date < b.std_effective_date)

Upvotes: 0

T I
T I

Reputation: 9943

SELECT t1.mnemonic
    , MAX(t1.std_effective_date) current
    , MAX(t2.std_effective_date) previous
FROM tbl1 t1
LEFT JOIN tb1 t2 ON t2.mnemonic = t1.mnemonic
                AND t2.std_effective_date < t1.std_effective_date
WHERE t1.mnemonic IN ('38066','38073','38080')
GROUP BY t1.mnemonic

Upvotes: 2

Related Questions