1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
set dateformat dmy;
Declare @t table
(Dt date,
ItemRelation int,
SaleCount int,
DocumentNum int,
DocumentYear int,
ispromo int);
insert into @t values
('20.10.2017', 11511, 245, 5, 2017, 0),
('21.10.2017', 11511, 232, 5, 2017, 0),
('22.10.2017', 11511, 138, 5, 2017, 0),
('23.10.2017', 11511, 143, 5, 2017, 0),
('24.10.2017', 11511, 245, 5, 2017, 0),
('25.10.2017', 11511, 120, 5, 2017, 0),
('26.10.2017', 11511, 196, 5, 2017, 0),
('27.10.2017', 11511, 364, 5, 2017, 0),
('28.10.2017', 11511, 416, 5, 2017, 0),
('29.10.2017', 11511, 252, 5, 2017, 0),
('30.10.2017', 11511, 1096, 5, 2017, 0),
('31.10.2017', 11511, 1333, 5, 2017, 0),
('01.11.2017', 11511, 999, 5, 2017, 0),
('02.11.2017', 11511, 683, 5, 2017, 0),
('03.11.2017', 11511, 429, 5, 2017, 0),
('04.11.2017', 11511, 1935, 5, 2017, 0),
('05.11.2017', 11511, 50, 5, 2017, 0),
('06.11.2017', 11511, 617, 5, 2017, 0),
('07.11.2017', 11511, 415, 5, 2017, 0),
('08.11.2017', 11511, 91, 5, 2017, 0),
('09.11.2017', 11511, 129, 5, 2017, 0),
('10.11.2017', 11511, 238, 5, 2017, 0),
('11.11.2017', 11511, 156, 5, 2017, 0),
('12.11.2017', 11511, 20, 5, 2017, 0),
('13.11.2017', 11511, 122, 5, 2017, 0),
('14.11.2017', 11511, -7, 5, 2017, 0),
('15.11.2017', 11511, 85, 5, 2017, 0),
('16.11.2017', 11511, 162, 5, 2017, 0),
('17.11.2017', 11511, 184, 5, 2017, 0),
('18.11.2017', 11511, 239, 5, 2017, 0),
('19.11.2017', 11511, 0, 5, 2017, 0),
('20.11.2017', 11511, 222, 5, 2017, 0),
('21.11.2017', 11511, 116, 5, 2017, 0),
('22.11.2017', 11511, 107, 5, 2017, 0),
('23.11.2017', 11511, 178, 5, 2017, 0),
('24.11.2017', 11511, 258, 5, 2017, 1),
('25.11.2017', 11511, 343, 5, 2017, 1),
('26.11.2017', 11511, 63, 5, 2017, 1),
('27.11.2017', 11511, 667, 5, 2017, 1),
('28.11.2017', 11511, 1708, 5, 2017, 1),
('29.11.2017', 11511, 1108, 5, 2017, 1),
('30.11.2017', 11511, 841, 5, 2017, 1),
('01.12.2017', 11511, 1256, 5, 2017, 1),
('02.12.2017', 11511, 1037, 5, 2017, 1),
('03.12.2017', 11511, 169, 5, 2017, 1),
('04.12.2017', 11511, 698, 5, 2017, 1),
('05.12.2017', 11511, 532, 5, 2017, 1);
with Numbered as
(
Select 1.0*row_number() over(Order by SaleCount)/count(*) over() as Num,* From @t
),
Procentile as (
Select top 1 SaleCount From Numbered Where Num <=0.75 Order by SaleCount desc
union all
Select top 1 SaleCount From Numbered Where Num >=0.75 Order by SaleCount ),
cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
(Select avg(SaleCount) From Procentile) as PERCENTILE,
avg(SaleCount) over (Partition by datePart(WEEKDAY,Dt)) as AVG_WeekDay
From @t)
Select * From CTE
Where PERCENTILE < SaleCount
and datePart(WEEKDAY,Dt) < 5
and ispromo = 0;
with Numbered as
(
Select 1.0*row_number() over(Order by SaleCount)/count(*) over() as Num,* From @t
),
Procentile as (
Select top 1 SaleCount From Numbered Where Num <=0.75 Order by SaleCount desc
union all
Select top 1 SaleCount From Numbered Where Num >=0.75 Order by SaleCount ),
cte as (
Select *,datePart(WEEKDAY,Dt) as WeekDay,
(Select avg(SaleCount) From Procentile) as PERCENTILE,
avg(SaleCount) over (Partition by datePart(WEEKDAY,Dt)) as AVG_WeekDay
From @t)
Update a
Set SaleCount = cte.AVG_WeekDay
From CTE
join @t a on a.Dt = cte.dt and a.ItemRelation=cte.ItemRelation and a.ispromo = cte.ispromo
Where CTE.PERCENTILE < CTE.SaleCount
and datePart(WEEKDAY,CTE.Dt) < 5
and CTE.ispromo = 0 ;
