Need report with the number of columns depending on date range parameter

Question

I'm quite a newbie with SQL. I'm currently working on an Oracle database and I've created a report that pulls up data depending on the date range parameter.

The code is as follows:

SELECT 
  DISTINCT C.CUSTOMER_CODE
, MS.SALESMAN_NAME
, SUM(C.REVENUE_AMT) Rev_Amt


FROM 
    C_REVENUE_ANALYSIS C
  , M_CUSTOMER_H MC
  , M_SALESMAN MS


WHERE C.COMPANY_CODE = 'W1'
  AND C.CUSTOMER_CODE = MC.CUSTOMER_CODE
  AND MC.SALESMAN_CODE = MS.SALESMAN_CODE
  AND trunc(C.REVENUE_DATE) between to_date(<STARTDATE>,'YYYYMMDD') and to_date(<ENDDATE>,'YYYYMMDD')
  AND MS.COMPANY_CODE = '00'

GROUP BY C.CUSTOMER_CODE, MS.SALESMAN_NAME

ORDER BY C.CUSTOMER_CODE, MS.SALESMAN_NAME

The resulting report for a date range from Jan 1st to April 30th is:

+-----------+--------------+--------------+
|Customer   |Salesman Name |Revenue Amount|
+-----------+--------------+--------------+
|Customer 1 |Salesman 1    |       5000.00|
+-----------+--------------+--------------+
|Customer 2 |Salesman 1    |       8000.00|
+-----------+--------------+--------------+
|Customer 3 |Salesman 2    |        300.00|
+-----------+--------------+--------------+
|Customer 4 |Salesman 3    |        600.00|
+-----------+--------------+--------------+
|Customer 5 |Salesman 3    |       5000.00|
+-----------+--------------+--------------+
|Customer 6 |Salesman 3    |       8000.00|
+-----------+--------------+--------------+
|Customer 7 |Salesman 4    |       9000.00|
+-----------+--------------+--------------+
|Customer 8 |Salesman 5    |       2000.00|
+-----------+--------------+--------------+
|Customer 9 |Salesman 6    |       1000.00|
+-----------+--------------+--------------+
|Customer10 |Salesman 6    |       5000.00|
+-----------+--------------+--------------+
|Customer11 |Salesman 7    |       6000.00|
+-----------+--------------+--------------+
|Customer12 |Salesman 8    |       8000.00|
+-----------+--------------+--------------+

Now here is where I need your help, please. I need to show a break up of revenues for each Salesman for each month between Jan to April.

So I would like the result to look like this:

+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer   |Salesman Name |Rev for Jan|Rev for Feb|Rev for Mar|Rev for Apr|Total Rev Amt|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer 1 |Salesman 1    |    1000.00|    1000.00|    1000.00|    2000.00|      5000.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer 2 |Salesman 1    |    2000.00|    2000.00|    2000.00|    2000.00|      8000.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer 3 |Salesman 2    |     100.00|       0.00|     100.00|     100.00|       300.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer 4 |Salesman 3    |     100.00|     200.00|     100.00|     200.00|       600.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer 5 |Salesman 3    |    1000.00|    2000.00|    1000.00|    1000.00|      5000.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer 6 |Salesman 3    |    1000.00|    2000.00|    1000.00|    4000.00|      8000.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer 7 |Salesman 4    |    2000.00|    2000.00|    3000.00|    2000.00|      9000.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer 8 |Salesman 5    |     500.00|     400.00|     500.00|     600.00|      2000.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer 9 |Salesman 6    |     200.00|     200.00|     200.00|     400.00|      1000.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer10 |Salesman 6    |    1000.00|    1000.00|    2000.00|    1000.00|      5000.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer11 |Salesman 7    |    2000.00|    2000.00|    1000.00|    1000.00|      6000.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+
|Customer12 |Salesman 8    |    2000.00|    2000.00|    2000.00|    2000.00|      8000.00|
+-----------+--------------+-----------+-----------+-----------+-----------+-------------+

Unfortunately, since I am a newbie, I don't have rights to create a Stored Procedure to regularly call this data. So I may have to write redundant code.

But the question really is, what would the code be to be able to break down revenues per each month per salesman?

Also, the number of columns varies depending on the Date Range. Eg; when Jan to April is selected, I get 4 columns for revenue plus 1 column for Total revenue. When Previous year's October to this year April is selected, I get 7 columns for revenue plus 1 column for Total revenue.

PLEASE can someone help a newbie eager to learn and prove himself? I would greatly appreciate your help. Thanks a lot in advance.

EDIT :

After a little persuasion, my manager has agreed to submit my stored procedure for approval and if approved, it will be created.

If I do get to create a stored procedure, what will be the code to get the desired result?

Thanks in advance.

score 0 · Answer 1 · answered Apr 23 '15 at 17:51

have a look on the pivot function http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

the ugly part is that you have to know the pivoted columns in advance (in your case the months in the range selected). A potential solution for that is to create a dynamic SQL statement using PLSQL but you might not have to proper rights to execute it. You mention in your post that you can't create procedures (I assume you don't have the CREATE PROCEDURE grant) but not sure if that's the only grant you're missing.

Thank you for your reply. Basically, I don't have any WRITE access. ONLY READ access. How is one expected to prove oneself with limited access???? — Cajetan, Apr 23 '15 at 17:56

score 0 · Answer 2 · edited May 23 '17 at 11:52

0

A case statement may work in the sum, similar to the Pivot statement.

SELECT 
  DISTINCT C.CUSTOMER_CODE
, MS.SALESMAN_NAME
, SUM(C.REVENUE_AMT) Rev_Amt
,CASE WHEN 
    trunc(C.REVENUE_DATE) between to_date('1 jan 2014','YYYYMMDD') and to_date('31 jan 2014','YYYYMMDD') 
    THEN SUM(C.REVENUE_AMT)
    ELSE 0
 END AS Revenue_Jan_2014

FROM 
    C_REVENUE_ANALYSIS C
  , M_CUSTOMER_H MC
  , M_SALESMAN MS


WHERE C.COMPANY_CODE = 'W1'
  AND C.CUSTOMER_CODE = MC.CUSTOMER_CODE
  AND MC.SALESMAN_CODE = MS.SALESMAN_CODE
  AND trunc(C.REVENUE_DATE) between to_date(<STARTDATE>,'YYYYMMDD') and to_date(<ENDDATE>,'YYYYMMDD')
  AND MS.COMPANY_CODE = '00'

GROUP BY C.CUSTOMER_CODE, MS.SALESMAN_NAME

ORDER BY C.CUSTOMER_CODE, MS.SALESMAN_NAME

But, as is the case with the answer by xionutz2k this also requires knowing the specific columns.

You can start using Dynamics SQL, but that can get ugly. Here is a link for reference on using Dynamic SQL.

Dynamic SQL to generate column names?

edited May 23 '17 at 11:52

Community

1
1

answered Apr 23 '15 at 17:56

Richard Vivian

1,700
1
14
19

Thank you Richard. I contemplated using CASE in the code for each column. But that would be nice if I knew the number of columns in advance. The Dynamic SQL sounds nice but wouldn't I need access to create a SPROC for that? – Cajetan Apr 23 '15 at 18:04
You probably will need access, as in most cases you need to generate the SQL as a string , then run the Execute statement. The only other option I can think of is that you get the data from SQL in a flat format, and then use your reporting tool to "Pivot" the data. I know this is quite easy with SSRS. What report tool are you using? – Richard Vivian Apr 23 '15 at 19:37
So I was able to persuade the manager to send my Stored Procedure for approval and if approved, it can be created and I can get to use it. So I can now create things I may need to as long as it gets approved. How do I go about this now? Please advise. And thank you again in advance. – Cajetan Apr 23 '15 at 20:03
Dynamic SQL means using a string variable (DECLARE sSQL as VARCHAR(MAX)) and setting the string eg. SET sSQL = 'SELECT ...Rest of statement here .". Then using an execute command to run that SQL to give you the result. Usually complex and messy. If you can do this in a report tool, it would be better. – Richard Vivian Apr 23 '15 at 20:18
I'm sorry Richard. I should've mentioned earlier. For reporting, I can use SSRS. I can create the report using Visual Studio and upload the report to our reports workspace. What would you suggest? – Cajetan Apr 23 '15 at 20:59
If you get the data in a flat format. I,e have the sales as rows with a month number rather than in a column. Then create a matrix report with SSRS. That will give you the dynamics columns. – Richard Vivian Apr 23 '15 at 22:24

score 0 · Answer 3 · answered Jan 25 '16 at 02:50

Another solution:

select a.cust, a.salesman, 
    (select sum(b.revenue) from rev b 
    where a.cust=b.cust and a.salesman=b.salesman and
    tr_date between to_date('1 jan 2014','YYYYMMDD') and to_date('31 jan 2014')) Jan_Rev,
    (select sum(c.revenue) from rev c
    where a.cust=c.cust and a.salesman=c.salesman and
    tr_date between to_date('1 feb 2014','YYYYMMDD') and to_date('28 feb 2014')) feb_Rev,
    (select sum(d.revenue) from rev d 
    where a.cust=d.cust and a.salesman=d.salesman and
    tr_date between to_date('1 mar 2014','YYYYMMDD') and to_date('31 mar 2014')) mar_Rev
    ... and so on for more months..
from 
    cust_table a
where
  .. add where clause if any ...

score 0 · Answer 4 · answered Aug 26 '20 at 15:06

A pivot like the one below can be used. By using the EXTRACT function to pull the month out of the revenue date, you can then use that to PIVOT and create a column for each month. Also, by using a SUM with a window you can get the total revenue for each salesman/customer combination.

One thing to be wary of is that if your date range spanned across multiple years, the same month for each year would be totaled into the same column.

Example: Jan 2019 and Jan 2020 would be added together if your date range was Jan 1 2019 to Jan 31 2020.

Query

WITH
    d (customer,
       salesman,
       revenue_amount,
       revenue_date)
    AS
        (SELECT 'Customer 1', 'Salesman 1', 20.00, TO_DATE ('1-JAN-2020') FROM DUAL
         UNION ALL
         SELECT 'Customer 1', 'Salesman 1', 30.00, TO_DATE ('8-JAN-2020') FROM DUAL
         UNION ALL
         SELECT 'Customer 1', 'Salesman 1', 30.00, TO_DATE ('21-APR-2020') FROM DUAL
         UNION ALL
         SELECT 'Customer 1', 'Salesman 1', 57.00, TO_DATE ('12-JUN-2020') FROM DUAL
         UNION ALL
         SELECT 'Customer 1', 'Salesman 1', 76.00, TO_DATE ('1-OCT-2020') FROM DUAL
         UNION ALL
         SELECT 'Customer 1', 'Salesman 1', 57.00, TO_DATE ('17-MAR-2020') FROM DUAL
         UNION ALL
         SELECT 'Customer 2', 'Salesman 1', 12.00, TO_DATE ('3-MAY-2020') FROM DUAL
         UNION ALL
         SELECT 'Customer 3', 'Salesman 2', 300.00, TO_DATE ('5-APR-2020') FROM DUAL)
  SELECT customer,
         salesman,
         NVL (jan, 0)     AS rev_for_jan,
         NVL (feb, 0)     AS rev_for_feb,
         NVL (mar, 0)     AS rev_for_mar,
         NVL (apr, 0)     AS rev_for_apr,
         NVL (may, 0)     AS rev_for_may,
         NVL (jun, 0)     AS rev_for_jun,
         NVL (jul, 0)     AS rev_for_jul,
         NVL (aug, 0)     AS rev_for_aug,
         NVL (sep, 0)     AS rev_for_sep,
         NVL (oct, 0)     AS rev_for_oct,
         NVL (nov, 0)     AS rev_for_nov,
         NVL (dec, 0)     AS rev_for_dec,
         total_revenue
    FROM (SELECT d.customer,
                 d.salesman,
                 d.revenue_amount,
                 EXTRACT (MONTH FROM d.revenue_date)                                 AS revenue_month,
                 SUM (revenue_amount) OVER (PARTITION BY d.customer, d.salesman)     AS total_revenue
            FROM d
           WHERE revenue_date BETWEEN TO_DATE ('1-JAN-2020', 'DD-MON-YYYY')
                                  AND TO_DATE ('30-APR-2020', 'DD-MON-YYYY'))
         PIVOT (SUM (revenue_amount)
               FOR revenue_month
               IN (1 AS jan,
                  2 AS feb,
                  3 AS mar,
                  4 AS apr,
                  5 AS may,
                  6 AS jun,
                  7 AS jul,
                  8 AS aug,
                  9 AS sep,
                  10 AS oct,
                  11 AS nov,
                  12 AS dec))
ORDER BY customer, salesman;

Result

     CUSTOMER      SALESMAN    REV_FOR_JAN    REV_FOR_FEB    REV_FOR_MAR    REV_FOR_APR    REV_FOR_MAY    REV_FOR_JUN    REV_FOR_JUL    REV_FOR_AUG    REV_FOR_SEP    REV_FOR_OCT    REV_FOR_NOV    REV_FOR_DEC    TOTAL_REVENUE
_____________ _____________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ________________
Customer 1    Salesman 1                50              0             57             30              0              0              0              0              0              0              0              0              137
Customer 3    Salesman 2                 0              0              0            300              0              0              0              0              0              0              0              0              300

Need report with the number of columns depending on date range parameter

EDIT :

4 Answers4

Query

Result