?
Solved

Help with a count query

Posted on 2013-06-16
3
Medium Priority
?
204 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 750 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 750 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 49

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

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 ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…
Suggested Courses

771 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