• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 50
  • Last Modified:

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
0
RIAS
Asked:
RIAS
1 Solution
 
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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
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
 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now