?
Solved

MSSQL query help

Posted on 2014-01-01
6
Medium Priority
?
366 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
6 Comments
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 80 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 80 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:
Hiran Desai earned 1680 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
Technology Partners: 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!

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 80 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 80 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

621 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