Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSSQL query help

Posted on 2014-01-01
6
Medium Priority
?
363 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

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!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

885 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