Return list of codes in a function

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

SQL Server 2008 Forums

Transact-SQL (2008)

Return list of codes in a function

Author

Topic

richardwaugh
Starting Member

36 Posts

Posted - 2011-12-16 : 11:10:45

Hello,

I have one table that contains 3 columns of interest. The first 2 columns are what will be used to return the 3rd column. What I want to do is create a function that will have Columns 1 and 2 as input (Column 1 is CallID and Column 2 is CaseID) and return Column 3 (Code). The thing is that the CallID and/or CaseID could appear many times in the table. Thus it is possible that having one CallID could result in 5 Codes being returned.

Is this even possible in SQL? I am learning more and more as I go, but must admit that I am limited in some of my knowledge of this stuff and it's limitations.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 11:15:26

if there are multiple codes per CallID,CaseID then which one should you return? what's the rule for that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

richardwaugh
Starting Member

36 Posts

Posted - 2011-12-16 : 11:28:01

Hello Visakh,

The only rule that would apply is I need a DISTINCT so that if there are 3 of the same code, it only returns 1.

I guess the better way to put it would be to have the CallID and CaseID as input. Obtain the list of codes and then return a list of the codes without the duplicates.

Thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-16 : 12:49:17

select DISTINCT Column3
from MyTable
where Column1 = @Col1
and Column2 = @Col2

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 13:10:24

quote:
Originally posted by richardwaugh

Hello Visakh,

The only rule that would apply is I need a DISTINCT so that if there are 3 of the same code, it only returns 1.

I guess the better way to put it would be to have the CallID and CaseID as input. Obtain the list of codes and then return a list of the codes without the duplicates.

Thanks!

can there be more than 1 distinct code for CallID and CaseID combination?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 13:45:30

You missed the Point

Say Col1 has 3 identical values

Column 2 for each column one has a different value

Which value do you want to marry to col1?

1 Brett
1 Visakh
1 Bustaz

SELECT DISTINCT on col1 AND col2 would give you those 3 rows

If you ONLY want 1 row for col1, WHAT Value do you want for col1?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

richardwaugh
Starting Member

36 Posts

Posted - 2011-12-19 : 08:14:01

Hey guys. I need to clarify a little bit (I was rushed in my posting of this and realized there may be details missing).

The table structure is CallID, CaseID, Code (and other columns such as date and time that are not needed). CallID can be either -1 or an actual ID. Same with CaseID. In most circumstances if there is a CallID then CaseId will be -1. Likewise, if there is a CaseID then CallID will be -1. The Code column is not unique in that someone could have mistakenly added the same code to a call or a case.

So basically I could have values of -1, 29488, 8.1.A or 23495, -1, 4.5.H or 29304, 39485, 9.3.T...etc...

Thanks again for all the replies so far!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 11:14:15

so if a code has couple of records with caseid<> -1 and couple with callid <> -1 which values you want to return?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

richardwaugh
Starting Member

36 Posts

Posted - 2011-12-19 : 11:26:50

Hello Visakh,

I'd like all codes returned that are unique. Basically I want to return a list of codes for both CaseID <> -1 OR CallID <> -1. However, if the list has duplicates, then I don't want the duplicates. The issue is that in our database there are Calls and Cases. The latest data (since March) has both a CallID and a CaseID (A call comes in and once approved, becomes a case). The older data only has a CaseID. Hopefully the example below is little more clear. In the example, the CallID and CaseID belong to the same Case. Ideally what I would returned is the codes of 1.2.A, 1.3.R and 2.5.G. What complicates it more when I try to do what I've done so far is that if a CaseID (that doesn't have a CallID) is NOT in the table with the codes, then I get a whole bunch of garbage values returned as well (but that is a seperate issue that I am dealing with).

CallID ++ CaseID ++ Code

-1 ++ 12345 ++ 1.2.A
-1 ++ 12345 ++ 1.3.R
-1 ++ 12345 ++ 1.3.R
-1 ++ 12121 ++ 1.5.I
43532 ++ -1 ++ 2.5.G

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 11:36:01

your explanation sounds like you need this!


SELECT DISTINCT CallID,CaseID,Code
FROM Table
WHERE CallID <> -1 OR CaseID <> -1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources