Tricky Query: Prediction Dates

Question

Tricky Query: Prediction Dates

Good afternoon.

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 create possible commission fees in the coming months. For example, I calculated the outbound sales (unit_sales) for the month (SALES_period) “June 2012” in the store. I expect some type A service charges in August 2012, Type B through October 2012, Type C, December 2012. I also have some of the related sales for different months in different stores.

:

  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 - unit_sales (2 ) (id) Y - unit_sales (4 ) (id) Z - unit_sales (6 ) (id)

sql : : : 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 |

10 (2012-04), 2 (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 |

-

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

.

+5

mysql

Neff Vullag 16 . '12 14:35

1

Olivier Coilland · Accepted Answer · 2012-06-16T20:43:33+0000

SQLFiddle! , : D

, ( SQL), :

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;

, ( , : p), , , .

, , . , ( ) - SQL-, :

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

, SO: p

:

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

, , - . LEFT JOIN .

( ) , . SQL, . (?).

, , BI . Jasper Reports, BIRT...

, SQL: p

Tricky Query: Prediction Dates

More articles: