Solved

SQL using MAX in one table and parameter in another

Posted on 2014-03-28
6
392 Views
Last Modified: 2014-03-28
Hi experts
I have 2 tables 'Session' and 'Count' , I want to retrieve the most recent Count.CountDate for any inputted Session.TrayNumber ie:

Session

Session_ID      TrayNumber
1001                 3
1002                 2
1003                 2
1004                 2

 Count

Session_ID      CountDate
1001             30/03/2014
1002             22/03/2014
1003             24/03/2014
1004             26/03/2014

So if I inputted Tray Number = 2 then I get the most recent date for that TrayNumber = 26/03/2014

What I have at the moment will only get the MAX CountDate for all the dates in the CountDate column ie:

SELECT *
FROM Session, Count
WHERE Session.Session_ID = Count.Session_ID
AND Count.CountDate = (SELECT MAX(CountDate)
FROM Count WHERE Session.TrayNumber = @TrayNumber)

Hope that this makes sense and someone can help.
0
Comment
Question by:dizzycat
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39961933
I think this article should be read:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

in code:
select c.Session_ID , c.CountDate
 from (
SELECT c.Session_ID , c.CountDate
, row_number() over (partition by c.Session_ID  order by c.CountDate desc) rn
FROM Session s
JOIN [Count] c
ON s.Session_ID = c.Session_ID
WHERE s.TrayNumber = @TrayNumber
) sq
where sq.rn = 1 

Open in new window

0
 

Author Comment

by:dizzycat
ID: 39961979
I'm very sorry but I forgot to mention i'm using the query in a Dataset which dosen't allow the use of Joins.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39961987
no reason for the subquery:
select 1001 ID, 3 Tray into #temp

insert into #temp select 1002, 2
insert into #temp select 1003, 2
insert into #temp select 1004, 2

select 1001 Id, cast ('3/30/2014' as smalldatetime) CD into #temp2

insert into #temp2 select 1002, '3/22/2004'
insert into #temp2 select 1003, '3/26/2004'
insert into #temp2 select 1004, '3/30/2004'


-- final query
select max(CD) CD from #temp t
join #temp2 t2 on t.ID = t2.ID
where t.tray = 2 -- change to @trayNumber
group by t.tray


drop table #temp
drop table #temp2

Open in new window

0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39961995
Just a note it IS possible to join on tables in a dataset:
http://msdn.microsoft.com/en-us/library/bb386969(v=vs.110).aspx
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39962132
If you just need the date, you can do this:


DECLARE @TrayNumber int
SET @TrayNumber = 2

SELECT MAX(c.CountDate) AS LastCountDate
FROM [Count] c
WHERE
    c.Session_ID IN (
        SELECT s.Session_ID
        FROM Session s
        WHERE
            s.TrayNumber = @TrayNumber
        )


If you need other columns, let me know, and I will post the relevant query.
0
 

Author Closing Comment

by:dizzycat
ID: 39962183
That's great, thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

860 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