November 1, 2016 at 11:32 am
Hi All,
The below query gives two records, but I am trying to figure out a way to display only one record if there is another record other than Agnt = 'who'
from the below query, I would like to get only the record with Agnt = 'ThisRec'.
create table #tst
(Id INT,
Unit INT,
Selct VARCHAR(25),
Rtm VARCHAR(10),
Agnt VARCHAR(10)
)
insert into #tst
select 1001, 1, NULL, NULL, 'Who'
union all
select 1001, 1, 'Selected', NULL, 'ThisRec'
union all
select 1001, 1, 'Not Selected', '0', 'XYZ'
union all
select 1001, 1, NULL, NULL, 'ABC'
select *,
Case
when (Selct is null or Selct = '' or Selct = 'Not Selected')
and (Rtm = '0' or Rtm is null) and Agnt = 'Who' and Unit = 1 then 'x'
when Selct = 'Selected' and Unit = 1 then 'x'
end
from #tst
where Case
when (Selct is null or Selct = '' or Selct = 'Not Selected')
and (Rtm = '0' or Rtm is null) and Agnt = 'Who' and Unit = 1 then 'x'
when Selct = 'Selected' and Unit = 1 then 'x'
end = 'x'
drop table #tst
any help is appreciated.
thank you!
November 1, 2016 at 11:52 am
What about this, since you don't have any other criteria the results will be fairly arbitrary if you have multiple rows that are returned either with ThisRec or no rows with ThisRec and multiple other rows.
WITH TEMP_CTE AS(
select *,
Case
when (Selct is null or Selct = '' or Selct = 'Not Selected')
and (Rtm = '0' or Rtm is null) and Agnt = 'Who' and Unit = 1 then 'x'
when Selct = 'Selected' and Unit = 1 then 'x'
end AS COL,
ROW_NUMBER() OVER(PARTITION BY (SELECT(1)) ORDER BY CASE WHEN Agnt = 'ThisRec' THEN 1 ELSE 0 END DESC) AS ROW_NUM
from #tst
where Case
when (Selct is null or Selct = '' or Selct = 'Not Selected')
and (Rtm = '0' or Rtm is null) and Agnt = 'Who' and Unit = 1 then 'x'
when Selct = 'Selected' and Unit = 1 then 'x'
end = 'x'
)
SELECT * FROM TEMP_CTE WHERE ROW_NUM = 1
November 1, 2016 at 11:56 am
if this query is only ever meant to return 1 row, then maybe instead of calculating a generic flag 'x' in the CASE statement, calculate a preference:
SELECT TOP 1 *,
CASE WHEN (Selct IS NULL OR Selct = '' OR Selct = 'Not Selected')
AND (Rtm = '0' OR Rtm IS NULL) AND Agnt = 'Who' AND Unit = 1 THEN 2
WHEN Selct = 'Selected' AND Unit = 1 THEN 1
END AS preference
FROM #tst
ORDER BY preference
November 1, 2016 at 1:24 pm
thank you ZZartin!
The Value "ThisRec" can be anything and there is a possibility of having multiple records based on the other criteria.
thanks!
November 1, 2016 at 1:25 pm
Chris Harshman (11/1/2016)
if this query is only ever meant to return 1 row, then maybe instead of calculating a generic flag 'x' in the CASE statement, calculate a preference:
SELECT TOP 1 *,
CASE WHEN (Selct IS NULL OR Selct = '' OR Selct = 'Not Selected')
AND (Rtm = '0' OR Rtm IS NULL) AND Agnt = 'Who' AND Unit = 1 THEN 2
WHEN Selct = 'Selected' AND Unit = 1 THEN 1
END AS preference
FROM #tst
ORDER BY preference
thank you Chris!
The query can return multiple records, not just one.
thanks!
November 1, 2016 at 2:15 pm
Hard to determine exactly what you want, but my best guess is something like below; adjust the case conditions to be specifically what you need them to be:
select *
from (
select *, ROW_NUMBER() over(partition by id order by
case when Selct = 'Selected' and Unit = 1
then 10
when Selct = '' and (Rtm = '0' or Rtm is null) and Unit = 1
then 20
when Selct = 'Not Selected' and (Rtm = '0' or Rtm is null) and Unit = 1
then 30
when Selct is null and Rtm is not null
then 40
when Selct is null and Rtm is null and Agnt <> 'Who'
then 50
else 99
end
) as row_num
from #tst
) as derived
where row_num = 1
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 2, 2016 at 8:33 am
How about this one, which specifies the exact conditions of the original post:
CREATE TABLE #tst (
Id int,
Unit int,
Selct varchar(25),
Rtm varchar(10),
Agnt varchar(10)
);
INSERT INTO #tst (Id, Unit, Selct, Rtm, Agnt)
SELECT 1001, 1, NULL, NULL, 'Who'
UNION ALL
SELECT 1001, 1, 'Selected', NULL, 'ThisRec'
UNION ALL
SELECT 1001, 1, 'Not Selected', '0', 'XYZ'
UNION ALL
SELECT 1001, 1, NULL, NULL, 'ABC';
WITH ALL_DATA AS (
SELECT *,
CASE
WHEN (Selct IS NULL OR Selct = '' OR Selct = 'Not Selected')
AND (Rtm = '0' OR Rtm IS NULL) AND Agnt = 'Who' AND Unit = 1 THEN 'x'
WHEN Selct = 'Selected' AND Unit = 1 THEN 'x'
END AS IDENTIFIER_FIELD
FROM #tst
WHERE CASE
WHEN (Selct IS NULL OR Selct = '' OR Selct = 'Not Selected')
AND (Rtm = '0' OR Rtm IS NULL) AND Agnt = 'Who' AND Unit = 1 THEN 'x'
WHEN Selct = 'Selected' AND Unit = 1 THEN 'x'
END = 'x'
)
SELECT *
FROM ALL_DATA AS D1
EXCEPT
SELECT *
FROM ALL_DATA AS D2
WHERE Agnt = 'Who'
AND EXISTS (SELECT 1 FROM ALL_DATA AS D3 WHERE D3.Agnt <> 'Who' AND D3.IDENTIFIER_FIELD = 'x');
DROP TABLE #tst;
The original post only wants to return the other "selected" record when the "Who" Agnt is also selected. If there are no other records in the "selected" status, then the "Who" Agnt should be returned... Assuming I correctly interpreted what was written in that post. What say you, ssc_san ?
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
November 2, 2016 at 9:57 am
ScottPletcher (11/1/2016)
Hard to determine exactly what you want, but my best guess is something like below; adjust the case conditions to be specifically what you need them to be:
select *
from (
select *, ROW_NUMBER() over(partition by id order by
case when Selct = 'Selected' and Unit = 1
then 10
when Selct = '' and (Rtm = '0' or Rtm is null) and Unit = 1
then 20
when Selct = 'Not Selected' and (Rtm = '0' or Rtm is null) and Unit = 1
then 30
when Selct is null and Rtm is not null
then 40
when Selct is null and Rtm is null and Agnt <> 'Who'
then 50
else 99
end
) as row_num
from #tst
) as derived
where row_num = 1
thank you Scott! this worked.
November 2, 2016 at 9:58 am
sgmunson (11/2/2016)
How about this one, which specifies the exact conditions of the original post:
CREATE TABLE #tst (
Id int,
Unit int,
Selct varchar(25),
Rtm varchar(10),
Agnt varchar(10)
);
INSERT INTO #tst (Id, Unit, Selct, Rtm, Agnt)
SELECT 1001, 1, NULL, NULL, 'Who'
UNION ALL
SELECT 1001, 1, 'Selected', NULL, 'ThisRec'
UNION ALL
SELECT 1001, 1, 'Not Selected', '0', 'XYZ'
UNION ALL
SELECT 1001, 1, NULL, NULL, 'ABC';
WITH ALL_DATA AS (
SELECT *,
CASE
WHEN (Selct IS NULL OR Selct = '' OR Selct = 'Not Selected')
AND (Rtm = '0' OR Rtm IS NULL) AND Agnt = 'Who' AND Unit = 1 THEN 'x'
WHEN Selct = 'Selected' AND Unit = 1 THEN 'x'
END AS IDENTIFIER_FIELD
FROM #tst
WHERE CASE
WHEN (Selct IS NULL OR Selct = '' OR Selct = 'Not Selected')
AND (Rtm = '0' OR Rtm IS NULL) AND Agnt = 'Who' AND Unit = 1 THEN 'x'
WHEN Selct = 'Selected' AND Unit = 1 THEN 'x'
END = 'x'
)
SELECT *
FROM ALL_DATA AS D1
EXCEPT
SELECT *
FROM ALL_DATA AS D2
WHERE Agnt = 'Who'
AND EXISTS (SELECT 1 FROM ALL_DATA AS D3 WHERE D3.Agnt <> 'Who' AND D3.IDENTIFIER_FIELD = 'x');
DROP TABLE #tst;
The original post only wants to return the other "selected" record when the "Who" Agnt is also selected. If there are no other records in the "selected" status, then the "Who" Agnt should be returned... Assuming I correctly interpreted what was written in that post. What say you, ssc_san ?
thank you sgmunson! it covers all the possible cases.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply