Solved

Help with a count query

Posted on 2013-06-16
3
202 Views
Last Modified: 2013-06-25
Hi,

  This is my requirement to form a query as per the attachement.

I need a efficient way of writing this query...using analytical functions.

Thanks in advance,
query-help.txt
0
Comment
Question by:ravibandi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 250 total points
ID: 39251984
Very nice pseudo code. Based on it I have converted to the ANSI compliant SQL statement. Here you can spot the problems straight away:
* Table PL_RLATNP_TERR_DIM is not joined with some tables
* Table CLO.USERS is not joined with some tables.

This means that the result can be duplicated. I recommend replacing in the query below COUNT(DISTINCT Call_record) to "*" and check that the result produces unique set of records without duplication. Otherwise, you will need to find some other fields by which the tables above could be joined.

Please also note, that I have used INNER JOINs for all the links. Depending on your table relationship some of the JOINS could be OUTER RIGHT or LEFT JOINs. You will really need to provide us more details if it is the case.

Select COUNT(DISTINCT Call_record) FROM
source_file sf INNER JOIN PL_RLATNP_TERR_DIM t
INNER JOIN CLO.USERS u
INNER JOIN CLO.SHR_CNTCT sc ON u.shr_cntct_id = sc.shr_cntct_id AND sc.cntct_extnsn_txt = RIGHT(sf.Extension, 4)
INNER JOIN CLO.SHR_LKUP l ON sc.cntct_mthd_lkup_id = l.shr_lkup_id
INNER JOIN CLO.TER_USER_MTRX m ON u.user_id = m.user_id and m.ter_id = t.CLO_RM_TERR_ID
WHERE sf.Call_Type in ('LOCAL', 'NA DD', 'TFN') 
and l.type = 'CNTCT_MTHD'
and l.nm = 'BUSINESS'
and t.CURR_REC_IND = 'Y'
and m.end_dt = (SELECT MAX(end_dt) FROM CLO.TER_USER_MTRX WHERE user_id = m.user_id and ter_id = m.ter_id)

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 39251993
There is some missing information I believe:

1. Inside the count() function you may include "distinct <<fieldname>>"
  -- but I don't know which field represents your definition of "a call record"

2. What is the correct table name after 'from'?

but here is a start
SELECT
     count(DISTINCT ??? something)
FROM source_FILE sf --<< ???? what is the correct table name?

INNER JOIN CLO.SHR_CNTCT sc  ON RIGHT(sf.Extension,4) = sc.cntct_extnsn_txt
INNER JOIN CLO.USERS u       ON sc.shr_cntct_id = u.shr_cntct_id
INNER JOIN CLO.SHR_LKUP l    ON sc.cntct_mthd_lkup_id = l.shr_lkup_id
    
INNER JOIN ( 
             SELECT
                  user_id
                , end_dt
                , row_number() over (partition by user_id order by end_dt DESC) as row_ref
             FROM CLO.TER_USER_MTRX
           ) m  ON u.user_id = m.user_id 
               AND row_ref = 1 --  <<  "Pick the record in CLO.TER_USER_MTRX with the latest end_dt value"
               
INNER JOIN PL_RLATNP_TERR_DIM t ON m.ter_id = t.CLO_RM_TERR_ID
    
WHERE   l.type = 'CNTCT_MTHD'
    AND l.nm = 'BUSINESS'
    AND t.CURR_REC_IND = 'Y'                                 
    AND sf.Call_Type in ('LOCAL', 'NA DD', 'TFN') 

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39252097
@chaau... sorry I didn't see your post (not sure why)
I was also concerned that CLO.USERS wasn't joined and am not certain what I came up with is correct.

I would definitely suggest the joins need review.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question