CODEWITHSUNDEEP
sqlsql-servert-sqlsubquery
TGuimond
TGuimond

Reputation: 5487

SELECT MAX Date T-SQL Subquery

I am attempting to SELECT rows from a table using a query like this

SELECT     pminf_member, pminf_schmem
         , pminf_date, pminf_fund
         , pminf_cont, pminf_rate
         , pminf_matrix
FROM   pe_minvf
WHERE (pminf_member = 4380) 
  AND (pminf_schmem = 'M') 
  AND (pminf_date <= '03/30/2011')
  AND (pminf_date =
           (SELECT MAX(pminf_date) AS Expr1
            FROM   pe_minvf AS pe_minvf_1
            WHERE  (pminf_member = 4380)
           )
       )
  AND (pminf_fund = 'LIFESTYLE')

What I should be getting from my subquery (I think) is a date of '01/01/2011' but when I run my query I am getting no results back.

If I replace the subquery with the hardcoded date I get the correct rows returned. For example

SELECT  pminf_member, pminf_schmem
      , pminf_date, pminf_fund
      , pminf_cont, pminf_rate
      , pminf_matrix
FROM  pe_minvf
WHERE (pminf_member = 4380)
  AND (pminf_schmem = 'M')
  AND (pminf_date <= '03/30/2011')
  AND (pminf_date = '01/01/2011')
  AND (pminf_fund = 'LIFESTYLE')

This query returns the correct results.

Any ideas why the subquery is not returning the max date or if it is, why am I getting no rows back?

Thanks, Tristan

Upvotes: 2

Views: 9897

Answers (5)

Hank Freeman
Hank Freeman

Reputation: 1212

Twelfth! Thanks for the proper syntax... I used it to resolve my similar problem de-jur (of-the-day) Unless I am wrong, what I have below will work.. If it doesn't I will fix it here... In the example below the data fields name have been cleansed but the syntax worked very fast. By the way, there are 20,480 distinct device_id

declare 
 @BegDate varchar(20)=(select cast(CONVERT(VARCHAR(20), getdate()-2,101) as varchar(20)) + ' 10:59:59 PM')
,@EndDate varchar(20)=(select cast(CONVERT(VARCHAR(20), getdate()-0,101) as varchar(20)) + ' 11:00:00 PM')
  select f1.ABC_ConfigProcStatusID,f1.DeviceID,f1.DBfilename ,f1.LastUpdatedDate
    from dbo.ABC_ConfigProcStatus f1
        inner join
          (select distinct DeviceID,max(LastUpdatedDate) as max_DeviceIDdte
            from dbo.ABC_ConfigProcStatus 
            where [Status]=2  
            and DeviceID not in(select ExclusionDeviceIDs from ABC_ConfigDeviceIDExclusionList)
            group by DeviceID) f2 
        on f2.max_DeviceIDdte = f1.LastUpdatedDate
   where [Status]=2  
   and f2.DeviceID = f1.DeviceID
   and f1.DeviceID not in(select ExclusionDeviceIDs from ABC_ConfigDeviceIDExclusionList)
   and LastUpdatedDate between @BegDate and @EndDate
   and left(upper(f1.DeviceID),3) in ('XYZ','ZKO')
   order by f1.LastUpdatedDate

Upvotes: 0

Twelfth
Twelfth

Reputation: 7180

use a subquery to limit rows returned instead of the where clause. Use this as the subquery:

(SELECT MAX(pminf_date) AS Expr1             
FROM   pe_minvf AS pe_minvf_1             
WHERE  (pminf_member = 4380)

Keep the query basically the same...

Select same_fields_as you_did_before
from pe_minvf
inner join 
 (SELECT MAX(pminf_date) AS Maxdate, tablekey             
 FROM   pe_minvf AS pe_minvf_1             
 WHERE  pminf_member = 4380) a on a.tablekey = pe_minvf.table_key
where same_where_clause_you_had

Make sense? I wasn't sure what your table_key was on pe_minvf...you'll have to insert that yourself. I find using subqueires adn inner joining to them is a more effective way of limiting rows then having a subquery in your where clause

Upvotes: 0

Longha
Longha

Reputation: 741

SELECT     pminf_member, pminf_schmem
         , pminf_date, pminf_fund
         , pminf_cont, pminf_rate
         , pminf_matrix
FROM   pe_minvf 
WHERE (pminf_member = 4380) 
  AND (pminf_schmem = 'M') 
  AND (pminf_date =
           (SELECT MAX(pminf_date) AS Expr1
            FROM   pe_minvf AS p
            WHERE  (p.pminf_member = 4380) AND 
                   p.pminf_date <= '03/30/2011'
           )
       )
  AND (pminf_fund = 'LIFESTYLE')

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

The query:

SELECT MAX(pminf_date) AS Expr
FROM   pe_minvf AS pe_minvf_1
WHERE  (pminf_member = 4380)
;

returns '01/01/2011' or something else?

Perhaps you want the same conditions on the subquery as in th emain query:

SELECT MAX(pminf_date) AS Expr
FROM   pe_minvf AS pe_minvf_1
WHERE  (pminf_member = 4380)
  AND  (pminf_schmem = 'M')

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425251

You filter on different conditions in your queries.

It's pminf_fund = 'LIFESTYLE' in the subquery but pminf_schmem = 'M' in the outer query.

Also, you limit the date in the outer query and don't do it in the subquery.

If you just need the most recent record up to '03/30/2011', use this:

SELECT  TOP 1
        pminf_member, pminf_schmem, pminf_date, pminf_fund, pminf_cont, pminf_rate, pminf_matrix
FROM    pe_minvf
WHERE   pminf_member = 4380
        AND pminf_schmem = 'M'
        AND pminf_fund = 'LIFESTYLE'
        AND pminf_date <= '03/30/2011'
ORDER BY
        pminf_date DESC

Upvotes: 6

Related Questions