Solved

Sql query for filter

Posted on 2016-11-28
12
39 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 28

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 28

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 28

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 28

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 28

Expert Comment

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

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 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