CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user3224907
user3224907

Reputation: 768

Oracle issue with reporting SQL

I have a reporting SQL in Oracle which is using the SUM function.

Table Y

CLIENT_NUM  LOCATION_CODE EFFECTIVE_DATE EXPIRY_DATE
1234        AB01          1/1/2011       6/30/2014
1234        AB01          7/1/2014       8/31/2014
1234        AB01          9/1/2014       6/30/2015
1234        AB66          1/1/2011       6/30/2014
1234        AB66          7/1/2014       8/31/2014
1234        AB66          9/1/2014       6/30/2015

Table X

CLIENT_NUM LOCATION_CODE EMPLOYEE_COUNT TOTAL_HOURS_WORKED REPORTING_YEAR
1234       AB01          600            12000              2014
1234       AB66          50             11000              2014

SQL:

SELECT SUM(x.EMPLOYEE_COUNT) 
      OVER (PARTITION BY CASE ':input_parm:'
                         WHEN 'LOCATION' THEN y.LOCATION_CODE || ' ' || y.LOCATION_NAME
                         WHEN 'CLIENT' THEN y.CLIENT_NUM|| ' ' ||z.CLIENT_NAME 
                         END
               ) AS EMPLOYEE_COUNT 
     , SUM(x.TOTAL_HOURS_WORKED) 
          OVER (PARTITION BY CASE ':input_parm:'
                             WHEN 'LOCATION' THEN y.LOCATION_CODE || ' ' || y.LOCATION_NAME
                             WHEN 'CLIENT' THEN y.CLIENT_NUM|| ' ' ||z.CLIENT_NAME 
                             END
           ) AS TOTAL_HOURS_WORKED 
FROM Y
LEFT OUTER JOIN X
    ON Y.CLIENT_NUM = X.CLIENT_NUM
   AND Y.LOCATION_CODE = X.LOCATION_CODE
   AND X.REPORTING_YEAR = '2014'
   AND location.EFFECTIVE_DATE BETWEEN TO_DATE('01-JAN-:in_reporting_year:','DD-MON-YYYY')
                                   AND TO_DATE('31-DEC-:in_reporting_year:','DD-MON-YYYY')
WHERE Y.CLIENT_NUM='1234' 
AND (Y.LOCATION_CODE='AB66' OR Y.LOCATION_CODE='AB01')

This is doubling the numbers up and returning the following when passing "CLIENT" or "LOCATION" for :input_parm: and 2014 for :in_reporting_year:

EMPLOYEE_COUNT TOTAL_HOURS_WORKED
1300           46000
1300           46000

This is the expected output:

EMPLOYEE_COUNT TOTAL_HOURS_WORKED
650            23000
650            23000

How can I further filter the join to match only one?

Upvotes: 1

Views: 109

Answers (1)

shawnt00
shawnt00

Reputation: 17915

You have two rows in Y for the same location_code and client_num pairs that you're joining on. So the join is duplicating your results and thus the doubling in the totals.

I'm guessing you'll need to match on one of the dates values somehow.

EDIT: To address your question in comments you could try changing FROM Y to the following:

FROM (SELECT DISTINCT CLIENT_NUM, LOCATION_CODE FROM Y) as Y

Upvotes: 2

Related Questions