April 25, 2022 at 5:38 pm
I have below the table structure and data. I'm trying to write a query that will return me SubmissionGuid only if NONE of the records associated with that submissionguid has quote status =1 . for example in the below records I have submissionguid 2A9FDC62-DF94-45DF-ABA0-DA7BBAC4B6D5 which has 3 records and 2 of them have quotestatus =2 and one has status =1 and hence it should not be returned. Only one submissionguid D4079605-D190-4CD1-AE0F-799E8E84CCC4 should be returned as both the records associated with has quote status =2 Thanks for help.
CREATE TABLE #Subs(
[SubmissionGuid] [uniqueidentifier] NOT NULL,
[QuoteStatus] [int] NOT NULL,
)
Insert into #Subs([SubmissionGuid],[QuoteStatus])
Values('2A9FDC62-DF94-45DF-ABA0-DA7BBAC4B6D5',2)
Insert into #Subs([SubmissionGuid],[QuoteStatus])
Values('2A9FDC62-DF94-45DF-ABA0-DA7BBAC4B6D5',2)
Insert into #Subs([SubmissionGuid],[QuoteStatus])
Values('2A9FDC62-DF94-45DF-ABA0-DA7BBAC4B6D5',1)
Insert into #Subs([SubmissionGuid],[QuoteStatus])
Values('D4079605-D190-4CD1-AE0F-799E8E84CCC4',2)
Insert into #Subs([SubmissionGuid],[QuoteStatus])
Values('D4079605-D190-4CD1-AE0F-799E8E84CCC4',2)
April 25, 2022 at 5:44 pm
What have you tried ?
April 25, 2022 at 5:51 pm
Maybe
SELECT SubmissionGUID, QuoteStatus
FROM #Subs
WHERE SubmissionGUID NOT IN (SELECT SubmissionGUID
FROM #Subs
WHERE QuoteStatus = 1);
April 25, 2022 at 7:20 pm
SELECT SubmissionGuid
FROM #Subs
GROUP BY SubmissionGuid
HAVING MAX(CASE WHEN QuoteStatus = 1 THEN 1 ELSE 0 END) = 0
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 26, 2022 at 9:02 pm
select *
from (
Select SubmissionGuid,QuoteStatus
,RowNum = Row_Number() over (Partition by QuoteStatus order by SubmissionGuid)
from #Subs)a
where QuoteStatus !=1 and RowNum =1
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply