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.