Problem In counting records in sql server

Question

2.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi ,
I need to count Pending Proposal's from Proposal table and show their record count.This is the table structure.

ProposalNO    ApplicationNo       Status  Locked

        ABC           1              A      Y
        ABC           2              A      Y
        ABC           3              A      Y
        ABC           4              R      N
        ABC           5
        DEF           6              A      Y
        DEF           7              R      N
        DEF           8              A      Y
        IJK           9              A      Y
        IJK          10

Now Proposal ABC has 5 applications out of which only 4 have entry in locked column so ABC is pending where as DEF is Completed and IJK is also pending. Now i need to get data for pending applications only. Locked column here is the status of application not the proposal. If the locked status of any application inside a proposal is null then that proposal is pending.

ProposalNo    NoOfApplications
  ABC                 5
  IJK                 2

i can always get no of applications using group by but i am not able to create a suitable query for finding weather the proposal is pending or not. Any sorf of help will be much appreciated .

Posted 29-Aug-14 3:11am

Aakash Sharma

Updated 30-Aug-14 2:23am

v2

Add a Solution

Comments

Mehdi Gholam 29-Aug-14 9:21am

Start by defining how "status" and "locked" should be combined together (AND OR NOT) for the count you want.

Aakash Sharma 30-Aug-14 8:15am

Status column is of little importance here. Proposal is pending if even 1 entry in the lock column again that proposal no in any tuple is null. Here locked column is the status of application not the proposal itself.

2 solutions

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)

Yvan Rodrigues · Answer 1 · 2014-08-29T03:32:00

Solution 1

I think you're looking for:

SQL

SELECT ProposalNo, 
    COUNT(ProposalNo) AS ProposalCount
WHERE Status = 'R'
GROUP BY ProposalNo
HAVING ProposalCount > 0

Posted 29-Aug-14 3:32am

Yvan Rodrigues

Updated 30-Aug-14 7:14am

v2

Comments

Aakash Sharma 30-Aug-14 8:22am

Actually the Locked column here is the status of the application under the proposal not the status of the proposal.I m sorry that i was not clear the first time.

Yvan Rodrigues 30-Aug-14 13:15pm

Changed WHERE to Status = 'R'.
Does that work?

DineshMaind · Answer 2 · 2014-08-30T11:11:00

Lets consider you have table

SQL

ProposalDetails(ProposalNO, ApplicationNo, Status, Locked)

having above data. Try following query it will make your picture clear.

SQL

 SELECT	ProposalNO,
	SUM(1) [Applications],
	SUM(CASE WHEN Locked = 'Y' THEN 1 ELSE 0 END) [Locked],
	SUM(CASE WHEN Locked = 'N' THEN 1 ELSE 0 END) [UnLocked],
	SUM(CASE WHEN Locked <> 'Y' AND Locked <> 'N' THEN 1 ELSE 0 END) [UnKnown]
FROM	ProposalDetails
GROUP BY ProposalNO

Now you can add following condition to your query if you think its valid.

SQL

HAVING SUM(CASE WHEN Locked = 'N' THEN 1 ELSE 0 END) > 0