Solved

Help with a count query

Posted on 2013-06-16
3
198 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
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…

920 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

13 Experts available now in Live!

Get 1:1 Help Now