Solved

Sql query for filter

Posted on 2016-11-28
12
23 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
12 Comments
 
LVL 25

Expert Comment

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

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

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

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

COUNT(*) OVER () cnt ,

COUNT(Checked) OVER () cnt1

Hope it helps !
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 18

Accepted Solution

by:
Pawan Kumar Khowal 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 18

Expert Comment

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

Author Closing Comment

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

Expert Comment

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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now