Link to home
Start Free TrialLog in
Avatar of Natchiket
NatchiketFlag for United Kingdom of Great Britain and Northern Ireland

asked on

List the unique occurences of a potentially repeating field

In the following table I am attempting to summarise in the column multi_jf, the occurences of JobFamily when grouping by StaffID and PosRef, the issue is sometimes the JobFamily is the same and sometimes different so I would only like the unique occurences of JobFamily 


SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE #Results(     [Contract] [varchar](32) NOT NULL,     [SiteName] [varchar](64) NOT NULL,     [StaffID] [varchar](6) NOT NULL,     [full_name] [varchar](64) NOT NULL,     [Activity] [varchar](64) NULL,     [JobFamily] [varchar](4) NULL,     [JobTitle] [varchar](128) NULL,     [PayElements] [varchar](16) NULL,     [RosteredHours] [float] NULL,     [PosRef] [varchar](32) NULL,     [StartDate] [datetime] NULL,     [EndDate] [datetime] NULL,     [CHPW] [float] NULL,     [PRCH] [float] NULL,     [AWRH] [float] NULL,     [multi_jf] [varchar](32) NULL ) ON [PRIMARY] GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'007317', N'John', N'MNG', N'AMD', N'Assistant Manager - BIAS', N'BC', 184.25, N'DDOPBIA007317', CAST(N'2020-12-01T00:00:00.000' AS DateTime), NULL, 40, 177, NULL, N'AMD') GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'007317', N'John', N'MNG', N'AMD', N'Assistant Manager - BIAS', N'OT', 2.25, N'DDOPBIA007317', CAST(N'2020-12-01T00:00:00.000' AS DateTime), NULL, 40, 177, NULL, N'AMD') GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'007390', N'Carleb', N'OA-GYM', N'OAGM', N'Operations Assistant - BIA', N'BA', 184, N'DRABIA007390_B', CAST(N'2020-12-01T00:00:00.000' AS DateTime), NULL, 40, 177, NULL, N'OAGM') GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'007390', N'Carleb', N'OA-GYM', N'OAGM', N'Operations Assistant - BIA', N'OT', 10.5, N'DRABIA007390_B', CAST(N'2020-12-01T00:00:00.000' AS DateTime), NULL, 40, 177, NULL, N'OAGM') GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'007405', N'Lian', N'MNG', N'AMD', N'Assistant Manager - BIAS', N'BA', 176, N'DDOPBIA007405_B', CAST(N'2021-05-08T00:00:00.000' AS DateTime), NULL, 40, 177, NULL, N'AMD') GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'007405', N'Lian', N'MNG', N'AMD', N'Assistant Manager - BIAS', N'OT', 10, N'DDOPBIA007405_B', CAST(N'2021-05-08T00:00:00.000' AS DateTime), NULL, 40, 177, NULL, N'AMD') GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'011656', N'Maria', N'MNG', N'AMD', N'Assistant Manager - Bedford International Centre', N'BC', 92, N'AMDBIA011656', CAST(N'2021-05-10T00:00:00.000' AS DateTime), NULL, 20, 88, NULL, N'AMD') GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'011656', N'Maria', N'MNG', N'AMD', N'Assistant Manager - Bedford International Centre', N'OT', 68.25, N'AMDBIA011656', CAST(N'2021-05-10T00:00:00.000' AS DateTime), NULL, 20, 88, NULL, N'AMD') GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'012827', N'Arron', N'OA-GYM', N'OAGL', N'Operations Assistant - General', N'BC', 89, N'OAGLBIA012827', CAST(N'2021-05-03T00:00:00.000' AS DateTime), NULL, 40, 177, NULL, N'OAGL') GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'012827', N'Arron', N'OA-GYM', N'OAGM', N'Operations Assistant - General', N'BC', 96, N'OAGLBIA012827', CAST(N'2021-05-03T00:00:00.000' AS DateTime), NULL, 40, 177, NULL, N'OAGM') GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'300597', N'Eloise', N'OA-GYM', N'OAGM', N'Operations Assistant - Fitness Support - BIA', N'BC', 86, N'OAGMBIA300597', CAST(N'2021-05-10T00:00:00.000' AS DateTime), NULL, 20, 88, NULL, N'OAGM') GO INSERT #Results ([Contract], [SiteName], [StaffID], [full_name], [Activity], [JobFamily], [JobTitle], [PayElements], [RosteredHours], [PosRef], [StartDate], [EndDate], [CHPW], [PRCH], [AWRH], [multi_jf]) VALUES (N'Bedford', N'Bedford Athletics Stadium', N'301327', N'Maureen', N'GEP', N'FCI', N'Fitness Class Instructor - Bedford CWB', N'BA', 9, N'FCIBEDZ301327', CAST(N'2018-11-08T00:00:00.000' AS DateTime), NULL, 0, 0, NULL, N'FCI') GO

Open in new window

Here is the code that attempts to populate multi_jf

update #Results set multi_jf=multi_job_family
    from #results r     inner join     (select StaffID,PosRef,multi_job_family = Stuff((select N'/'+JobFamily from     (select StaffId,Posref,JobFamily from #Results) r1 where r2.staffid=r1.staffid and r2.posref=r1.posref order by JobFamily for xml path(N'')),1,1,'')     from #Results r2 group by StaffID,PosRef)  mr     on r.StaffID = mr.StaffID and r.PosRef=mr.PosRef

Open in new window

As you can see this works for Arron as the JobFamilys are different but not for John etc. which causes the JobFamilys to be duplicated in multi_jf

ASKER CERTIFIED SOLUTION
Avatar of Natchiket
Natchiket
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial