0

I am stuck on an issue that has been causing my mind to ramble for the last couple of days.

DETAILS ABOUT REPORT

This report is to identify the following details based off of the CUSTOMER_ID

  • new customer sales total in the NEWCUSTSALES field
  • existing customer sales total in the EXISTINGSALES field

THE FOLLOWING COLUMN NEEDS TO BE ADDED TO REPORT

  • Calculate the percentage of sales that was represented by new business. (NEWCUSTSALES) / (EXISTINGSALES)

the FY in the query represents FISCAL YEAR

The query executes but does not provide the data I am looking for.

Currently, it is summing all the sales for the date range (65 Million) and dumping that number into the NEWCUSTSALES without taking into consideration I only want the SUM of sales for that CUSTOMER_ID in that row...

Here is a screenshot of the export

Example of output

SELECT A.SITE
     , A.CUSTOMER_ID
     , A.NAME
     , A.CUST_GRP
     , A.REGION_CODE
     ,         (CASE WHEN REGION_CODE 
               IN ('132','137','133','122','156','155','154','158','157') 
             THEN 'Rep1' 
             WHEN REGION_CODE 
               IN ('109','115','110','116','118','117','113','114') 
             THEN 'Rep2' 
             WHEN REGION_CODE 
               IN ('125','144','140','123','141','126','124') 
             THEN 'Rep3' 
             WHEN REGION_CODE 
               IN ('127','131','129','128','112','130') 
             THEN 'Rep4' 
             WHEN REGION_CODE 
             IN ('149','150','142','138','153','147','146','148','143','145','139') 
             THEN 'Rep5' 
             WHEN REGION_CODE 
             IN ('107','105','101','102','108','104','151','111','152','106','103') 
             THEN 'Rep6' 
             WHEN REGION_CODE 
             IN ('121','120','119') 
             THEN 'Rep7'
             ELSE 'Unassigned' 
             END) AS REP
     , A.CREATION_DATE AS STARTING_DATE
     , SUM(A.SALES) AS AMOUNT
     , SUM(A.COST) AS COSTS
     , (SUM(A.SALES) - SUM(A.COST)) AS MARGIN
     , (CASE WHEN B.FIRSTINVDATE BETWEEN TO_DATE('&FY_startDate','mm/dd/yyyy') AND TO_DATE('&FY_end_date','mm/dd/yyyy')
             THEN (SELECT SUM(SALES) FROM IFSINFO.HB_INVOICING_ALL WHERE INVDATE BETWEEN TO_DATE('&FY_startDate','mm/dd/yyyy') AND TO_DATE('&FY_end_date','mm/dd/yyyy'))
             ELSE NULL
             END) AS NEWCUSTSALES
     , (CASE WHEN B.FIRSTINVDATE NOT BETWEEN TO_DATE('&FY_startDate','mm/dd/yyyy') AND TO_DATE('&FY_end_date','mm/dd/yyyy')
             THEN (SELECT SUM(SALES) FROM IFSINFO.HB_INVOICING_ALL WHERE INVDATE BETWEEN TO_DATE('&FY_startDate','mm/dd/yyyy') AND ADD_MONTHS(TRUNC(TO_DATE('&FY_startDate','mm/dd/yyyy'),'mm'),-11))
             ELSE NULL
             END) AS EXISTINGSALES         
FROM IFSINFO.HB_INVOICING_ALL A 
    JOIN (SELECT COMPANY CO, DELIVERY_IDENTITY ID, MIN(FIRSTINVDATE) FIRSTINVDATE 
          FROM IFSINFO.HB_CUST_FIRST_INV 
          GROUP BY COMPANY, DELIVERY_IDENTITY) B  
      ON (A.COMPANY = B.CO) AND (A.CUSTOMER_ID = B.ID)
WHERE A.COMPANY = '&COMPANY'
AND A.SITE = '&STARTING_SITE'
AND TO_DATE(A.INVDATE) BETWEEN ADD_MONTHS(TRUNC(TO_DATE('&END_DATE','mm/dd/yyyy'),'mm'),-11) AND TO_DATE('&END_DATE','mm/dd/yyyy')
GROUP BY A.SITE, A.CUSTOMER_ID, A.NAME, A.CUST_GRP, A.REGION_CODE, A.CREATION_DATE, B.FIRSTINVDATE 
  • 1
    (SELECT SUM(SALES) FROM IFSINFO.HB_INVOICING_ALL WHERE INVDATE BETWEEN TO_DATE('&FY_startDate','mm/dd/yyyy') AND TO_DATE('&FY_end_date','mm/dd/yyyy')) my cystal ball says this line needs to be fixed. – Raj Oct 28 '15 at 18:50
  • @Raj yeah I feel like that line is not tying back into the main query, but I am unsure on how to do so – Hector Oct 28 '15 at 18:52
  • since it is a very simple change, I'll say it is missing a group by clause. if you re-read the question, you will know what you want to group by, so add that missing join condition. – Raj Oct 28 '15 at 19:14
  • @Raj Not sure I am following you, I have added (SELECT SUM(SALES) FROM IFSINFO.HB_INVOICING_ALL WHERE INVDATE BETWEEN TO_DATE('&FY_startDate','mm/dd/yyyy') AND TO_DATE('&FY_end_date','mm/dd/yyyy')GROUP BY CUSTOMER_ID) and am getting a ORA-01427: single-row subquery returns more than one row error at this time – Hector Oct 28 '15 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.