?
Solved

T-SQL MAX Select Query

Posted on 2011-04-19
3
Medium Priority
?
311 Views
Last Modified: 2012-05-11
I am trying to figure out the last recorded date for a particular processID for a specific JobNumber.

This query returns all JobNumbers from a table that meet the filter, and I am getting the recorded dates from a different table.

The problem is the table with the dates may have multiple recorded times by ProcessID and JobNumber.

See in code sample in the CASE statment, I try the MAX but it returns the maximum number for all JobNumber that have ProcessID = 0

How can I dynamically filter by Jobnumber if I don't know the jobnumbers until query exection?
select 
Ccustno, 
JobNumber, 
convert(varchar(10),DueDate,101) as DueDate, 
MailClass,
CASE [0] 
when 1 then (Select Convert(varchar(20),MAX([StopTime]),120) as [StopTime] from [JobStepsStartStopTimes] where [Processid] = 0) 
when 0 then ''
END
as [PUT ON COMPUTER]
from dbo.sales_jobentry 
where [8] = 0 and convert(varchar(10),DueDate,101) = convert(varchar(10),getdate() + 1,101)
order by Ccustno asc

Open in new window

0
Comment
Question by:handyjay
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
hyphenpipe earned 2000 total points
ID: 35426461

select	ccustno
		, jobnumber
		, convert(varchar(10),duedate,101) as duedate
		, mailclass
		, case [0] 	when 1 then ca.stoptime
					when 0 then '' end as [put on computer]
from dbo.sales_jobentry s
cross apply (select stoptime = convert(varchar(20),max([stoptime]),120) from [jobstepsstartstoptimes] j where [processid] = 0 and s.jobnumber = j.jobnumber) ca
where [8] = 0 and convert(varchar(10),duedate,101) = convert(varchar(10),getdate() + 1,101)
order by ccustno asc

Open in new window

0
 

Author Comment

by:handyjay
ID: 35426506
Thank you, I did not know about "cross apply"

In the intern I wrote it a bit different using a join on jobnumber.

Which is more efficient?
select 
Ccustno, 
s.JobNumber, 
convert(varchar(10),DueDate,101) as DueDate, 
MailClass,
CASE [0] 
when 1 then (Select Convert(varchar(20),MAX([StopTime]),120) as [StopTime] from [JobStepsStartStopTimes] where [Processid] = 0 and jobnumber = s.jobnumber) 
when 0 then ''
END
as [PUT ON COMPUTER]
from dbo.sales_jobentry s join [JobStepsStartStopTimes] j
on s.jobnumber = j.jobnumber
where [8] = 0 and convert(varchar(10),DueDate,101) = convert(varchar(10),getdate() + 1,101)
group by ccustno,s.JobNumber,convert(varchar(10),DueDate,101),MailClass,[0] 
order by Ccustno asc

Open in new window

0
 

Author Closing Comment

by:handyjay
ID: 35426880
Thanks
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

864 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