Tricky Query: Forecasting Dates

Question

Good Day.

I am blocked by this problem in my SQL query:

Given the following table:

CREATE TABLE `Forecasted_Sales_tcl` (
`DEALER_id` varchar(15) NOT NULL,
`SALES_period` date NOT NULL,
`TYPE` int(2) NOT NULL,
`UNIT_SALES` int(6) DEFAULT NULL,
`HEAD_OFFICE_CODE` varchar(15) DEFAULT NULL
PRIMARY KEY (`DEALER_CODE`,`SALES_MONTH`,`TYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

http://sqlfiddle.com/#!2/b780c

I need to generate the possible sales charges in the coming months. For example I have plotted an outbound sales (unit_sales ) for a month (SALES_period) "June 2012" on a store. I am expecting some service charges type A on Aug 2012, type B on Oct 2012, type C on Dec 2012. I Also have some out bound sales on some different months on different stores.

I am trying to generate a report something like this:

  Period  |charge A  | charge B |charge C | store_id
2012-Jan  |    X     |     Y    |    Z    |   (id)
2012-Feb  |    :     |     :    |    :    : 
2012-Mar  |    :     |     :    |    :    : 
2012-Apr  |    :     |     :    |    :    : 
2012-May  |    :     |     :    |    :    : 
2012-Jun  |    :     |     :    |    :    : 
2012-Jul  |    :     |     :    |    :    : 
2012-Aug  |    :     |     :    |    :    : 
2012-Sep  |    :     |     :    |    :    : 
2012-Oct  |    :     |     :    |    :    : 
2012-Nov  |    :     |     :    |    :    : 
2012-Dec  |    :     |     :    |    :    : 

X is total the unit_sales (2 Months Ago) for the store (id) Y is total the unit_sales (4 Months Ago) for the store (id) Z is total the unit_sales (6 Months Ago) for the store (id)

---

Given the said data on the sql fiddle and some parameters: Generate Report: From: 2012-06 To : 2013-07

  Period  | Dealer Id  |  CHARGE X |  CHARGE B |  CHARGE C | 
 2012-06  |    0001    |         0 |         0 |         0 |
 2012-07  |    0001    |         0 |         0 |         0 |
 2012-08  |    0001    |       100 |         0 |         0 |
 2012-09  |    0001    |         0 |         0 |         0 |
 2012-10  |    0001    |         0 |       100 |         0 |
 2012-11  |    0001    |         0 |         0 |         0 |
 2012-12  |    0001    |         0 |         0 |       100 |
 2013-01  |    0001    |         0 |         0 |         0 |
 2013-02  |    0001    |         0 |         0 |         0 |
 2013-03  |    0001    |         0 |         0 |         0 |
 2013-04  |    0001    |         0 |         0 |         0 |
 2013-05  |    0001    |         0 |         0 |         0 |
 2013-06  |    0001    |         0 |         0 |         0 |
 2013-07  |    0001    |         0 |         0 |         0 |

  Period  | Dealer Id  |  CHARGE A |  CHARGE B |  CHARGE C | 
 2012-06  |    0002    |         0 |        10 |         2 |
 2012-07  |    0002    |         0 |         0 |         0 |
 2012-08  |    0002    |        10 |         0 |         0 |
 2012-09  |    0002    |        18 |         0 |         0 |
 2012-10  |    0002    |         5 |        10 |         0 |
 2012-11  |    0002    |         0 |        18 |         0 |
 2012-12  |    0002    |         0 |         5 |        10 |
 2013-01  |    0002    |         0 |         0 |        18 |
 2013-02  |    0002    |         0 |         0 |         5 |
 2013-03  |    0002    |         0 |         0 |         0 |
 2013-04  |    0002    |         0 |         0 |         0 |
 2013-05  |    0002    |         0 |         0 |         0 |
 2013-06  |    0002    |         0 |         0 |         0 |
 2013-07  |    0002    |         0 |         0 |         0 |

On This 10 is for the Sales (2012-04) while 2 is for the sales (2012-02)

  Period  | Dealer Id  |  CHARGE A |  CHARGE B |  CHARGE C | 
 2012-06  |    0003    |         0 |         0 |         0 |
 2012-07  |    0003    |         0 |         0 |         0 |
 2012-08  |    0003    |         1 |         0 |         0 |
 2012-09  |    0003    |         0 |         0 |         0 |
 2012-10  |    0003    |         0 |         1 |         0 |
 2012-11  |    0003    |         0 |         0 |         0 |
 2012-12  |    0003    |         0 |         0 |         1 |
 2013-01  |    0003    |         0 |         0 |         0 |
 2013-02  |    0003    |         0 |         0 |         0 |
 2013-03  |    0003    |         0 |         0 |         0 |
 2013-04  |    0003    |         0 |         0 |         0 |
 2013-05  |    0003    |         0 |         0 |         0 |
 2013-06  |    0003    |         0 |         0 |         0 |
 2013-07  |    0003    |         0 |         0 |         0 |

Master Report

           |               2012-06             |               2012-07             |               2012-08             |               2012-09             |               2012-10             |               2012-11             |
Dealer ID  |  CHARGE A |  CHARGE B |  CHARGE C |  CHARGE A |  CHARGE B |  CHARGE C |  CHARGE A |  CHARGE B |  CHARGE C |  CHARGE A |  CHARGE B |  CHARGE C |  CHARGE A |  CHARGE B |  CHARGE C |  CHARGE A |  CHARGE B |  CHARGE C |
001        |         0 |         0 |         0 |         0 |         0 |         0 |       100 |         0 |         0 |        18 |         0 |         0 |         0 |       100 |         0 |         0 |        18 |         0 |
002        |         0 |        10 |         2 |         0 |         0 |         0 |        10 |         0 |         0 |         0 |         0 |         0 |         0 |        10 |         0 |         0 |         0 |         0 |
003        |         0 |         0 |         0 |         0 |         0 |         0 |         1 |         0 |         0 |         0 |         0 |         0 |         0 |         1 |         0 |         0 |         0 |         0 |

Thanks for the help.

Answer 1

Many thanks for the SQLFiddle ! It's a really nasty report you're trying to achieve here :D

The closest I can get to (while using decent SQL) is this:

SELECT
  DEALER_ID,
  DATE,
  -- Next 3 rows feature the trick to transpose lines to columns.
  SUM(IF(CHARGE = 'A', UNIT_SALES, 0)) as CHARGE_A,
  SUM(IF(CHARGE = 'B', UNIT_SALES, 0)) as CHARGE_B,
  SUM(IF(CHARGE = 'C', UNIT_SALES, 0)) as CHARGE_C
FROM (

SELECT -- Create a row for each charge A.
  DEALER_id,
  'A' as CHARGE,
  DATE_FORMAT(DATE_ADD(SALES_PERIOD, INTERVAL 2 MONTH), "%Y-%m") as DATE,
  UNIT_SALES
FROM forecasted_sales_tcl

UNION

SELECT -- Create a row for each charge B.
  DEALER_id,
  'B' as CHARGE,
  DATE_FORMAT(DATE_ADD(SALES_PERIOD, INTERVAL 4 MONTH), "%Y-%m") as DATE,
  UNIT_SALES
FROM forecasted_sales_tcl

UNION

SELECT -- Create a row for each charge C.
  DEALER_id,
  'C' as CHARGE,
  DATE_FORMAT(DATE_ADD(SALES_PERIOD, INTERVAL 6 MONTH), "%Y-%m") as DATE,
  UNIT_SALES
FROM forecasted_sales_tcl

) T
WHERE DATE >= "2012-06" AND DATE <= "2013-07"
GROUP BY DEALER_ID, DATE
ORDER BY DEALER_ID, DATE;

That gives you exactly what you want (and point out some mistakes in your fake output by the way :p) except that it doesn't generate the empty rows and that's where I stop for decency sake.

I don't say it's not possible but it becomes really ugly to generate it. If you really want to get into it, the first (and most difficult) job is to write a SQL query that generates a single column output:

DATE
2012-06
2012-07
2012-08
(...)
2013-06
2013-07

That could be a nice question to ask on SO :p

For curiosity sake you could have a look at this other trick:

SELECT @row := @row + 1 as row, t.* FROM some_table t, (SELECT @row := 0) r

Anyway, if you really want to get the ouput with the empty rows, the simplest way is to have another table filled with the periods. Next LEFT JOIN finishes the job.

For the Master Report it is exactly the same pattern (and I'll be glad to help you with it if needed) but I strongly discourage you from doing this in SQL as it's not really its job. The transposition will be really ugly, and utterly not parameterizable (spelling ?).

I don't know what you're using to ouput the report but you should look at proper BI Tools for this business. From memory, Jasper Reports, BIRT...

Well, enjoy SQL :p