How to get greater than zero value sqlserver

Question

0.00/5 (No votes)

See more:

SQL

SQL-Server

SQL-Server-2008R2

, +

I have made a query in sqlserver. the code is below.

SQL

select ReceiptNo, S_Adm_No, paid_date, S_Adm_No, Class, S_Name, S_F_Name, Mode_Payment,ChqNo,ChqDated, bank_Name, For_the_Month , 
FHeadId, FHead,Fine, TotalAmount, Concession, payableAmount, SUM (Apr + May+Jun+Jul+Aug+Sep+Oct+Nov+[Dec]+Jan+Feb+Mar) as FEE 
from View_Fee_Receipt_Students where ReceiptNo='AIS/16-17/00096' group by ReceiptNo, 
S_Adm_No, paid_date, S_Adm_No, Class, S_Name, S_F_Name, Mode_Payment,ChqNo,ChqDated, bank_Name, For_the_Month ,
Fine, TotalAmount, FHeadId, FHead, Concession, payableAmount

My question is, how to get value where fee has greater than zero...

What I have tried:

.........................................................

Posted 26-Jan-17 5:25am

Member 12245539

Updated 29-Jan-17 18:49pm

Add a Solution

Comments

Peter Leow 26-Jan-17 11:38am

Your query looks weird, can you show the table structure and explain how a ReceiptNo relates to payments for different months.

2 solutions

Solution 2

you can try with below query

Select * from (
select ReceiptNo, S_Adm_No, paid_date, S_Adm_No, Class, S_Name, S_F_Name, Mode_Payment,ChqNo,ChqDated, bank_Name, For_the_Month ,
FHeadId, FHead,Fine, TotalAmount, Concession, payableAmount, SUM (isnull(Apr,0) + isnull(May,0) +isnull(Jun,0)+isnull(Jul,0)+isnull(Aug,0)+isnull(Sep,0)+isnull(Oct,0)+isnull(Nov,0)+isnull([Dec],0)+isnull(Jan,0)+isnull(Feb,0)+isnull(Mar,0)) as FEE
from View_Fee_Receipt_Students where ReceiptNo='AIS/16-17/00096' group by ReceiptNo,
S_Adm_No, paid_date, S_Adm_No, Class, S_Name, S_F_Name, Mode_Payment,ChqNo,ChqDated, bank_Name, For_the_Month ,
Fine, TotalAmount, FHeadId, FHead, Concession, payableAmount) as result

WHERE FEE > 0

Posted 29-Jan-17 18:49pm

Vaibhav-Systematix

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-01-26T05:40:00

Do you mean

SQL

... WHERE ReceiptNo='AIS/16-17/00096' GROUP BY ... HAVING SUM (Apr + May+Jun+Jul+Aug+Sep+Oct+Nov+[Dec]+Jan+Feb+Mar) > 0

But...You do realise that each clause in a GROUP BY increases teh number of separate groups generated, don't you? When you use more than one grouping field, SQL creates a group for each of the rows with distinct values in any of the named fields. And with the number of fields in your GROUP BY, I suspect you will get a separate group for each row in the input table...