Whats the most efficient way to get related record of a group by query?

Hi Experts,

I have following 2 tables

Orientations
ID - AutoNum
FacilityID
EmployeeID

OrientationNotes
ID - AutoNum
OrientationID - points to Orientations.ID
Notes

Now would like to get in one query the last note per EmployeeID, along with the OrientationID & FacilityID from that record.

Would like to have the most efficient way, including the simplest regarding clearness?
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
how do you define "last note" ??
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
anyway, try something like this:

declare @Orientations table
(
	ID int,
	FacilityID int,
	EmployeeID int
)
insert into @Orientations
values
(1, 1, 123),
(2, 1, 456),
(3, 1, 789),
(4, 2, 222),
(5, 2, 333),
(6, 3, 444);

declare @OrientationNotes table
(
	ID int,
	OrientationID int,
	Notes varchar(500)
)
insert into @OrientationNotes
values
(1, 1, 'Hello World 1'),
(2, 1, 'Hello World 2'),
(3, 3, 'Hello World 3'),
(4, 4, 'Hello World 4'),
(5, 1, 'Hello World 5'),
(6, 5, 'Hello World 6'),
(7, 5, 'Hello World 7');

;with cte as
(
	select OrientationID, Notes, row_number() over (partition by OrientationID order by ID desc) idx from @OrientationNotes
)
select
a.ID, a.FacilityID, a.EmployeeID, b.Notes
from @Orientations a
left join (select * from cte where idx = 1) b
on a.ID = b.OrientationID

Open in new window

0
Vaibhav GoelMSBI , SQL ConsultantCommented:
HELLO bfuchs,
 
SELECT One.EmployeeID,One.OrientationID,Two.Notes FROM
(
      SELECT EmployeeID,FacilityID,MAX(Id) Id FROM Orientations GROUP BY EmployeeID,FacilityID
) AS One CROSS APPLY(
      SELECT TOP 1 Notes FROM OrientationNotes AS ONotes WHERE ONotes.OrientationID = One.Id ORDER BY ONotes.Id DESC
) AS Two

Vaibhav
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bfuchsAuthor Commented:
Hi Ryan,
how do you define "last note" ??
By max ID AutoNum of OrientationNotes table.

The problem with that suggestion is as following.

I need to include this in a store proc sql and replace the existing as follows.

if @strRetColums is not null
	set @tmp = 'Select ' + @strRetColums
set @tmp = @tmp + ' FROM         dbo.Orientations INNER JOIN
                      dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN
                      dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
(SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM         
dbo.OrientationNotes o2 ' + @strWhere1 + 
'  ) SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM         
dbo.OrientationNotes o2

Open in new window


Now instead of the following
SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM         
dbo.OrientationNotes o2

Open in new window

I need to have the last note.
Not sure how I can accomplish that with yours..

Thanks,
Ben
0
bfuchsAuthor Commented:
@Vaibhav,

I get an error "Invalid column name OrientationID", however the column does exists..

Thanks,
Ben
0
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello bfuchs,

Revised

SELECT One.EmployeeID,FacilityID,Two.Notes FROM
(
      SELECT EmployeeID,FacilityID,MAX(Id) Id FROM Orientations GROUP BY EmployeeID,FacilityID
) AS One CROSS APPLY(
      SELECT TOP 1 Notes FROM OrientationNotes AS ONotes WHERE ONotes.OrientationID = One.Id ORDER BY ONotes.Id DESC
) AS Two

Vaibhav
0
bfuchsAuthor Commented:
OK

Regarding efficiency Ryan's is much better, 3 vs. 7 sec.
However I first must see which is accurate.
Ryan gives me much more records than Vaibhav..
I changed Ryan's left join for an inner join but still see a difference in results.
Can someone help figure out what's the diff?
Also if I'm to apply Ryan's suggestion (due to efficiency), how can I have that encapsulated in another SQL statement?

Thanks,
Ben
0
bfuchsAuthor Commented:
@Vaibhav,

It doesn't look like the logic is correct..
I need the last note per employee, and from there to view the related orientation fields.
While the following gives me the last Orientation per employee
SELECT EmployeeID,FacilityID,MAX(Id) Id FROM Orientations GROUP BY EmployeeID,FacilityID

Open in new window


Thanks,
Ben
0
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Ben

Impressive.

Thank you very much for the feedback. I request you provide some sample data from the tables and the output you are expecting

Vaibhav
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
if @strRetColums is not null
      set @tmp = 'Select ' + @strRetColums
set @tmp = @tmp + ' FROM         dbo.Orientations INNER JOIN
                      dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN
                      dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
(SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM        
dbo.OrientationNotes o2 ' + @strWhere1 +
'  ) SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM        
dbo.OrientationNotes o2

i think the sp script posted above is not complete so can't really give you a direct answer.

but it seems that you got some info from other tables, such as Employeestbl and Facilitiestbl, but to make it simple, you can always join your tables together using CTE syntax, to make your SQL statement looks easier and more easy to understand.
0
bfuchsAuthor Commented:
@Ryan,

This is the complete stored proc.
USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[proc_FilterOrientationNotes1]    Script Date: 12/19/2017 13:16:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_FilterOrientationNotes1]
(@strWhere varchar(5000),
 @strRetColums varchar(500) = null

 )
AS 
declare @tmp varchar(5000)
declare @strWhere1 varchar(5000)
declare @strWhere2 varchar(5000)

set @strWhere1 = (select [value] from ProgrammingSettings where [Code] = 'UniqueFilterOrientationNotesSql')

if @strRetColums is null

	set @tmp='SELECT     TOP (99.9999) PERCENT CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR
                      Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red, dbo.OrientationNotes.ID, dbo.Orientations.EmployeeID, 
                      dbo.Orientations.FacilityID, dbo.Orientations.Active, dbo.OrientationNotes.Note, COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered) 
                      AS Day, dbo.OrientationNotes.Initial, dbo.Orientations.ID AS OrientationID, dbo.Orientations.DateEntered, dbo.Orientations.Initial AS OrientationInitial, 
                      dbo.Orientations.Traveler, dbo.EmployeesDocsTov.TovInfoLastDay AS LastDay, dbo.Orientations.DueDate, dbo.OrientationNotes.Mailing, 
                      dbo.Orientations.DueDateInitial, dbo.EmployeesDocsTov.Last_Orient_Note_Day, dbo.Employeestbl.AvailibilityPDays, 
                      dbo.Employeestbl.AvailibilityPShifts, dbo.Employeestbl.City, dbo.Employeestbl.LastName, dbo.Employeestbl.FirstName, dbo.Employeestbl.Title, dbo.Employeestbl.AddressLine1,
                      dbo.Facilitiestbl.Name AS FacilityName, note.LastNoteID, dbo.EmployeesDocsTov.TovInfoFacility1 AS LastFacility, 
                      dbo.Employeestbl.Degree, dbo.Employeestbl.NotSendMassTextMsg, dbo.Employeestbl.NotSendMassEmail, dbo.Employeestbl.Email, 
                      dbo.Employeestbl.Beeper, dbo.Employeestbl.SMSProvider, dbo.Employeestbl.ExpectedGraduation,
					  dbo.Employeestbl.LastName + '' '' + dbo.Employeestbl.FirstName As EmpName'
if @strRetColums is not null
	set @tmp = 'Select ' + @strRetColums
set @tmp = @tmp + ' FROM         dbo.Orientations INNER JOIN
                      dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN
                      dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
(SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM         
dbo.OrientationNotes o2 ' + @strWhere1 + 
'  ) note ON dbo.Orientations.ID = note.OrientationID
                       LEFT OUTER JOIN
                      dbo.EmployeesDocsTov ON dbo.Orientations.EmployeeID = dbo.EmployeesDocsTov.EmployeeID LEFT OUTER JOIN
                      dbo.OrientationNotes ON dbo.Orientations.ID = dbo.OrientationNotes.OrientationID'


IF @strWhere IS not NULL and @strwhere <> 'ALL' and @strwhere <> 'Long' set @tmp = @tmp + ' where ' + @strWhere 

IF @strWhere IS NULL set @tmp = @tmp + ' where Orientations.EmployeeID = 3' 

if @strWhere = 'Long'
begin
	set @strWhere2 = (select [value] from ProgrammingSettings where [Code] = 'FilterOrientationNotesSql')
	set @tmp = @tmp + ' where ' + @strWhere2 
end



	--if charindex('order by',@strWhere)= 0 set @tmp = @tmp + ' ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc'
	if charindex('order by',@tmp)= 0 set @tmp = @tmp + ' ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc'
execute (@tmp)

Open in new window


As mentioned, I'm trying to replace the following code which gives me the last note per orientation (passing to @strWhere1  criteria + GroupBy OrientationID)
 LEFT OUTER JOIN
(SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM         
dbo.OrientationNotes o2 ' + @strWhere1 + 
'  ) note ON dbo.Orientations.ID = note.OrientationID

Open in new window


With what you had suggested, which should give me the last note per employee.

Thanks,
Ben
0
bfuchsAuthor Commented:
@Vaibhav,

Sorry I didnt meant to offend, just was testing for accurancy.

For creating test scenario, we can use what Pyan posted above,
declare @Orientations table
(
	ID int,
	FacilityID int,
	EmployeeID int
)
insert into @Orientations
values
(1, 1, 123),
(2, 1, 456),
(3, 1, 789),
(4, 2, 222),
(5, 2, 333),
(6, 3, 444);

declare @OrientationNotes table
(
	ID int,
	OrientationID int,
	Notes varchar(500)
)
insert into @OrientationNotes
values
(1, 1, 'Hello World 1'),
(2, 1, 'Hello World 2'),
(3, 3, 'Hello World 3'),
(4, 4, 'Hello World 4'),
(5, 1, 'Hello World 5'),
(6, 5, 'Hello World 6'),
(7, 5, 'Hello World 7');

Open in new window


Thanks,
Ben
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try something like this:

ALTER PROCEDURE [dbo].[proc_FilterOrientationNotes1]
(@strWhere varchar(5000),
 @strRetColums varchar(500) = null

 )
AS 
declare @tmp varchar(5000)
declare @strWhere1 varchar(5000)
declare @strWhere2 varchar(5000)
declare @extra varchar(5000)

set @strWhere1 = (select [value] from ProgrammingSettings where [Code] = 'UniqueFilterOrientationNotesSql')

set @extra = ';with o2 as
(
	select OrientationID, Notes, row_number() over (partition by OrientationID order by ID desc) idx from OrientationNotes
)'

if @strRetColums is null

	set @tmp='SELECT     TOP (99.9999) PERCENT CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR
                      Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red, dbo.OrientationNotes.ID, dbo.Orientations.EmployeeID, 
                      dbo.Orientations.FacilityID, dbo.Orientations.Active, dbo.OrientationNotes.Note, COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered) 
                      AS Day, dbo.OrientationNotes.Initial, dbo.Orientations.ID AS OrientationID, dbo.Orientations.DateEntered, dbo.Orientations.Initial AS OrientationInitial, 
                      dbo.Orientations.Traveler, dbo.EmployeesDocsTov.TovInfoLastDay AS LastDay, dbo.Orientations.DueDate, dbo.OrientationNotes.Mailing, 
                      dbo.Orientations.DueDateInitial, dbo.EmployeesDocsTov.Last_Orient_Note_Day, dbo.Employeestbl.AvailibilityPDays, 
                      dbo.Employeestbl.AvailibilityPShifts, dbo.Employeestbl.City, dbo.Employeestbl.LastName, dbo.Employeestbl.FirstName, dbo.Employeestbl.Title, dbo.Employeestbl.AddressLine1,
                      dbo.Facilitiestbl.Name AS FacilityName, note.LastNoteID, dbo.EmployeesDocsTov.TovInfoFacility1 AS LastFacility, 
                      dbo.Employeestbl.Degree, dbo.Employeestbl.NotSendMassTextMsg, dbo.Employeestbl.NotSendMassEmail, dbo.Employeestbl.Email, 
                      dbo.Employeestbl.Beeper, dbo.Employeestbl.SMSProvider, dbo.Employeestbl.ExpectedGraduation,
					  dbo.Employeestbl.LastName + '' '' + dbo.Employeestbl.FirstName As EmpName'

if @strRetColums is not null
	set @tmp = 'Select ' + @strRetColums

if COALESCE(@strWhere1,'') = ''
	set @strWhere1 = ' where o2.idx = 1 '
else
	set @strWhere1 = @strWhere1 + ' and o2.idx = 1 '

set @tmp = @tmp + ' FROM         dbo.Orientations INNER JOIN
                      dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN
                      dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
(SELECT     o2.OrientationID,  o2.ID AS LastNoteID
FROM         
 o2 ' + @strWhere1 + 
'  ) note ON dbo.Orientations.ID = note.OrientationID
                       LEFT OUTER JOIN
                      dbo.EmployeesDocsTov ON dbo.Orientations.EmployeeID = dbo.EmployeesDocsTov.EmployeeID LEFT OUTER JOIN
                      dbo.OrientationNotes ON dbo.Orientations.ID = dbo.OrientationNotes.OrientationID'


IF @strWhere IS not NULL and @strwhere <> 'ALL' and @strwhere <> 'Long' set @tmp = @tmp + ' where ' + @strWhere 

IF @strWhere IS NULL set @tmp = @tmp + ' where Orientations.EmployeeID = 3' 

if @strWhere = 'Long'
begin
	set @strWhere2 = (select [value] from ProgrammingSettings where [Code] = 'FilterOrientationNotesSql')
	set @tmp = @tmp + ' where ' + @strWhere2 
end

	--if charindex('order by',@strWhere)= 0 set @tmp = @tmp + ' ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc'
	if charindex('order by',@tmp)= 0 set @tmp = @tmp + ' ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc'

print (@extra + char(10) + @tmp)
--execute (@extra + char(10) + @tmp)

Open in new window


change the last print statement to execute for real testing at your end.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Hi,

First thanks for taking the effort helping me out with this..

When I run I get printed the following
--execute [proc_FilterOrientationNotes122] 'long'

;with o2 as
(
	select OrientationID, Notes, row_number() over (partition by OrientationID order by ID desc) idx from OrientationNotes
)
SELECT     TOP (99.9999) PERCENT CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR
                      Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red, dbo.OrientationNotes.ID, dbo.Orientations.EmployeeID, 
                      dbo.Orientations.FacilityID, dbo.Orientations.Active, dbo.OrientationNotes.Note, COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered) 
                      AS Day, dbo.OrientationNotes.Initial, dbo.Orientations.ID AS OrientationID, dbo.Orientations.DateEntered, dbo.Orientations.Initial AS OrientationInitial, 
                      dbo.Orientations.Traveler, dbo.EmployeesDocsTov.TovInfoLastDay AS LastDay, dbo.Orientations.DueDate, dbo.OrientationNotes.Mailing, 
                      dbo.Orientations.DueDateInitial, dbo.EmployeesDocsTov.Last_Orient_Note_Day, dbo.Employeestbl.AvailibilityPDays, 
                      dbo.Employeestbl.AvailibilityPShifts, dbo.Employeestbl.City, dbo.Employeestbl.LastName, dbo.Employeestbl.FirstName, dbo.Employeestbl.Title, dbo.Employeestbl.AddressLine1,
                      dbo.Facilitiestbl.Name AS FacilityName, note.LastNoteID, dbo.EmployeesDocsTov.TovInfoFacility1 AS LastFacility, 
                      dbo.Employeestbl.Degree, dbo.Employeestbl.NotSendMassTextMsg, dbo.Employeestbl.NotSendMassEmail, dbo.Employeestbl.Email, 
                      dbo.Employeestbl.Beeper, dbo.Employeestbl.SMSProvider, dbo.Employeestbl.ExpectedGraduation,
					  dbo.Employeestbl.LastName + ' ' + dbo.Employeestbl.FirstName As EmpName FROM         dbo.Orientations INNER JOIN
                      dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN
                      dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
(SELECT     o2.OrientationID,  o2.ID AS LastNoteID
FROM         
 o2  Group BY OrientationID  and o2.idx = 1   ) note ON dbo.Orientations.ID = note.OrientationID
                       LEFT OUTER JOIN
                      dbo.EmployeesDocsTov ON dbo.Orientations.EmployeeID = dbo.EmployeesDocsTov.EmployeeID LEFT OUTER JOIN
                      dbo.OrientationNotes ON dbo.Orientations.ID = dbo.OrientationNotes.OrientationID where  COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered) >= '01/01/2006' and COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered) >= '12/19/2017' And OrientationNotes.Initial = 'RO' And Orientations.EmployeeID in (select ID from [EmployeesTbl] where isnull(EmployeeStatus,'') <> 'Disapproved') ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc

Open in new window

And this gives me an error "Incorrect syntax near the keyword 'and'." at following line.
 o2  Group BY OrientationID  and o2.idx = 1   ) note ON dbo.Orientations.ID = note.OrientationID

Open in new window


Thanks,
Ben
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ok... so what you retrieved from the db (table: ProgrammingSettings) contains "Group BY OrientationID " instead of a where condition.

since we're using another approach, can you empty the value from ProgrammingSettings where Code = 'UniqueFilterOrientationNotesSql'?

which means we no longer need a Group By here.
0
bfuchsAuthor Commented:
Hi Ryan,

Sorry for the delay, hope to get to the office later on today & test this..

can you empty the value from ProgrammingSettings where Code = 'UniqueFilterOrientationNotesSql'?
I also use that to send along a where clause, for example user filtered for notes within a date range, so that inner query should only contain notes filtered for.

Thanks,
Ben
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I also use that to send along a where clause, for example user filtered for notes within a date range, so that inner query should only contain notes filtered for.

ok, but in the case above, do you still want to use "Group BY OrientationID" ? I think it's no longer necessary here...
0
bfuchsAuthor Commented:
Thank you very much Ryan!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.