SQL question/problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL question/problem

Author

Topic

technah
Starting Member

2 Posts

Posted - 2013-08-30 : 12:08:03

Hi everyone.
I don't have much background in SQL and I ran into this problem and was wondering if anyone could provide some insight on how to create a query that will solve the following issue

I'm working with lets say a banking database and for every account number (ACCT#) four records are created. these records are related to each other based on the ACCT#.

the records are differentiated from each other by the Record number column (RCDNUM) RCDNUM =1,2,3 or 4
another column of interest in the records is a column named INCDEC which can have 5 different varchar values 'X','Y','N','M','P'

I am interested in seeing results return where the INCDEC column <>'N' but only when INCDEC <> 'N' for all 4 RCDNUMs associated with that ACCT#. If INCDEC <> 'N' for say 3 of the 4 records but = 'N' for one of the records I don't want that to be returned in the results.

thanks for checking this out and the help. I appreciate it and thanks in advance

>_<

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-30 : 13:40:30

From what I understood of your description, you can do something like this:


select acctNum, incdec from
(
select
	acctNum,
	incdec,
	sum(case when incdec <> 'N' then 1 else 0 end) over(partition by acctNum) as CountofNonN
from
	yourTable
) s where CountofNonN = 4;

technah
Starting Member

2 Posts

Posted - 2013-08-30 : 14:16:11

Thanks James it worked!! :):):):)

>_<

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-31 : 04:50:40

isnt it better to make it like below


select acctNum, incdec from
(
select
	acctNum,
	incdec,
	sum(case when incdec = 'N' then 1 else 0 end) over(partition by acctNum) as CountofNonN
from
	yourTable
) s where CountofNonN = 0

so that its scalable.
ie In future if you've more than 4 records associated to account and still want to exclude accounts with an occurance of incdec='N' above suggestion will still hold good.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources