SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Max if value of specific field is 'Y'
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CThomp
Starting Member

5 Posts

Posted - 05/15/2014 :  09:25:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1077 Posts

Posted - 05/15/2014 :  09:54:06  Show Profile  Reply with Quote
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.
Go to Top of Page

CThomp
Starting Member

5 Posts

Posted - 05/15/2014 :  10:15:46  Show Profile  Reply with Quote
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.

Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1077 Posts

Posted - 05/15/2014 :  10:47:04  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000