;WITH
BASE_WEEKS AS
(
SELECT
DATEPART(WEEKDAY,date_astr) WEEK_DAY,
FROM dbo.TST_CALENDAR
WHERE
year_iso=2017 AND
week_iso BETWEEN 40 AND 42
),
FORECAST_WEEKS AS
(
SELECT
DATEPART(WEEKDAY,date_astr) WEEK_DAY,
FROM dbo.TST_CALENDAR
WHERE
year_iso=2017 AND
week_iso=43
),
T_SINGLE AS
(
SELECT
F.whs_id,
F.art_id,
A.id_gr,
C.WEEK_DAY,
SUM(F.fact) SUM_FACT
FROM BASE_WEEKS C
JOIN dbo.TST_FACT F ON C.date_astr=F.date_astr
JOIN dbo.TST_ART A ON F.art_id=A.art_id
GROUP BY
F.whs_id,
F.art_id,
A.id_gr,
C.WEEK_DAY
),
T_GROUP AS
(
SELECT
A.id_gr,
T.WEEK_DAY,
SUM(T.SUM_FACT) SUM_FACT
FROM T_SINGLE T
JOIN dbo.TST_ART A ON T.art_id=A.art_id
GROUP BY
A.id_gr,
T.WEEK_DAY
),
T_AVERAGE AS
(
SELECT
T.id_gr,
CAST(AVG(T.SUM_FACT) AS FLOAT)/
(
SELECT COUNT()
FROM
(
SELECT
year_iso,
week_iso
FROM BASE_WEEKS
GROUP BY
year_iso,
week_iso
) T
) AVG_FACT
FROM T_GROUP T
GROUP BY T.id_gr
),
T_SALE_SHARE AS
(
SELECT
S.WEEK_DAY,
S.whs_id,
S.art_id,
S.id_gr,
CAST(S.SUM_FACT AS FLOAT) / G.SUM_FACT SALE_SHARE
FROM T_SINGLE S
JOIN T_GROUP G
ON
S.id_gr=G.id_gr AND
S.WEEK_DAY=G.WEEK_DAY
),
T_FORECAST AS
(
SELECT
S.whs_id,
S.art_id,
S.WEEK_DAY,
ROUND(S.SALE_SHARE A.AVG_FACT,0) FORECAT
FROM T_SALE_SHARE S
JOIN T_AVERAGE A ON S.id_gr=A.id_gr
)
INSERT DBO.TST_FORECAST
SELECT
W.date_astr,
T.whs_id,
T.art_id,
T.FORECAT
FROM T_FORECAST T
JOIN FORECAST_WEEKS W ON T.WEEK_DAY=W.WEEK_DAY
;
--SELECT * FROM DBO.TST_FORECAST;
-- Ошибка по дням
SELECT ,
C.date_astr,
C.whs_id,
C.art_id,
CAST(ABS(F.fact-C.forecast) AS FLOAT)100/C.forecast
FROM dbo.TST_FORECAST C
JOIN dbo.TST_FACT F
ON
C.date_astr=F.date_astr AND
C.whs_id=F.whs_id AND
C.art_id=F.art_id
;
-- Ошибка за неделю
SELECT
C.whs_id,
C.art_id,
SUM(C.forecast),
SUM(F.fact),
CAST(ABS(SUM(F.fact)-SUM(C.forecast)) AS FLOAT)*100/SUM(C.forecast)
FROM dbo.TST_FORECAST C
JOIN dbo.TST_FACT F
ON
C.date_astr=F.date_astr AND
C.whs_id=F.whs_id AND
C.art_id=F.art_id
GROUP BY
C.whs_id,
C.art_id
;
USE Misc GO
--SET DATEFIRST 1;
;WITH BASE_WEEKS AS ( SELECT DATEPART(WEEKDAY,date_astr) WEEK_DAY, FROM dbo.TST_CALENDAR WHERE year_iso=2017 AND week_iso BETWEEN 40 AND 42 ), FORECAST_WEEKS AS ( SELECT DATEPART(WEEKDAY,date_astr) WEEK_DAY, FROM dbo.TST_CALENDAR WHERE year_iso=2017 AND week_iso=43 ), T_SINGLE AS ( SELECT F.whs_id, F.art_id, A.id_gr, C.WEEK_DAY, SUM(F.fact) SUM_FACT FROM BASE_WEEKS C JOIN dbo.TST_FACT F ON C.date_astr=F.date_astr JOIN dbo.TST_ART A ON F.art_id=A.art_id GROUP BY F.whs_id, F.art_id, A.id_gr, C.WEEK_DAY ), T_GROUP AS ( SELECT A.id_gr, T.WEEK_DAY, SUM(T.SUM_FACT) SUM_FACT FROM T_SINGLE T JOIN dbo.TST_ART A ON T.art_id=A.art_id GROUP BY A.id_gr, T.WEEK_DAY ), T_AVERAGE AS ( SELECT T.id_gr, CAST(AVG(T.SUM_FACT) AS FLOAT)/ ( SELECT COUNT() FROM ( SELECT year_iso, week_iso FROM BASE_WEEKS GROUP BY year_iso, week_iso ) T ) AVG_FACT FROM T_GROUP T GROUP BY T.id_gr ), T_SALE_SHARE AS ( SELECT S.WEEK_DAY, S.whs_id, S.art_id, S.id_gr, CAST(S.SUM_FACT AS FLOAT) / G.SUM_FACT SALE_SHARE FROM T_SINGLE S JOIN T_GROUP G ON S.id_gr=G.id_gr AND S.WEEK_DAY=G.WEEK_DAY ), T_FORECAST AS ( SELECT S.whs_id, S.art_id, S.WEEK_DAY, ROUND(S.SALE_SHARE A.AVG_FACT,0) FORECAT FROM T_SALE_SHARE S JOIN T_AVERAGE A ON S.id_gr=A.id_gr ) INSERT DBO.TST_FORECAST SELECT W.date_astr, T.whs_id, T.art_id, T.FORECAT FROM T_FORECAST T JOIN FORECAST_WEEKS W ON T.WEEK_DAY=W.WEEK_DAY ; --SELECT * FROM DBO.TST_FORECAST;
-- Ошибка по дням SELECT , C.date_astr, C.whs_id, C.art_id, CAST(ABS(F.fact-C.forecast) AS FLOAT)100/C.forecast FROM dbo.TST_FORECAST C JOIN dbo.TST_FACT F ON C.date_astr=F.date_astr AND C.whs_id=F.whs_id AND C.art_id=F.art_id ;
-- Ошибка за неделю SELECT C.whs_id, C.art_id, SUM(C.forecast), SUM(F.fact), CAST(ABS(SUM(F.fact)-SUM(C.forecast)) AS FLOAT)*100/SUM(C.forecast) FROM dbo.TST_FORECAST C JOIN dbo.TST_FACT F ON C.date_astr=F.date_astr AND C.whs_id=F.whs_id AND C.art_id=F.art_id GROUP BY C.whs_id, C.art_id ;