error with stored procedure logic
I am trying to do something similar to How can I improve this 开发者_开发技巧SQL query?
However, in there logic Essentially I have a list of IDs that could have multiple values associated with them, the values being Yes, No, or some other string. For ID x, if any of the values are a Yes, x should be Yes, if they are all No, it should be No, if they contain any other values but yes and no, display that value. I only want to return 1 row per ID, no duplicates.
but in my logic if all values are yes, then display yes, if all values are no then display no, if it is a mix of yes,no or anything else display mix
DECLARE @tempTable table ( ID int, Val varchar(1) )
INSERT INTO @tempTable ( ID, Val ) VALUES ( 10, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 11, 'N')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 11, 'N')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 12, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 12, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 12, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 13, 'N')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 14, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 14, 'N')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 15, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 16, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 17, 'F')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 18, 'P')
SELECT DISTINCT t.ID, COALESCE(t2.Val, t3.Val, t4.Val)
FROM @tempTable t
LEFT JOIN
(
SELECT ID, Val
FROM @tempTable
WHERE Val = 'Y'
) t2 ON t.ID = t2.ID
LEFT JOIN
(
SELECT
ID, Val FROM @tempTable
WHERE Val = 'N'
) t3 ON t.ID = t3.ID
LEFT JOIN
(
SELECT ID, Val
FROM @tempTable
WHERE Val <> 'Y' AND Val <> 'N'
) t4 ON t.ID = t4.ID
Update dbo.households
SET dbo.households.code = #TempTable.code
FROM #TempTable
WHERE dbo.households.id = #TempTable.id
SELECT ID ,
CASE WHEN MAX(Val) = MIN(Val)
AND MAX(Val) IN ( 'N', 'Y' ) THEN MAX(Val)
ELSE 'M'
END
FROM @tempTable
GROUP BY ID
Or a version that returns Y
if any exist rather than if all are Y
SELECT ID ,
ISNULL(MAX(CASE WHEN Val = 'Y' THEN 'Y' END),
CASE WHEN MAX(Val) = MIN(Val) THEN MAX(Val)
ELSE 'M'
END)
FROM @tempTable
GROUP BY ID
Or a final version that takes literally the "if they contain any other values but yes and no, display that value." statement.
SELECT ID ,
ISNULL(MAX(CASE WHEN Val = 'Y' THEN 'Y'END),
ISNULL(MAX(CASE WHEN Val NOT IN ( 'N', 'Y' ) THEN Val END),
MAX(Val)))
FROM @tempTable
GROUP BY ID
You can make use of max()
since it's a char value. Give this a whirl:
Note: The logic employed here is that Non-Y/N > Any Y > Only N
[Edit] Refactored & simplified:
select
t.ID
,isnull(max(nyn.Val),max(yn.Val)) as Val
from @tempTable as t
left join @tempTable as nyn -- Non-Y/N value
on nyn.ID = t.ID
and nyn.Val not in ('Y','N')
left join @tempTable as yn -- Y/N value
on yn.ID = t.ID
and yn.Val in ('Y','N')
group by t.ID
Yields the following results:
ID Val
10 Y
11 N
12 Y
13 N
14 Y
15 Y
16 Y
17 F
18 P
Another way to do it. I use MAX() in case more than one value return other than Y and N
SELECT
ID,
CASE
WHEN EXISTS(SELECT 1 FROM @tempTable tt WHERE t.ID = tt.ID AND tt.Val = 'Y')
THEN 'Y'
WHEN EXISTS(SELECT 1 FROM @tempTable tt WHERE t.ID = tt.ID AND tt.Val NOT IN ('Y','N'))
THEN (SELECT MAX(Val) FROM @tempTable tt WHERE t.ID = tt.ID AND tt.Val NOT IN ('Y','N'))
ELSE 'N'
END
FROM @tempTable t
GROUP BY ID
精彩评论