February 27, 2017 at 8:49 am
I have the following tables:
Declare @Activities table(activityIncId int,activitySqlId int,activityCode nvarchar(100), activityName nvarchar(100));
Declare @ActivitiesPositions table(activityPositionIncId int, activityPositionSqlId int, activityIncId int, activitySqlId int, positionIncId int, positionSqlId int, operatorIncId int,operatorSqlId int, orderNumber int);
Declare @Positions table(positionIncId int,positionSqlId int, positionName nvarchar(100), positionCode nvarchar(100));
Declare @Operators table(operatorIncId int, operatorSqlId int, operatorName nvarchar(400));
insert into @Activities(activityIncId,activitySqlId,activityCode,activityName)
select 499784,5804,'S15-03503-07/001','Phase running' union all
select 499785,5804,'S15-03503-07/002','Phase reporting' union all
select 499786,5804,'S15-03503-07/003','Ship Items' union all
select 499787,5804,'S15-03503-07/004','Send raw data to SD'union all
select 499788,5804,'S15-03503-07/005','Raw data'
insert into @ActivitiesPositions(activityPositionIncId, activityPositionSqlId , activityIncId , activitySqlId , positionIncId , positionSqlId , operatorIncId ,operatorSqlId , orderNumber)
select 492648,5804,499784,5804, 1, 5804, 7170, 128, 0 union all
select 497113,5804,499785,5804, 1, 5804, 7170, 128, 1 union all
select 608827,5804,499786,5804, 1, 5804, 7170, 128, 0 union all
select 564315,5804,499787,5804, 1, 5804, 15175, 128, 1 union all
select 564316,5804,499787,5804, 1, 5804, 11492, 128, 2 union all
select 564317,5804,499787,5804, 1, 5804, 7170, 128, 3 union all
select 608828,5804,499788,5804, 1, 5804, 7170, 128, 0
insert into @Positions(positionIncId,positionSqlId, positionName, positionCode)
select 1,5804,'Operator','EAS006'
insert into @Operators(operatorIncId, operatorSqlId , operatorName)
select 7170,128,'Vincent Vogel' union all
select 11492,128,'Bertrand de Taffin de Tilques' union all
select 15175,128,'Carine Vergely'
Expected output is:
Activitycode Activityname Primary Operator Other Operators
-------------- -------------- -------------- --------------
S15-03503-07/001 Phase running Vincent Vogel
S15-03503-07/002 Phase reporting Vincent Vogel
S15-03503-07/003 Ship Items Vincent Vogel
S15-03503-07/004 Send raw data to SD Carine Vergely Bertrand de Taffin de Tilques, Vincent Vogel
S15-03503-07/005 Raw data Vincent Vogel
I am already getting the primary operator like this way:
SELECT AC.activityName, AC.activitycode
,ISNULL(OPO.operatorName, '') AS Operator,
ASP3.orderNumber
FROM Activities AS AC
LEFT JOIN ActivitiesPositions ASP3
LEFT JOIN Operators OPO ON OPO.operatorIncId = ASP3.operatorIncId AND OPO.operatorSqlId = ASP3.operatorSqlId AND OPO.isDeleted = 0x0
INNER JOIN Positions AS POS6
ON POS6.positionIncId = ASP3.positionIncId AND POS6.positionSqlId = ASP3.positionSqlId
AND POS6.positionCode = 'EAS006' --Operator
AND ASP3.orderNumber = (SELECT TOP 1 orderNumber from ActivitiesPositions AS ActPos2
INNER JOIN Positions AS Pos2
on ActPos2.activityIncId = ASP3.activityIncId AND ActPos2.activitySqlId = ASP3.activitySqlId
AND ActPos2.positionIncId = Pos2.positionIncId AND ActPos2.positionSqlId = Pos2.positionSqlId AND Pos2.positionCode = 'EAS006'
AND ActPos2.isDeleted=0x0 AND Pos2.isDeleted=0x0 ORDER BY orderNumber ASC)
ON AC.activityIncId = ASP3.activityIncId AND AC.activitySqlId =ASP3.activitySqlId AND ASP3.isDeleted=0x0
ORDER BY AC.activitycode
The minimal value of order number column for a activitycode is shown as Primary operator. For example, S15-03503-07/004 is having Carine Vergely as Primary operator as Order number in the ActivitiesPositions table is 1. I need to show the other operators with Order number 2 and 3 has to be shown as Other Operators.
I need help in fetching the 'Other Operators' column. Thanks.
February 27, 2017 at 2:08 pm
Glad that you posted consumable data, but your existing query has problems if I try to use your declared tables, as the isDeleted field is missing from several of them. So here's my updated (and prettied up) code, including the addition to the SELECT to provide your "OtherOperators" field. As you didn't really explain the details, I had to guess as to where to get the data, so it may not have the entirely correct JOIN in it, but it does appear to at least produce the desired results:
USE LOCAL_DB
GO
DECLARE @Activities AS TABLE (activityIncId int,activitySqlId int,activityCode nvarchar(100), activityName nvarchar(100));
DECLARE @ActivitiesPositions AS TABLE (activityPositionIncId int, activityPositionSqlId int, activityIncId int, activitySqlId int, positionIncId int, positionSqlId int, operatorIncId int, operatorSqlId int, orderNumber int, isDeleted bit);
DECLARE @Positions AS TABLE (positionIncId int,positionSqlId int, positionName nvarchar(100), positionCode nvarchar(100), isDeleted bit);
DECLARE @Operators AS TABLE (operatorIncId int, operatorSqlId int, operatorName nvarchar(400), isDeleted bit);
INSERT INTO @Activities(activityIncId,activitySqlId,activityCode,activityName)
SELECT 499784,5804,'S15-03503-07/001','Phase running' UNION ALL
SELECT 499785,5804,'S15-03503-07/002','Phase reporting' UNION ALL
SELECT 499786,5804,'S15-03503-07/003','Ship Items' UNION ALL
SELECT 499787,5804,'S15-03503-07/004','Send raw data to SD'UNION ALL
SELECT 499788,5804,'S15-03503-07/005','Raw data'
INSERT INTO @ActivitiesPositions (activityPositionIncId, activityPositionSqlId, activityIncId, activitySqlId, positionIncId, positionSqlId, operatorIncId, operatorSqlId, orderNumber, isDeleted)
SELECT 492648,5804,499784,5804, 1, 5804, 7170, 128, 0, 0 UNION ALL
SELECT 497113,5804,499785,5804, 1, 5804, 7170, 128, 1, 0 UNION ALL
SELECT 608827,5804,499786,5804, 1, 5804, 7170, 128, 0, 0 UNION ALL
SELECT 564315,5804,499787,5804, 1, 5804, 15175, 128, 1, 0 UNION ALL
SELECT 564316,5804,499787,5804, 1, 5804, 11492, 128, 2, 0 UNION ALL
SELECT 564317,5804,499787,5804, 1, 5804, 7170, 128, 3, 0 UNION ALL
SELECT 608828,5804,499788,5804, 1, 5804, 7170, 128, 0, 0
INSERT INTO @Positions (positionIncId,positionSqlId, positionName, positionCode, isDeleted)
SELECT 1,5804,'Operator','EAS006', 0
INSERT INTO @Operators (operatorIncId, operatorSqlId, operatorName, isDeleted)
SELECT 7170,128,'Vincent Vogel', 0 UNION ALL
SELECT 11492,128,'Bertrand de Taffin de Tilques', 0 UNION ALL
SELECT 15175,128,'Carine Vergely', 0
SELECT
AC.activityName,
AC.activityCode,
ISNULL(OPO.operatorName, '') AS Operator,
ASP3.orderNumber,
STUFF((SELECT ', ' + O.operatorName
FROM @Operators AS O
INNER JOIN @ActivitiesPositions AS AP
ON O.operatorIncId = AP.operatorIncId
AND AP.activityIncId = ASP3.activityIncId
AND AP.activitySqlId = ASP3.activitySqlId
AND O.operatorName <> OPO.operatorName
FOR XML PATH('')
), 1, 2, '') AS OtherOperators
FROM @Activities AS AC
LEFT JOIN @ActivitiesPositions AS ASP3
ON AC.activityIncId = ASP3.activityIncId
AND AC.activitySqlId = ASP3.activitySqlId
AND ASP3.isDeleted = 0x0
LEFT JOIN @Operators AS OPO
ON OPO.operatorIncId = ASP3.operatorIncId
AND OPO.operatorSqlId = ASP3.operatorSqlId
AND OPO.isDeleted = 0x0
INNER JOIN @Positions AS POS6
ON POS6.positionIncId = ASP3.positionIncId AND POS6.positionSqlId = ASP3.positionSqlId
AND POS6.positionCode = 'EAS006' --Operator
AND ASP3.orderNumber = (
SELECT TOP 1 orderNumber
FROM @ActivitiesPositions AS ActPos2
INNER JOIN @Positions AS Pos2
ON ActPos2.activityIncId = ASP3.activityIncId
AND ActPos2.activitySqlId = ASP3.activitySqlId
AND ActPos2.positionIncId = Pos2.positionIncId
AND ActPos2.positionSqlId = Pos2.positionSqlId
AND Pos2.positionCode = 'EAS006'
AND ActPos2.isDeleted = 0x0
AND Pos2.isDeleted = 0x0
ORDER BY orderNumber ASC
)
ORDER BY AC.activityCode;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 28, 2017 at 7:45 am
Hello, Steve Thank You for your kindness.
I made some minor changes in the code you have posted which suits my requirement, its working fine.
SELECT
AC.activityName,
AC.activityCode,
ISNULL(OPO.operatorName, '') AS Operator,
ASP3.orderNumber,
STUFF((SELECT DISTINCT ', ' + O.operatorName
FROM @Operators AS O
INNER JOIN @ActivitiesPositions AS AP
ON O.operatorIncId = AP.operatorIncId
AND O.operatorSqlId = AP.operatorSqlId AND O.isDeleted = 0 AND AP.isDeleted = 0
AND AP.activityIncId = ASP3.activityIncId
AND AP.activitySqlId = ASP3.activitySqlId
AND AP.orderNumber>ASP3.orderNumber
INNER JOIN @Positions AS POS ON POS.positionIncId = AP.positionIncId AND POS.positionSqlId = AP.positionSqlId AND POS.positionCode = 'EAS006' --Operator
FOR XML PATH('')
), 1, 2, '') AS OtherOperators
FROM @Activities AS AC
LEFT JOIN @ActivitiesPositions AS ASP3
ON AC.activityIncId = ASP3.activityIncId
AND AC.activitySqlId = ASP3.activitySqlId
AND ASP3.isDeleted = 0x0
LEFT JOIN @Operators AS OPO
ON OPO.operatorIncId = ASP3.operatorIncId
AND OPO.operatorSqlId = ASP3.operatorSqlId
AND OPO.isDeleted = 0x0
INNER JOIN @Positions AS POS6
ON POS6.positionIncId = ASP3.positionIncId AND POS6.positionSqlId = ASP3.positionSqlId
AND POS6.positionCode = 'EAS006' --Operator
AND ASP3.orderNumber = (
SELECT TOP 1 orderNumber
FROM @ActivitiesPositions AS ActPos2
INNER JOIN @Positions AS Pos2
ON ActPos2.activityIncId = ASP3.activityIncId
AND ActPos2.activitySqlId = ASP3.activitySqlId
AND ActPos2.positionIncId = Pos2.positionIncId
AND ActPos2.positionSqlId = Pos2.positionSqlId
AND Pos2.positionCode = 'EAS006'
AND ActPos2.isDeleted = 0x0
AND Pos2.isDeleted = 0x0
ORDER BY orderNumber ASC
)
ORDER BY AC.activityCode;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply