Solved

Help with a count query

Posted on 2013-06-16
3
196 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
  • 2
3 Comments
 
LVL 24

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now