How to select unique records from a stored procedure?

Hi Experts,

I have the following stored procedure that returns a recordsed.
USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[proc_FilterOrientationNotes1]    Script Date: 01/09/2018 23:33:53 ******/
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


However since this contains data of EmployeeID, FacilityID among other fields, I'm looking for a way to get unique records per EmployeeID, and only last record of FacilityID (based on ID of Orientation table) of that returned recordsed.
I'm wondering if the stored proc would have to be converted to a tabled valued function first?
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.

Mark WillsTopic AdvisorCommented:
well, a tabled value function wont let you do dynamic SQL.

Will have a look and get back to you. It is a bit involved at first glimpse...
0
Mark WillsTopic AdvisorCommented:
Well, it certainly is involved :)  

Not sure what you are asking for with "to get unique records per EmployeeID, and only last record of FacilityID (based on ID of Orientation table) "

You certainly seem to have employeeID and facilityID from orientations table (and used to join to Employeestbl and Facilitiestbl)

Can you use those to get the additional information, and how is it to incorporate into the stored procedure ?

But then, what isnt 'unique' ? If the join resolves in having repeating data because of Orientations rows, then not sure what you mean by unique...

You might need to store the result set  (from the EXEC) into a table, and then manipulate or extract the employeeID and facilityID so you can then gather the additional information.

Stored Procedures can call other stored procedures.  Maybe you dont need to link to employeestbl - leave that to another procedure ?

Think I need more of your help before I will understand the requirement.

How often is it run ? Can multiple people run it concurrently ?
0
Scott PletcherSenior DBACommented:
Not data to test with, but I think something like this will give you what you want on the Orientation* tables.

Not sure about the "EmployeeDocsTov" table, how to select only 1 row from that table, but you could use an OUTER APPLY to limit to a single row similar to what I did for the other tables.

'
FROM  dbo.Employeestbl E INNER JOIN (
    SELECT Orient.*
    FROM (
         SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
         FROM dbo.OrientationNotes o2 '
         + @strWhere1 + '  
    ) note
    OUTER APPLY (
        SELECT *
        FROM dbo.Orientations O
        WHERE note.OrientationID IS NOT NULL AND
            O.EmployeeID = E.ID AND
            O.ID = note.OrientationID
        UNION ALL
        SELECT TOP (1) *
        FROM dbo.Orientations O
        WHERE note.OrientationID IS NULL AND
            O.EmployeeID = E.ID
        ORDER BY O.ID DESC
    ) AS Orient
) AS Orient ON orient.EmployeeID = E.ID INNER JOIN
    dbo.Facilitiestbl F ON oreint.FacilityID = F.ID LEFT OUTER JOIN
    dbo.EmployeesDocsTov EDT ON Orient.EmployeeID = EDT.EmployeeID
'
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 Experts,

Think I need more of your help before I will understand the requirement.

OK, lets first give some intro to what this all is about.

We have following 5 tables involved.
Employeestbl, Orientations, OrientationNotes, Facilitiestbl and EmployeesDocsTov.

EmployeesDocsTov is just a part of the Employeestbl that was split.

Now each employee may have several Orientations
And each orientation may have several notes.
Facilities is just added since each Orientation is for a facility.

Now the purpose of this stored proc is to have users filter by all those columns and return requested data.
See attached how form based on this proc looks like..

Current request is they should have an option to get the list with only one record per employee.

in addition, the orientation info related to each employee should be the last entered orientation.
and finally the note should be the last note of that orientation.

FYI- each of those tables contains a column named ID which is PK and are all Auto num, so we can determine the order of records according to that.

I'm passing to the stored proc either a where string param, or (in case the where string exceeds the limit of MS Access in my case) it retrieves the where from a table as you can see in the code above.

I was considering create a new stored proc that returns only unique records per employee, in order to simplify the code, however had difficulties applying it.

Also I already had someone helping me with this in the past, however I had to switch direction in job, and when came back to apply it things weren't working as expected, so I decided to start from scratch..
take a look at the following
https://www.experts-exchange.com/questions/29074453/Whats-the-most-efficient-way-to-get-related-record-of-a-group-by-query.html

How often is it run ? Can multiple people run it concurrently ?
Its being used quite a lot, and users may run it concurrently .

@Scott,
I tried yours and got some errors.

Msg 8120, Level 16, State 1, Line 17
Column 'dbo.OrientationNotes.OrientationID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 4104, Level 16, State 1, Line 25
The multi-part identifier "E.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 31
The multi-part identifier "E.ID" could not be bound.
Msg 4104, Level 16, State 1, Line 35
The multi-part identifier "oreint.FacilityID" could not be bound.

Open in new window


Thanks,
Ben
Untitled.png
0
Mark WillsTopic AdvisorCommented:
Hi Ben

First up, thank you very much for the background. It does make a big difference. Especially the screen image. Puts it in context very nicely.

You said "I was considering create a new stored proc that returns only unique records per employee"

And I believe the approach by Scott - making the Employeestbl the driving table will probably help in that regard.

You said "Current request is they should have an option to get the list with only one record per employee."

Absolutely agree :)

Would it be possible to get some sample data (by individual table) for a couple of employees ?

We could then develop some queries to make sure they work the way you want, and then investigate making it parameterised for a stored procedure. Either incorporate into existing, or make a new one.

Is that possible ?
0
bfuchsAuthor Commented:
Hi Mark,

thank you very much for the background.
you w/c, let me know if any additional clarification needed.

Correction..

Current request I got is...

Would it be possible to get some sample data (by individual table) for a couple of employees ?
As you may already sensed, my SQL skills aren't the greatest..
Would it be OK if I upload them as Access tables?

Thanks,
Ben
0
Mark WillsTopic AdvisorCommented:
Yep, Access or Excel or however you are most comfortable.

Dont need a lot, it is so we can join and get data so just a couple of examples from each table - and a couple of extra rows where we need 'last' entry.

Dont even need every column.... Just enough so we can 'prove' it will work (mainly joins, and critical data)
0
bfuchsAuthor Commented:
Hi Mark,

Here you go..

I might not come into work tom.
will test it next week
Have a nice weekend!!

Thanks,
Ben
db60.zip
0
Mark WillsTopic AdvisorCommented:
Got it, thanks...

Lots of columns in employeestbl :)

Have a great weekend, and will post back soonish (but not too soon to interrupt your weekend)
0
bfuchsAuthor Commented:
Hi Mark,

I am back at work..

Let me know when you have something for me to test.

Thanks,
Ben
0
Mark WillsTopic AdvisorCommented:
What about :
;with CTE_Orientations As
( select   O.EmployeeID
		 , max(O.ID) as LastOrientationID
   From    Orientations O 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
		 , LastOrientationID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
)
   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
		   , N.ID
		   , O.EmployeeID 
           , O.FacilityID
		   , O.Active
		   , N.Note
		   , COALESCE (N.Day, O.DateEntered) AS Day
		   , N.Initial
		   , O.ID AS OrientationID
		   , O.DateEntered
		   , O.Initial AS OrientationInitial 
           , O.Traveler
		   , T.TovInfoLastDay AS LastDay
		   , O.DueDate
		   , N.Mailing
		   , O.DueDateInitial
		   , T.Last_Orient_Note_Day
		   , E.AvailibilityPDays
		   , E.AvailibilityPShifts
		   , E.City
		   , E.LastName
		   , E.FirstName
		   , E.Title
		   , E.AddressLine1
		   , F.Name AS FacilityName
		   , N.ID as LastNoteID
		   , T.TovInfoFacility1 AS LastFacility
		   , E.Degree
		   , E.NotSendTextMsg
		   , E.NotSendEmail
		   , E.Email
		   , E.Beeper
		   , E.SMSProvider 
		   , E.ExpectedGraduation
		   , E.LastName + ' ' + E.FirstName As EmpName 

FROM         CTE_Notes C
INNER JOIN   Orientations O on C.LastOrientationID = O.ID 
INNER JOIN   Employeestbl E ON C.EmployeeID = E.ID 
INNER JOIN   Facilitiestbl F ON O.FacilityID = F.ID 
LEFT OUTER JOIN  OrientationNotes N on C.LastNoteID = N.ID
LEFT OUTER JOIN  EmployeesDocsTov T ON C.EmployeeID = T.EmployeeID 

Open in new window

Could do with some indexes (if you dont already)
create index idx_orientations_id on orientations (employeeid, id)
create index idx_orientationnotes_id on orientationnotes (orientationid, id)

Open in new window

Obviously no "where" clauses so might need to tune even more.
Noticed the use of table aliases - reduces code size considerably
Also structured so that the CTE part could be added on top of the existing query - just need to add in a test (also for the from clause)
I couldnt find the NotSendMassTextMsg and email, so reverterted to NotSendTextMsg and email.

Any way, let me know your thoughts....
0
bfuchsAuthor Commented:
Hi Mark,

Its sounds great!

Tested the query itself and works perfectly, however when I try to apply it to the stored proc I get an error.
Tried as follows
USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[proc_FilterOrientationNotesUniqueEmp]    Script Date: 01/16/2018 14:42:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_FilterOrientationNotesUniqueEmp]
(@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')



if @strRetColums is null

	set @tmp=';with CTE_Orientations As
( select   O.EmployeeID
		 , max(O.ID) as LastOrientationID
   From    Orientations O 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
		 , LastOrientationID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
)
   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
		   , N.ID
		   , O.EmployeeID 
           , O.FacilityID
		   , O.Active
		   , N.Note
		   , COALESCE (N.Day, O.DateEntered) AS Day
		   , N.Initial
		   , O.ID AS OrientationID
		   , O.DateEntered
		   , O.Initial AS OrientationInitial 
           , O.Traveler
		   , T.TovInfoLastDay AS LastDay
		   , O.DueDate
		   , N.Mailing
		   , O.DueDateInitial
		   , T.Last_Orient_Note_Day
		   , E.AvailibilityPDays
		   , E.AvailibilityPShifts
		   , E.City
		   , E.LastName
		   , E.FirstName
		   , E.Title
		   , E.AddressLine1
		   , F.Name AS FacilityName
		   , N.ID as LastNoteID
		   , T.TovInfoFacility1 AS LastFacility
		   , E.Degree
		   , E.NotSendTextMsg
		   , E.NotSendEmail
		   , E.Email
		   , E.Beeper
		   , E.SMSProvider 
		   , E.ExpectedGraduation
		   , E.LastName + ' ' + E.FirstName As EmpName 

FROM         CTE_Notes C
INNER JOIN   Orientations O on C.LastOrientationID = O.ID 
INNER JOIN   Employeestbl E ON C.EmployeeID = E.ID 
INNER JOIN   Facilitiestbl F ON O.FacilityID = F.ID 
LEFT OUTER JOIN  OrientationNotes N on C.LastNoteID = N.ID
LEFT OUTER JOIN  EmployeesDocsTov T ON C.EmployeeID = T.EmployeeID '

--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 +  + @strWhere1 + 


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

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

if @strWhere = 'Long'
begin
	set @strWhere2 = (select [value] from ProgrammingSettings where [Code] = 'UniqueFilterOrientationNotesSql')
	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 O.DateEntered DESC, OrientationNotes.ID desc'

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

Open in new window

Getting following
Msg 102, Level 15, State 1, Procedure proc_FilterOrientationNotesUniqueEmp, Line 69
Incorrect syntax near ' + E.FirstName As EmpName 

FROM         CTE_Notes C
INNER JOIN   Orientations O on C.LastOrientationID = O.ID 
INNER JOIN   '.

Open in new window

Tried commenting out last line, didnt help.

Re optimization, will probably open a new question for that, as this will be used very frequent and would prefer it works in the most efficient manner.


Thanks,
Ben
0
Mark WillsTopic AdvisorCommented:
did you double up  single quotes when inside another quoted string
	   , E.LastName + '' '' + E.FirstName As EmpName 

Open in new window

0
bfuchsAuthor Commented:
Hi Mark,

Right that was the culprit.

It looks like we're almost done.-:)

When I execute the stored proc as is, so the SQL string gets printed and then I copy and run the SQL, it works.

However when I have the SP execute the SQL, I get the following

Msg 203, Level 16, State 2, Procedure proc_FilterOrientationNotesUniqueEmp, Line 104
The name ';with CTE_Orientations As
( select   O.EmployeeID
             , max(O.ID) as LastOrientationID
   From    Orientations O
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
             , LastOrientationID
         , max(N.id) as LastNoteID
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
)
   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
               , N.ID
               , O.EmployeeID
           , O.FacilityID
               , O.Active
            ' is not a valid identifier.

Thanks,
Ben
0
Mark WillsTopic AdvisorCommented:
Sorry about that. Use this as a template... it works but no param passing, no lookup, so try it first, then add in the necessary bits...
IF object_id('[dbo].[proc_FilterOrientationNotes1]','P') is not NULL 
   drop procedure [dbo].[proc_FilterOrientationNotes1]
GO

CREATE 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 = '; with CTE_Orientations As
( select   O.EmployeeID
		 , max(O.ID) as LastOrientationID
   From    Orientations O 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
		 , LastOrientationID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
)
   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
		   , N.ID
		   , O.EmployeeID 
           , O.FacilityID
		   , O.Active
		   , N.Note
		   , COALESCE (N.Day, O.DateEntered) AS Day
		   , N.Initial
		   , O.ID AS OrientationID
		   , O.DateEntered
		   , O.Initial AS OrientationInitial 
           , O.Traveler
		   , T.TovInfoLastDay AS LastDay
		   , O.DueDate
		   , N.Mailing
		   , O.DueDateInitial
		   , T.Last_Orient_Note_Day
		   , E.AvailibilityPDays
		   , E.AvailibilityPShifts
		   , E.City
		   , E.LastName
		   , E.FirstName
		   , E.Title
		   , E.AddressLine1
		   , F.Name AS FacilityName
		   , N.ID as LastNoteID
		   , T.TovInfoFacility1 AS LastFacility
		   , E.Degree
		   , E.NotSendTextMsg
		   , E.NotSendEmail
		   , E.Email
		   , E.Beeper
		   , E.SMSProvider 
		   , E.ExpectedGraduation
		   , E.LastName + '' '' + E.FirstName As EmpName 

FROM         CTE_Notes C
INNER JOIN   Orientations O on C.LastOrientationID = O.ID 
INNER JOIN   Employeestbl E ON C.EmployeeID = E.ID 
INNER JOIN   Facilitiestbl F ON O.FacilityID = F.ID 
LEFT OUTER JOIN  OrientationNotes N on C.LastNoteID = N.ID
LEFT OUTER JOIN  EmployeesDocsTov T ON C.EmployeeID = T.EmployeeID 
'
print @tmp
exec (@tmp)

GO

-- now test

exec [dbo].[proc_FilterOrientationNotes1] @strWhere = '1=1'

Open in new window

0
bfuchsAuthor Commented:
Hi,

The above does execute w/o errors.

However realized that total records returned is even grater then the total quantity of records in OrientationNotes table.

The expected results should be the same as the qty of the following.
select distinct employeeid from Orientations 

Open in new window

As the whole purpose of this is to have only one record per EmployeeID.

Thanks,
Ben
0
Mark WillsTopic AdvisorCommented:
Will check it out. I was getting the correct number from test data.....
0
bfuchsAuthor Commented:
Hi Mark,

At the beginning, record count made sense, looks like something got changed in last version..

Thanks,
Ben
0
bfuchsAuthor Commented:
Hi,

At the moment I'm getting right count, not sure what I did, tried copying old SQL into new version..
but this is what I stayed with..
; USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[proc_FilterOrientationNotesUniqueEmp2]    Script Date: 01/17/2018 15:06:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_FilterOrientationNotesUniqueEmp2] (
 @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 = ';with CTE_Orientations As
( select   O.EmployeeID
		 , max(O.ID) as LastOrientationID
   From    Orientations O 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
		 , LastOrientationID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
)
   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
		   , N.ID
		   , O.EmployeeID 
           , O.FacilityID
		   , O.Active
		   , N.Note
		   , COALESCE (N.Day, O.DateEntered) AS Day
		   , N.Initial
		   , O.ID AS OrientationID
		   , O.DateEntered
		   , O.Initial AS OrientationInitial 
           , O.Traveler
		   , T.TovInfoLastDay AS LastDay
		   , O.DueDate
		   , N.Mailing
		   , O.DueDateInitial
		   , T.Last_Orient_Note_Day
		   , E.AvailibilityPDays
		   , E.AvailibilityPShifts
		   , E.City
		   , E.LastName
		   , E.FirstName
		   , E.Title
		   , E.AddressLine1
		   , F.Name AS FacilityName
		   , N.ID as LastNoteID
		   , T.TovInfoFacility1 AS LastFacility
		   , E.Degree
		   , E.NotSendTextMsg
		   , E.NotSendEmail
		   , E.Email
		   , E.Beeper
		   , E.SMSProvider 
		   , E.ExpectedGraduation
 

FROM         CTE_Notes C
INNER JOIN   Orientations O on C.LastOrientationID = O.ID 
INNER JOIN   Employeestbl E ON C.EmployeeID = E.ID 
INNER JOIN   Facilitiestbl F ON O.FacilityID = F.ID 
LEFT OUTER JOIN  OrientationNotes N on C.LastNoteID = N.ID
LEFT OUTER JOIN  EmployeesDocsTov T ON C.EmployeeID = T.EmployeeID  
'
--print @tmp
exec (@tmp)

Open in new window


Will finish adding the necessary code & let you know.

Thanks,
Ben
0
Mark WillsTopic AdvisorCommented:
I have been investigating and can add extra surety / integrity.

In CTE_Orientations, we can get all the Last ID's
; with CTE_Orientations As
( select   O.EmployeeID
         , max(O.ID) as LastOrientationID
         , max(F.ID) as LastFacilityID
         , max(T.ID) as LastEmployeesDocsTovID
   From    Orientations O
   INNER JOIN   Facilitiestbl F ON O.FacilityID = F.ID 
   LEFT OUTER JOIN  EmployeesDocsTov T ON O.EmployeeID = T.EmployeeID 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
         , LastOrientationID
         , max(LastFacilityID) as LastFacilityID
         , max(LastEmployeesDocsTovID) as LastEmployeesDocsTovID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
)

Open in new window

and to test for dupes
; with CTE_Orientations As
( select   O.EmployeeID
         , max(O.ID) as LastOrientationID
         , max(F.ID) as LastFacilityID
         , max(T.ID) as LastEmployeesDocsTovID
   From    Orientations O
   INNER JOIN   Facilitiestbl F ON O.FacilityID = F.ID 
   LEFT OUTER JOIN  EmployeesDocsTov T ON O.EmployeeID = T.EmployeeID 
   Group by O.EmployeeID
), CTE_Notes as
(  Select  EmployeeID
         , LastOrientationID
         , max(LastFacilityID) as LastFacilityID
         , max(LastEmployeesDocsTovID) as LastEmployeesDocsTovID
         , max(N.id) as LastNoteID 
   from CTE_Orientations
   Left Outer Join OrientationNotes N on LastOrientationID = N.OrientationID
   Group by EmployeeID, LastOrientationID
)
SELECT         c.EmployeeID, LastOrientationID, LastFacilityID, LastEmployeesDocsTovID, LastNoteID,
               count(*)as qcount
             , count(c.LastOrientationID) as Ocount
             , count(c.LastFacilityID) as Fcount
             , count(c.LastEmployeesDocsTovID) as Tcount
             , count(c.LastNoteID) as Ncount
FROM         CTE_Notes C
INNER JOIN   Orientations O on C.LastOrientationID = O.ID 
INNER JOIN   Employeestbl E ON C.EmployeeID = E.ID 
INNER JOIN   Facilitiestbl F ON C.LastFacilityID = F.ID 
LEFT OUTER JOIN  OrientationNotes N on C.LastNoteID = N.ID
LEFT OUTER JOIN  EmployeesDocsTov T ON C.LastEmployeesDocsTovID = T.EmployeeID 
group by c.EmployeeID, LastOrientationID, LastFacilityID, LastEmployeesDocsTovID, LastNoteID

having   Count(*) > 1
or      count(c.LastOrientationID) > 1
or      count(c.LastFacilityID) > 1
or      count(c.LastEmployeesDocsTovID) > 1
or      count(c.LastNoteID) > 1

order by 1,2,3,4,5

Open in new window

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 Mark,

I tested this latest and found couple of issues..
However I feel its not fair to go with this that long
Perhaps would close the question by accepting this one
And will open a new with the remaining issues
And you will take it from there..
So you can awarded for your time-:)

Thanks,
Ben
0
bfuchsAuthor Commented:
Thank you very much!!
0
Mark WillsTopic AdvisorCommented:
Ben,

Appreciated, very much so, but also dont want to leave you with issues....

Since you have closed this question, please make sure you leave a forwarding address :)

Regards,
Mark Wills
0
bfuchsAuthor Commented:
Hi Mark,


See following.
(not sure if that's the norm..-:)
https://www.experts-exchange.com/questions/29078891/Final-fix-up-needed-for-stored-proc.html

Thanks,
Ben
0
Mark WillsTopic AdvisorCommented:
Normal enough, and we have another expert before I started posting :)

All good...

But what happened to employee names ?

Thought we had that back in #a42436792
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
Microsoft SQL Server

From novice to tech pro — start learning today.