Solved

MSSQL query help

Posted on 2014-01-01
6
353 Views
Last Modified: 2014-01-05
Hello there,

I am trying to find if all the cell of the col called isComplete is true for an id.then it should retuen something to let me know they all are true or not.i tried myself and came up with this query and in my code I can do I check if the value is 0 then all the records of that col is true. I have attached the table data also to make things easy to understand.

SELECT 
sum(case when iscomplete='true' then 0 else 1 end ) as complete
 from supplierorderdetails sod
where 
sod.supplierorderid = 2

Open in new window


cheers
Zolf
1-1-2014-12-01-38-PM.gif
0
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 20 total points
ID: 39749533
try this

SELECT
 case when ( sum(case when iscomplete='true' then 1 else 0 end ) =count(*)  then 'all are ture' else 'not true' end)  from supplierorderdetails sod
where
sod.supplierorderid = 2
0
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 20 total points
ID: 39749538
use the below query

DECLARE @id INT
SELECT @id = 2
; WITH C 
AS
(
 SELECT COUNT(1) AS C From supplierorderdetails  
 WHERE supplierorderid = @id
 AND ISNULL(iscomplete,'') = 'true'
)
SELECT CASE WHEN C > 0 THEN ' Complete' Else 'Incomplete' END 
FROM C  

Open in new window

0
 
LVL 4

Accepted Solution

by:
ItWorked earned 420 total points
ID: 39749711
You were just a step behind ;-)
here is what i did.
create table #test
(
	ID int,
	Boolean bit
)

insert into #test values (1,1)
insert into #test values (1,1)
insert into #test values (2,0)
insert into #test values (1,1)
insert into #test values (1,1)


Select *
from #test

SELECT case when count(
						case when Boolean=1 then 1 
							 else null 
						end 
					  )=count(*) then 1 
			else 0 
	   end
from #test sod
where sod.ID = 1


drop table #test

Open in new window



and just do little change in your code
SELECT case when count(
						case when iscomplete='true' then 1 
							 else null 
						end 
					  )=count(*) then 1 
			else 0 
	   end
from supplierorderdetails sod
where sod.supplierorderid = 2

Open in new window


Count aggregate does not increment when value is NULL so I have used that concept and It worked.
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 20 total points
ID: 39754398
SELECT supplierorderid,'is correct' as answer

 from supplierorderdetails sod
where
sod.supplierorderid = 2
group by supplierorderid
having count(case when iscomplete='true' then 0 else 1 end) < 1
order by 1
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 20 total points
ID: 39754552
try this
SELECT supplierorderid 
  FROM supplierorderdetails 
 WHERE supplierorderid = 2 
 GROUP BY supplierorderid 
HAVING SUM(CASE 
             WHEN iscomplete = 'true' THEN 1 
             ELSE 0 
           END) = COUNT(*) 
 ORDER BY supplierorderid

Open in new window

0
 

Author Closing Comment

by:zolf
ID: 39758498
thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question