Solved

SQL Query - Get most recent entry for each member

Posted on 2008-10-08
10
1,277 Views
Last Modified: 2008-10-20
I'm stuck on developing this query and could use some help. What I am trying to do is look at a table (BP) and find how long it has been since the last entry was made for each entity. The query I have developed does that, but if an entity has more than one entry in there, it shows both and I just want the most recent one displayed. Hopefully this makes sense.
SELECT b.BPID, a.EntityShortName, b.DateEntered, DateDiff(day,b.DateEntered,GetDate()) DaysSinceLastBP
FROM BP AS b 
INNER JOIN RiskMgmtAssignments AS a ON b.EntityID = a.EntityID 
INNER JOIN RMCs AS rm ON a.RMCID = rm.ID
WHERE (b.IsComplete = 1) AND (rm.Name = @username)
ORDER BY 
DateDiff(day,b.DateEntered,GetDate()) DESC

Open in new window

0
Comment
Question by:cciservices
  • 4
  • 4
  • 2
10 Comments
 
LVL 1

Accepted Solution

by:
cciservices earned 0 total points
ID: 22671465
I came up with this query that appears to do the trick, but if someone knows a more efficient way to get the same result, I'm happy to give you the points for it.
SELECT DISTINCT a.EntityShortName, 
(SELECT TOP 1 DateEntered FROM BP WHERE EntityID = a.EntityID ORDER BY DateEntered DESC) DateEntered, 
DateDiff(day,(SELECT TOP 1 DateEntered FROM BP WHERE EntityID = a.EntityID ORDER BY DateEntered DESC),GetDate()) DaysSinceLastBP
FROM BP AS b 
INNER JOIN RiskMgmtAssignments AS a ON b.EntityID = a.EntityID 
INNER JOIN RMCs AS rm ON a.RMCID = rm.ID
WHERE (b.IsComplete = 1) AND (rm.Name = @username)
ORDER BY DaysSinceLastBP DESC

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22671478
Try this:

select b.bpid,dateentered, datediff(d,b.dateentered,getdate()) DaysSinceLastBP
from (select bpid,entityid,dateentered = max(dateentered) from bp where iscomplete=1 group by bpid,entityid ) b
where exists (select 1 from riskmgmtassignments a join rmcs rm on a.rmcid=rm.id where a.entityid=b.entityid and rm.name=@username)
order by datediff(d,b.dateentered,getdate())  desc
0
 
LVL 3

Expert Comment

by:3abqari
ID: 22671505
Use the "Top" keyword with how many records you would like returned.

for example: Select Top 1 * from tablename


SELECT TOP 1 b.BPID, a.EntityShortName, b.DateEntered, DateDiff(day,b.DateEntered,GetDate()) DaysSinceLastBP
FROM BP AS b 
INNER JOIN RiskMgmtAssignments AS a ON b.EntityID = a.EntityID 
INNER JOIN RMCs AS rm ON a.RMCID = rm.ID
WHERE (b.IsComplete = 1) AND (rm.Name = @username)
ORDER BY 
DateDiff(day,b.DateEntered,GetDate()) DESC

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:cciservices
ID: 22671521
Brandon,
Nope. That didn't do it. Still pulls in more than 1 entry for each entity. Thanks, though.
0
 
LVL 1

Author Comment

by:cciservices
ID: 22671557
3abqari,
That is only going to get me one record total and that is not what I am looking for. I am looking for 1 entry per entity. For instance, there are 42 different entities and 60 entries. I need the most recent entries for those 42, so there should be 42 records returned with the most recent dateEntered for each of them.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22671581
which part is doing it?


select 1 from riskmgmtassignments a join rmcs rm on a.rmcid=rm.id where rm.name=@username

or

select bpid,entityid,dateentered = max(dateentered) from bp where iscomplete=1 group by bpid,entityid
0
 
LVL 3

Expert Comment

by:3abqari
ID: 22671592
Have you tried adding a group by clause?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22671595
What does this give you?


select b.bpid,dateentered, datediff(d,b.dateentered,getdate()) DaysSinceLastBP
from (select entityid,dateentered = max(dateentered) from bp where iscomplete=1 group by entityid ) b
where exists (select 1 from riskmgmtassignments a join rmcs rm on a.rmcid=rm.id where a.entityid=b.entityid and rm.name=@username)
order by datediff(d,b.dateentered,getdate())  desc

0
 
LVL 1

Author Comment

by:cciservices
ID: 22671644
That doesn't work either because you took bpid out of your subquery and adding into it and and the group by makes it return too many records as well. Did you see my second sql statement that I came up with that is working for me (ID:22671465)? I'm going to use that, but if you can write it in a more efficient manner, I'll give you the points.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22671861
I'm trying to figure out where your one to many relationships occur which is what I was getting at in http:#22671581.

Do you have many BP to each RiskMgmtAssignments?
Do you have many RMCs to each RiskMgmtAssignments?

vice versa on either?
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 37
Live Storage Vmotion VMs with shared VMDK 10 59
SQL View nearest date 5 36
SQL Server Import/Error Wizard error 12 19
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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