Select Max if value of specific field is 'Y'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Max if value of specific field is 'Y'

Author

Topic

CThomp
Starting Member

5 Posts

Posted - 2014-05-15 : 09:25:45

I need to check if the latest record in a table for the current client, based on EFFECTIVE_DATE, has a value of 'Y' in the STATUS field. Sample records for client #19:

CLIENT EFFECTIVE_DATE STATUS
------ -------------- ------
19 08/13/2010 Y
19 01/10/2013 N
19 02/03/2014 Y

If I run:
SELECT CLIENT, max(EFFECTIVE_DATE), STATUS
FROM FTABLE GROUP BY CLIENT, STATUS

I get the two latest records since STATUS is different:

CLIENT EFFECTIVE_DATE STATUS
------ -------------- ------
19 01/10/2013 N
19 02/03/2014 Y

I wrote the following and it works, but it may be causing 'Insufficient Disk Space' error from the database.
Is there any way to write this query without temp table?

DECLARE @TEMPMAX

TABLE (CLIENT INT, EDATE DATE)
INSERT INTO @TEMPMAX (CLIENT, EDATE)

SELECT CLIENT_NO, max(EFFECTIVE_DATE)
FROM FTABLE
WHERE CLIENT_NO = :CLIENT_NO {the active client}
GROUP BY CLIENT_NO

SELECT
F.CLIENT_NO, F.EFFECTIVE_DATE, F.STATUS

FROM @TEMPMAX T, FTABLE F
WHERE T.CLIENT=F.CLIENT_NO
AND T.EDATE=F.EFFECTIVE_DATE
AND F.STATUS='Y'
AND F.CLIENT_NO = :CLIENT_NO {the active client}
AND CAST(F.EFFECTIVE_DATE AS DATE) <> CAST(GETDATE() AS DATE)

CLIENT EFFECTIVE_DATE STATUS
------ -------------- ------
19 02/03/2014 Y

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 09:54:06

Your sample query won't compile. What do mean by ":CLIENT_NO"? What is the colon for? It isn't recognized by SQL Server. How big do you expect @TEMPMAX to grow? If more than a few dozen rows, consider using a temporary table instead.

CThomp
Starting Member

5 Posts

Posted - 2014-05-15 : 10:15:46

Thank you for the reply -
":CLIENT_NO" is recognized in the application where I am running the query, to retrieve the client number currently being viewed.
This can be replaced with "19" to retrieve the sample records.
The query could run numerous times a day, each time a certain GUI screen in the application is displayed. Should I use #TEMP instead of @TEMP?

quote:
Originally posted by gbritton

Your sample query won't compile. What do mean by ":CLIENT_NO"? What is the colon for? It isn't recognized by SQL Server. How big do you expect @TEMPMAX to grow? If more than a few dozen rows, consider using a temporary table instead.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 10:47:04

Use a temporary table if it will hold more than a few records. Especially since you have a join in your last query. you can put an index on a temporary table which can make an enormous difference in runtime.

If it were me, I'd get the query running in SSMS before porting it to an application. That's a better environment to troubleshoot and see the performance effects of various options.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources