Sql query for filter

Hello,
i have a table :
Table1

Columns are :

Position           Checked    InvoiceNumber
1                           1                  inv1
2                                               inv1
3                           1                  inv1


How do i write a query to :
Check all 'Checked' for the   InvoiceNumber  ='inv1' ; if all 1 then return 1 orelse return 0

Cheers
RIASAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Lee SavidgeCommented:
Deleted my comment as misread the question - sorry :)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not sure if this is what you want:
SELECT Position, 
        CASE Checked
            WHEN 1 THEN 1
            ELSE 0
        END Checked,
        InvoiceNumber
FROM Table1
WHERE InvoiceNumber  ='inv1' 

Open in new window

0
 
RIASAuthor Commented:
Vitor,

The question is my invoicenumber has 3  position , the requirement is that the query result should return 1 when all the postions have value'1' in the checked column. Basically, all postions are checked. When all positions are checked the result is 1 ,even if one position is null , the result of the query is 0


Cheers
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
Try..

CREATE TABLE checkall
(
	 Position INT         
	,Checked   INT 
	,InvoiceNumber VARCHAR(10)
)
GO


[b]Output [/b]

INSERT INTO checkall VALUES
(1                         ,  1              ,    'inv1'),
(2                        ,   NULL              ,      'inv1'),
(3                       ,    1              ,    'inv1')
GO

SELECT TOP 1 CASE WHEN cnt = cnt1 THEN 1 ELSE 0 END IsCheckedAll  FROM
(
	SELECT * , COUNT(*) OVER () cnt , COUNT(Checked) OVER () cnt1 FROM checkall
	WHERE InvoiceNumber = 'inv1'
)k

Open in new window


Output
IsCheckedAll  
-----------
0


(1 row(s) affected)

Open in new window


Hope it helps !!
0
 
RIASAuthor Commented:
Thanks Pawan,

What is  cnt = cnt1?
0
 
Pawan KumarDatabase ExpertCommented:
These are the columns I generated at runtime. :)

COUNT(*) OVER () cnt ,

COUNT(Checked) OVER () cnt1

Hope it helps !
0
 
RIASAuthor Commented:
pawan,
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

QUERY



SELECT TOP 1 CASE WHEN cnt = cnt1 THEN 1 ELSE 0 END IsCheckedAll  FROM (
      SELECT * , COUNT(*) OVER () cnt , COUNT(Checked) OVER () cnt1 FROM PRXTRACT
      WHERE InvoiceNumber ='110847'
)
0
 
Pawan KumarDatabase ExpertCommented:
Try. alias name was missing.

SELECT TOP 1 CASE WHEN cnt = cnt1 THEN 1 ELSE 0 END IsCheckedAll  
FROM (
      SELECT * , COUNT(*) OVER () cnt , COUNT(Checked) OVER () cnt1 FROM PRXTRACT
      WHERE InvoiceNumber ='110847'
)k
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
RIASAuthor Commented:
Also,
The positions for an invoice number is not fixed to 3 ,it can vary .

Cheers
0
 
Pawan KumarDatabase ExpertCommented:
It will work in all cases.
0
 
RIASAuthor Commented:
Cheers! It worked mate!
0
 
Pawan KumarDatabase ExpertCommented:
Cheers RIAS !!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.