Solved

Sql query for filter

Posted on 2016-11-28
12
40 Views
Last Modified: 2016-11-28
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
Comment
Question by:RIAS
[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
12 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 41903964
Deleted my comment as misread the question - sorry :)
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41903965
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
 

Author Comment

by:RIAS
ID: 41903973
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41903974
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
 

Author Comment

by:RIAS
ID: 41903977
Thanks Pawan,

What is  cnt = cnt1?
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41903980
These are the columns I generated at runtime. :)

COUNT(*) OVER () cnt ,

COUNT(Checked) OVER () cnt1

Hope it helps !
0
 

Author Comment

by:RIAS
ID: 41903981
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
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41903982
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
 

Author Comment

by:RIAS
ID: 41903995
Also,
The positions for an invoice number is not fixed to 3 ,it can vary .

Cheers
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41904002
It will work in all cases.
0
 

Author Closing Comment

by:RIAS
ID: 41904020
Cheers! It worked mate!
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41904067
Cheers RIAS !!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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