[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Help with a count query

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

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…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

649 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