[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1287
  • Last Modified:

SQL Query - Get most recent entry for each member

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
cciservices
Asked:
cciservices
  • 4
  • 4
  • 2
1 Solution
 
cciservicesAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
3abqariCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cciservicesAuthor Commented:
Brandon,
Nope. That didn't do it. Still pulls in more than 1 entry for each entity. Thanks, though.
0
 
cciservicesAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
3abqariCommented:
Have you tried adding a group by clause?
0
 
BrandonGalderisiCommented:
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
 
cciservicesAuthor Commented:
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
 
BrandonGalderisiCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now