troubleshooting Question

List the unique occurences of a potentially repeating field

Avatar of Natchiket
NatchiketFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server
1 Comment1 Solution6 ViewsLast Modified:

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
Natchiket

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 1 Comment.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 1 Comment.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004