One to many record select question

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

One to many record select question

Author

Topic

neilwarner
Starting Member

3 Posts

Posted - 2010-09-17 : 09:42:55

I am writing a Crystal report on a call tracking database. When a caller calls in there is call tracking table that links caller type information, date/time to the Call_ID information (In the Polidy_info table), and that is a one to one relationship. I have successfully built that part of the query.

Three tables are related {related fields in brackets} - Call_Tracking - (Call_ID) - Policy_Info - (LOB_CDE & ADMIN_SYS_CDE) - Product_Table

My problem is when I have to link the following tables (Policy_Info and Product_Table) – shown at the bottom. I need a query that will return the PROD_DSCRP for each Call_ID. I have already successfully built queries when the LOB_CDE from the Policy Info doesn't appear in the Product Table, and when LOB_CDE = null in the first table. When the LOB_CDE matches between both tables, sometimes the ADMIN_SYS_CDE isn't in the second table, our DBA's have added the ADMIN_SYS_CDE of 'ALL' to the Product Table for some of the LOB, but not all.

What I need help with: Sometimes the ADMIN_SYS_CDE matches in the second table, and there is a value of 'ALL' in ADMIN_SYS_CDE.

For example CALL_ID 10004 has an LOB_CDE of “A” and ADMIN_SYS_CDE of 958.

The SQL statement I created was something like this…

Select POLINFO.Call_ID as Callid,
POLINFO.LOB_CDE as POLOB,
POLINFO.ADMIN_SYS_CDE as POSYSCDE,
PRODTBL.LOB_CDE as PRLOB,
PRODTBL.ADMIN_SYS_CDE as PRSYSCDE,
PRODTBL. PROD_DSCRP as PRDCTCDE,
From Policy_Info as POLINFO,
Product_Table as PRODTBL
Where POLINFO.LOB_CDE = PRODTBL.LOB_CDE as PRLOB
And (POLINFO.ADMIN_SYS_CDE = PRODTBL.ADMIN_SYS_CDE
Or PRODTBL ADMIN_SYS_CDE = ‘All’)
And POLINFO.Call_ID = 10004 - added to just test this Call_ID

The return of this SQL is two rows:
Callid POLOB POSYSCDE PRLOB PRSYSCDE PROD_DSCRP
10004 A 958 A 958 Product Name
10004 A 958 A ALL Product Name

I only want one row returned for each called. I have also tried to use union in two select statements where ADMIN_SYS_CDE = PRODTBL.ADMIN_SYS_CDE and PRODTBL ADMIN_SYS_CDE = ‘All’, but this shows both instances as well.

The Queries I feel I need to build for this reaport and union are:

1. POLINFO.LOB_CDE = ‘Null’ - Done
2. POLINFO.LOB_CDE <> PRODTBL.LOB_CDE - Done
3. POLINFO.LOB_CDE = PRODTBL.LOB_CDE
POLINFO.ADMIN_SYS_CDE <> PRODTBL.ADMIN_SYS_CDE (and NOT PRODTBL.ADMIN_SYS_CDE <> ‘ALL’
4. POLINFO.LOB_CDE = PRODTBL.LOB_CDE
POLINFO.ADMIN_SYS_CDE = PRODTBL.ADMIN_SYS_CDE (and PRODTBL.ADMIN_SYS_CDE = ‘ALL’)
5. POLINFO.LOB_CDE = PRODTBL.LOB_CDE
POLINFO.ADMIN_SYS_CDE = PRODTBL.ADMIN_SYS_CDE (and PRODTBL.ADMIN_SYS_CDE <> ‘ALL’)

Some how I need for 3, 4, & 5 to not contain duplicate within each query and not duplcates between the three queries.

The end result needs to look like this:
Date/Time Call_ID Caller_Type Call_Reason Compl_Rsn Prod
1/1/2010 10004 Owner Statement None Product Name

Tables with example information:

Call_Tracking
Date/Time Call_ID Caller_Type Call_Reason Complaint_Reason
1/1/2010 10001 Owner Balance None
1/1/2010 10002 Agent Balance Balance
1/1/2010 10004 Owner Statement None

Policy_Info
Call_ID LOB_CDE ADMIN_SYS_CDE
10001 A 201
10002 A 232
10003 A 456
10004 A 958
10005 A 789

Product_Table
LOB_CDE ADMIN_SYS_CDE PROD_DSCRP
A 201 Product Name
A 258 Product Name
A 458 Product Name
A 958 Product Name
A ALL Product Name

Neil Warner
Programmer Call Center Tech

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-17 : 10:02:39

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-17 : 11:22:20

try this :

SELECT POLINFO.Call_ID AS Callid,
POLINFO.LOB_CDE AS POLOB,
POLINFO.ADMIN_SYS_CDE AS POSYSCDE,
PRODTBL.LOB_CDE AS PRLOB,
PRODTBL.ADMIN_SYS_CDE AS PRSYSCDE,
PRODTBL. PROD_DSCRP AS PRDCTCDE
FROM Policy_Info AS POLINFO
INNER JOIN Product_Table AS PRODTBL
ON POLINFO.LOB_CDE = PRODTBL.LOB_CDE AND POLINFO.ADMIN_SYS_CDE = PRODTBL.ADMIN_SYS_CDE
WHERE
POLINFO.Call_ID = 10004

this gives me the following o/p:

Callid POLOB POSYSCDE PRLOB PRSYSCDE PRDCTCDE
10004 A 958 A 958 Product Name

Remove the WHERE CLAUSE to get results for all Caller_ID.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-17 : 11:35:57

quote:
Originally posted by neilwarner

The end result needs to look like this:
Date/Time Call_ID Caller_Type Call_Reason Compl_Rsn Prod
1/1/2010 10004 Owner Statement None Product Name

Neil Warner
Programmer Call Center Tech

Use the following query to get the end result


SELECT          
               CALLTRK.datetimes as CallTime,
                CALLTRK.Call_ID AS Callid,
                CALLTRK.Caller_Type,
                CALLTRK.Call_Reason,
                CALLTRK.Complaint_Reason,
				
				PRODTBL. PROD_DSCRP AS PRDCTCDE
	    FROM   Call_Tracking  AS CALLTRK
	    INNER JOIN  Policy_Info AS POLINFO 
				ON CALLTRK.Call_ID = POLINFO.Call_ID
	    INNER JOIN Product_Table AS PRODTBL
	            ON POLINFO.LOB_CDE = PRODTBL.LOB_CDE AND POLINFO.ADMIN_SYS_CDE = PRODTBL.ADMIN_SYS_CDE

and I am getting following output for your sample data:

CallTime Callid Caller_Type Call_Reason Complaint_Reason PRDCTCDE
2010-01-01 00:00:00.000 10001 Owner Balance None Product Name
2010-01-01 00:00:00.000 10004 Owner Statement None Product Name

neilwarner
Starting Member

3 Posts

Posted - 2010-09-24 : 11:23:04

Thanks for all of the help, I ended up setting up a union between the two SQL statements (shown below) where one statement is where the ADM_SYS_CDE is equal between tables, and the other statement shows ADM_SYS_CDE equal to 'all' in the PRODTBL table.

I needed results if either statement were true, but no duplicates. I was trying an and/or statement in a single SQL statement and that was causing duplicate results, but the union isn't causing this issue.

Below is the actual statement with 'real' table names. The extra fields provide descriptions for each call (Caller type, call reason, complaint, complaint type, etc).

NOW I need something new now... I need a statement that will provide when LOB equal between tables, and ADM_SYS_CO_CDE either not equal between POLINFO and PRODTBL and PRODTBL.ADMIN_SYS_CO_CDE not equal to 'ALL'. This would be the last of 4 queries I feel I need to build for this report.

select track.call_id_nbr as callid,
PRODUCTCD.PRDCT_GRP_DSCRP as PRDCTCD,
POLINFO.cn_cntrct_nbr as cntrnumb,
to_char(TRACK.ADD_DATE, 'MM/DD/YYYY hh24:mi:ss') as TrackingDateTime,
CALLERCAT.CALLER_CATEGORY as CallerCategory,
FAMILY.CALL_FAMILY_DSCRP as Family,
FAMILYTYPE.CALL_FAMILY_TYP as FamilyType,
COMPLAINT.COMPLAINT_DSCRP as Complaint,
COMPLTYPE.COMPLAINT_TYP as ComplaintType,
POLINFO.PRDCT_LOB_CDE as POlobcde,
POLINFO.ADMIN_SYS_CO_CDE as POsyscde
from dpssadm.iivrk_call_tracking TRACK,
dpssadm.iivrk_call_polinfo POLINFO,
dpssadm.iivrk_call_family FAMILY,
dpssadm.iivrk_call_Family_TYPE FAMILYTYPE,
dpssadm.iivrk_caller_category CALLERCAT,
dpssadm.iivrk_Complaint COMPLAINT,
dpssadm.iivrk_complaint_type COMPLTYPE,
dpssadm.iicvk_PRDCT PRODUCTCD
where TRACK.CALL_ID_NBR = POLINFO.CALL_ID_NBR
and TRACK.complaint_id = COMPLAINT.complaint_id
and TRACK.CALL_FAMILY_ID = FAMILY.CALL_FAMILY_ID
and TRACK.CALL_FAMILY_TYP_ID = FAMILYTYPE.CALL_FAMILY_TYP_ID
and TRACK.CALLER_CATEGORY_ID = CALLERCAT.CALLER_CATEGORY_ID
and TRACK.complaint_type_id = COMPLTYPE.complaint_type_id
and POLINFO.PRDCT_LOB_CDE = PRODUCTcd.PRDCT_LOB_CDE
and POLINFO.ADMIN_SYS_CO_CDE = PRODUCTcd.ADMIN_SYS_CO_CDE

union

select track.call_id_nbr as callid,
PRODUCTCD.PRDCT_GRP_DSCRP as PRDCTCD,
POLINFO.cn_cntrct_nbr as cntrnumb,
to_char(TRACK.ADD_DATE, 'MM/DD/YYYY hh24:mi:ss') as TrackingDateTime,
CALLERCAT.CALLER_CATEGORY as CallerCategory,
FAMILY.CALL_FAMILY_DSCRP as Family,
FAMILYTYPE.CALL_FAMILY_TYP as FamilyType,
COMPLAINT.COMPLAINT_DSCRP as Complaint,
COMPLTYPE.COMPLAINT_TYP as ComplaintType,
POLINFO.PRDCT_LOB_CDE as POlobcde,
POLINFO.ADMIN_SYS_CO_CDE as POsyscde
from dpssadm.iivrk_call_tracking TRACK,
dpssadm.iivrk_call_polinfo POLINFO,
dpssadm.iivrk_call_family FAMILY,
dpssadm.iivrk_call_Family_TYPE FAMILYTYPE,
dpssadm.iivrk_caller_category CALLERCAT,
dpssadm.iivrk_Complaint COMPLAINT,
dpssadm.iivrk_complaint_type COMPLTYPE,
dpssadm.iicvk_PRDCT PRODUCTCD
where TRACK.CALL_ID_NBR = POLINFO.CALL_ID_NBR
and TRACK.complaint_id = COMPLAINT.complaint_id
and TRACK.CALL_FAMILY_ID = FAMILY.CALL_FAMILY_ID
and TRACK.CALL_FAMILY_TYP_ID = FAMILYTYPE.CALL_FAMILY_TYP_ID
and TRACK.CALLER_CATEGORY_ID = CALLERCAT.CALLER_CATEGORY_ID
and TRACK.complaint_type_id = COMPLTYPE.complaint_type_id
and POLINFO.PRDCT_LOB_CDE = PRODUCTcd.PRDCT_LOB_CDE
and PRODUCTcd.ADMIN_SYS_CO_CDE = 'ALL'

Neil Warner
Programmer Call Center Tech

neilwarner
Starting Member

3 Posts

Posted - 2010-10-20 : 14:22:52

The Product table should have contained either the exact sys_cde or a valuse 'All' contained both an exact match and 'All'. The DBA's removed the exact match since this was an error and wasn't neede since the 'ALL' was in the table.

My query with the following syntax now works perfectly:

and (POLINFO.ADMIN_SYS_CO_CDE = PRODUCTcd.ADMIN_SYS_CO_CDE or
PRODUCTcd.ADMIN_SYS_CO_CDE = 'ALL')

Thanks for all of the information provided.

Neil Warner
Programmer Call Center Tech

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources