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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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