Solved

MSSQL query help

Posted on 2014-01-01
6
343 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

735 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