A report is required with the number of columns depending on the date range parameter

Question

I am new to SQL. I am currently working on an Oracle database , and I have created a report that retrieves data based on a 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 

Final report for the date range from January 1 to April 30:

 +-----------+--------------+--------------+ |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 this is where I need your help, please. I need to show the income difference for each Seller for each month between January and April.

Therefore, 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'm new, I don’t have permission to create a stored procedure to call this data regularly. Therefore, I may have to write redundant code.

But in fact, the question is, what would the code be able to divide the income for each month into a seller?

In addition, the number of columns varies depending on the date range. For instance; when Jan to April is selected, I get 4 columns for revenue plus 1 column for total revenue. When the previous year is selected from October to April, I get 7 columns for revenue plus 1 column for total revenue.

PLEASE can someone help beginners who want to learn and prove themselves? I would really appreciate your help. Thank you very much in advance.

EDIT:

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

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

Thanks in advance.

Answer 1

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

The ugly part is that you need to know the turning columns in advance (in your case, the months in the selected range). A potential solution to this is to create a dynamic SQL statement using PLSQL, but you may not have permission to execute it. You note in your message that you cannot create procedures (I assume that you do not have a CREATE PROCEDURE grant), but you are not sure that this is the only grant you are missing.

Answer 2

The case statement can work in a sum similar to the Pivot expression.

 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 with the answer from xionutz2k, this also requires knowledge of specific columns.

You can start using Dynamics SQL, but it can get ugly. Here is a link to link to using Dynamic SQL.

Dynamic SQL to generate column names?

Answer 3

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 ...