January 24, 2006 at 8:21 am
The following table is a result set I need to filter:
POS_NO PC MC MS MV
0001141220
000114110010
001596112120
022783112120
023052110010
023903110100
Notice there are 2 records where POS_NO=000114. This is because there are two people that filled that position over the year. It was filled by person A for 2 months (MS - MONTHS_Staffed) and Vacant for 10 (MV - MONTHS_Vacant). The reason I say two people becuase we use a vacant ID when the position is vacant. How would I further filter this so that I get 1 record for POS_NO=000114 and the split of MS and MV is also in 1 record.
Thanks
January 24, 2006 at 8:38 am
More rules are needed however try this.
SET NOCOUNT ON
DECLARE @Position TABLE
(
POS_NO VARCHAR(10),
PC INT,
MC INT,
MS INT,
MV INT
)
INSERT @Position
SELECT '000114', 1, 2, 2, 0 UNION
SELECT '000114', 1, 10, 0, 10 UNION
SELECT '001596', 1, 12, 12, 0 UNION
SELECT '022783', 1, 12, 12, 0 UNION
SELECT '023052', 1, 10, 0, 10 UNION
SELECT '023903', 1, 10, 10, 0
/* If you want to filter vacant ID only */
SELECT * FROM @Position WHERE MC <> 10 ORDER BY 1
/* If you want to filter only when there is more than one record for a position */
SELECT P.*, D.POS_NO
FROM
@Position P
LEFT OUTER JOIN
(
SELECT POS_NO
FROM
@Position
GROUP BY POS_NO
HAVING COUNT(MC) > 1) D
ON
P.POS_NO = D.POS_NO
WHERE
D.POS_NO IS NULL
OR P.MC <> 10
ORDER BY 1
Regards,
gova
January 24, 2006 at 8:43 am
Thanks for the reply.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply