Optimize a Sql by removing the with

Question

Optimize a Sql by removing the with

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have to calculate the average sale per store-article of the last 7 full weeks.

That is, if today is Wednesday 07/25/2018 , it would take the corresponding sale from 04/06/18 to 07/22/2018 . To position myself on those dates I used next_day() .

In the first sentence, I look for the really open days that each store has had from 04/06/2018 to 22/07/2018 and I keep it.

And those days later I use them in the second sentence for the average sum of sales in the period / number of days.

I am not very convinced of the structure, would there be another better way to calculate the data?

With P1 as(select VECTIE,COUNT (DISTINCT VEFVTD) AS DVENTA from xxxx where VEFVTD BETWEEN (SELECT DATE(NEXT_DAY(CURRENT DATE,'SUNDAY')- 55 DAYS)
FROM SYSIBM.SYSDUMMY1) AND  (SELECT DATE(NEXT_DAY(CURRENT DATE,'SUNDAY')- 7 DAYS)
FROM SYSIBM.SYSDUMMY1) 
GROUP BY VECTIE)

SELECT A.VECTIE, VECPRO,                                
    CAST((SUM(VECVTD)/DVENTA) AS DECIMAL( 9, 3)) VECVTD,  
    CAST((SUM(VECVGR)/DVENTA) AS DECIMAL( 9, 3)) VECVGR   
FROM xxxx A INNER JOIN P1 ON A.VECTIE=P1.VECTIE                                        
WHERE VEFVTD BETWEEN (SELECT DATE(NEXT_DAY(CURRENT DATE,'SUNDAY')- 55 DAYS)
FROM SYSIBM.SYSDUMMY1) AND  (SELECT DATE(NEXT_DAY(CURRENT DATE,'SUNDAY')- 7 DAYS)
FROM SYSIBM.SYSDUMMY1) 

GROUP BY A.VECTIE, VECPRO ,DVENTA

sql

asked by Mer 25.07.2018 в 10:30

source

2 answers

php curl $ _FILES empty (empty) How can I run a service in the background without stopping?

score 0 · Answer 1

And that's the way it does not go:

SELECT A.VECTIE, VECPRO,                                
    CAST((SUM(VECVTD)/COUNT (DISTINCT VEFVTD)) AS DECIMAL( 9, 3)) VECVTD,  
    CAST((SUM(VECVGR)/COUNT (DISTINCT VEFVTD)) AS DECIMAL( 9, 3)) VECVGR   
FROM xxxx A INNER JOIN xxxx1 B ON A.VECTIE=B.VECTIE                                        
WHERE VEFVTD BETWEEN (SELECT DATE(NEXT_DAY(CURRENT DATE,'SUNDAY')- 55 DAYS)
FROM SYSIBM.SYSDUMMY1) AND  (SELECT DATE(NEXT_DAY(CURRENT DATE,'SUNDAY')- 7 DAYS)
FROM SYSIBM.SYSDUMMY1) 

GROUP BY A.VECTIE, VECPRO ,VEFVTD

I remain attentive

score 0 · Answer 2

0

You could do a subquery, removing the "not really open" days and work on it in the second query that you add with an alias

answered by 25.07.2018 в 18:13