メモリストページ

メモ：メモ一覧

SELECT 
 S.YMD
,S.BRAND_CODE
,S.LOW_YMD
,S.VOLUME
,S.HIGH_AMT

,S.BEF5_AVGVOLUME * END_AMT / 1000 AS BEF5_AVGVOLUME

,M.HIGH_AMT AS BEF_LOW_AMT

FROM 
(
	SELECT 
		 A.YMD
		,A.BRAND_CODE
		,A.END_AMT
		,A.HIGH_AMT
		,A.LOW_AMT
		,A.VOLUME
		,A.BEFORE_AMT

,(SELECT MAX(YMD)
		   FROM TestEveryDay4Data04 Z
		   WHERE Z.BRAND_CODE = A.BRAND_CODE
		     AND Z.YMD < A.YMD) AS LOW_YMD

,(SELECT AVG(Z.VOLUME)
		  FROM EveryDay4Data Z 
		INNER JOIN EigyoCalendar ZZ ON
			    ZZ.YMD = Z.YMD
		  WHERE Z.BRAND_CODE = A.BRAND_CODE 
			    AND ZZ.TEMP_NUM BETWEEN AA.TEMP_NUM - 10 AND AA.TEMP_NUM - 5
		 ) AS BEF5_AVGVOLUME

FROM EveryDay4Data A
	INNER JOIN EigyoCalendar AA ON
		    AA.YMD = A.YMD
	INNER JOIN EveryDay4Data B ON 
		    B.YMD = (select Z.BEF_YMD from EigyoCalendar Z where Z.YMD = A.YMD)
		AND B.BRAND_CODE = A.BRAND_CODE

INNER JOIN MoveAverage AX ON 
		    AX.YMD = A.YMD
		AND AX.BRAND_CODE = A.BRAND_CODE

WHERE
	    A.YMD = '20190308'
	AND A.END_AMT > A.BEFORE_AMT * 1.15
	AND A.END_AMT < A.BEFORE_AMT * 1.7
	AND A.END_AMT > 200

AND AX.move5 < AX.move75 * 1.15

) S
LEFT JOIN TestEveryDay4Data04 M ON
    M.YMD = S.LOW_YMD
AND M.BRAND_CODE = S.BRAND_CODE

WHERE 
	(     S.VOLUME > 100000000 / S.END_AMT
	  AND S.BEF5_AVGVOLUME * S.END_AMT >= 10000000
	)
	OR S.HIGH_AMT = S.LOW_AMT

ORDER BY S.YMD, S.VOLUME / S.BEFORE_AMT DESC