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 5
bfuchsAsked:
Who is Participating?
 
Ryan ChongCommented:
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
 
Ryan ChongCommented:
how do you define "last note" ??
0
 
Ryan ChongCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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 ChongCommented:
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
 
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 ChongCommented:
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 ChongCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.