Solved

MSSQL query help

Posted on 2014-01-01
6
339 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 40

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 3 92
Mysql Left Join Case 10 48
insert wont work in SQL 14 19
SQL Server: Unable to remove duplicate sets in Header/Detail 6 22
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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…

785 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