How to compute the turnover each semester

Question

I have a column in an SQL table. That contains a dateof delivery order.

So the same date can be repeat (in one day we delivred severals orders), like this:

05-01-16
05-01-16
05-01-16
08-01-16
08-01-16
14-01-16
22-01-16
22-01-16
04-02-16
05-02-16
05-02-16

I want, compute the AVG of the turnover of each article in each 6 months, I explain more:

From January to June ==> Turnover 1
From Febrary to July ==> Turnover 2
From March to August ==> Turnover 3
From April to September ==> Turnover 4
From May to Obtober ==> Turnover 5
From June to November ==> Turnover 6
From July to December ==> Turnover 7

I'm already extracted the month by the request bellow, but I can't compute dynamically (because my data should be change each month) the turnover like this example above:

select distinct extract (month from Article) as mt 
order by mt

I tried to use a cursor but I can't arrived to the best solution.

I did a request to compute a turnover for each customer per article in the first 6 months (I did it manually ) is the following:

select "LRU", "Client", round(sum("Montant_fac_eur"))
from "foundry"
where "Nature"='Repair' 
and "Client"={{w_widget3.selectedValue}}
and "annee"='2016'
and extract (month from "date") between '1' and '6'


group by "LRU", "Client"

Her result is the following:

 LRU            Client  round
"article1"       4001   8859     Turnover of article1 from January to June
"article2"       4001   94315    Turnover of article2 from January to June
"article3"       4001   273487   Turnover of article3 from January to June
"article4"       4001   22292    Turnover of article4 from January to June
"article5"       4001   22292    Turnover of article5 from January to June
"article6"       4001   42590    Turnover of article6 from January to June
"article7"       4001   9965     Turnover of article7 from January to June
"article8"       4001   39654    Turnover of article8 from January to June
"article9"       4001   3883     Turnover of article9 from January to June
"article10"      4001   41612    Turnover of article10 from January to June

I want do a loop to compute a turnover each 6 months without to write it manually if it possible ? Can someones please help me and give me a solution or suggestion how can I do it ? Thank you.

Answer 1

Here you can view the simplified definition and solution of your problem (if I understood you correctly): http://sqlfiddle.com/#!9/48a2e1/1

CREATE TABLE foundry
(
    lru varchar(50) NOT NULL,
    client int  NOT NULL,
    purchase_date date,
    price int NOT NULL
);

INSERT INTO foundry (lru, client, purchase_date, price) VALUES
("article1", 4001, "01-01-16", 100),
("article1", 4001, "01-01-17", 200),
("article1", 4001, "01-02-16", 300),
("article1", 4001, "01-04-16", 400),
("article1", 4001, "01-06-16", 500),
("article1", 4001, "01-08-16", 600),
("article1", 4001, "01-10-16", 700),
("article1", 4001, "01-11-16", 800),
("article1", 4002, "01-01-16", 900),
("article1", 4002, "01-07-16", 1000),
("article1", 4002, "01-12-16", 1100);

Basically we have a table with four columns: lru (article name), client, purchase date, and some price.

The solution looks like:

SELECT lru, client, avg(price), COUNT(*) as total_items,
MONTHNAME(STR_TO_DATE(L, '%m')) as start_month, MONTHNAME(STR_TO_DATE(R, '%m')) as end_month FROM foundry,
(
  SELECT 1 as L, 6 as R
    UNION ALL
  SELECT 2, 7
    UNION ALL
  SELECT 3, 8
    UNION ALL
  SELECT 4, 9
    UNION ALL
  SELECT 5, 10
    UNION ALL
  SELECT 6, 11
    UNION ALL
  SELECT 7, 12
) months
WHERE month(purchase_date) >= L AND month(purchase_date) <= R
GROUP BY lru, client, L, R

The idea is:

1. Generate all possible combinations of months: 1-6, 2-7, ..., 7,12
2. Join source data with generated months combination
3. Use AVG with GROUP BY

And the result:

lru     client  avg(price)  total_items     start_month     end_month
article1    4001    300     5   January     June
article1    4001    400     3   February    July
article1    4001    500     3   March   August
article1    4001    500     3   April   September
article1    4001    600     3   May     October
article1    4001    650     4   June    November
article1    4001    700     3   July    December
article1    4002    900     1   January     June
article1    4002    1000    1   February    July
article1    4002    1000    1   March   August
article1    4002    1000    1   April   September
article1    4002    1000    1   May     October
article1    4002    1000    1   June    November
article1    4002    1050    2   July    December

Answer 2

I would recommend common table expressions for each six-month coverage. https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx Like: with tp1 (Turnoverperiod, Averagevaule) as ( select 'Period1' as Turnoverperiod, AVG(turnover) as Averagevalue where date between period1.startdate and period2.enddate ) , tp2 as ( .... tp2 )

select * from tp1 union select * from tp2

also, you can create a dynamic sql string (nvarchar(max)) you can programatically append the union queries then use sp_executesql statement.

Answer 3

I am not sure which RDBMs you are using, so here a possible solution with ANSI SQL (use your RDBMs analogue)

Try address this problem in 2 steps:

1. Create a view in a form (lets say you call it v_turnover_per_month), using basic group by syntax over your source table:

   article  month    turnover
   art1     201701  1000
   art1     201702  1020
   ...      ...      ...
   art2     201701  5000
   ...      ...      ...
   

2. Use following select statement

   SELECT  article,
           'Turnover from ' || m1.month || ' until ' || m6.month as title,
           max(m1.turnover + m2.turnover + m3.turnover + m4.turnover + m5.turnover + m6.turnover) as total_turnover_6month
   
   FROM v_turnover_per_month as m6
   JOIN v_turnover_per_month as m5 ON m6.article = m5.article and m5.month+1=m6.month
   JOIN v_turnover_per_month as m4 ON m6.article = m4.article and m4.month+1=m5.month
   JOIN v_turnover_per_month as m3 ON m6.article = m3.article and m3.month+1=m4.month
   JOIN v_turnover_per_month as m2 ON m6.article = m2.article and m2.month+1=m3.month
   JOIN v_turnover_per_month as m1 ON m6.article = m1.article and m1.month+1=m2.month
   
   GROUP BY 1, 2;
   

Answer 4

Here's my take:

DROP TABLE IF EXISTS test;

# credit to tvelykyy for providing some test data and table def which I adapted
CREATE TABLE test (lru VARCHAR(16), `client` INTEGER UNSIGNED, purchase_date DATE, price int NOT NULL);

INSERT INTO test (lru, client, purchase_date, price) VALUES
("article1", 4001, '2016-01-01', 100),
("article1", 4001, '2016-02-01', 200),
("article1", 4001, '2016-03-01', 300),
("article1", 4001, '2016-04-01', 400),
("article1", 4001, '2016-05-01', 500),
("article1", 4001, '2016-06-01', 600),
("article1", 4001, '2016-07-01', 700),
("article1", 4001, '2016-08-01', 800),
("article1", 4002, '2016-01-01', 1100),
("article1", 4002, '2016-06-01', 1200);

SELECT A.lru, A.`client`, ROUND(SUM(price)) round, CONCAT('Turnover of article ', A.lru, ' from ', DATE_FORMAT(DATE_ADD(MAX(B.purchase_date), INTERVAL -6 MONTH), '%M %Y'), ' to ', DATE_FORMAT(MAX(B.purchase_date), '%M %Y')) period FROM
  (
  SELECT DISTINCT lru, LAST_DAY(purchase_date) purchase_month, `client` FROM test
  ) A
LEFT OUTER JOIN
  test B
ON A.lru = B.lru AND A.`client` = B.`client` AND A.purchase_month >= LAST_DAY(B.purchase_date) AND DATE_ADD(A.purchase_month, INTERVAL -6 MONTH) < LAST_DAY(B.purchase_date)
GROUP BY A.lru, A.`client`, A.purchase_month
ORDER BY A.lru, A.`client`, A.purchase_month;

The query works by selecting the DISTINCT lru, clients and months in the subquery A which allows a LEFT JOIN back to the original data ON the lru, client and date in the last six months using the last day of each month to define the months in a standard manner. Finally aggregation is used as specified.

Please let me know if you require further information or I didn't understand the question in its entirety.

Thanks,

James