-- Перекрытия цен
begin
create table #costs0( CS_PKKEY int, CS_PRKEY int, CS_SVKEY int, CS_TYPE smallint, CS_BYDAY smallint, CS_DISCOUNT smallint, CS_LONG smallint, CS_LONGMIN smallint, CS_CODE int, CS_SUBCODE1 int, CS_SUBCODE2 int, CS_WEEK varchar(7)
, CS_DATE datetime, CS_DATEEND datetime
, cs_DateSellBeg datetime, cs_DateSellEnd datetime
, DateSell bit not null
, CS_RATE varchar(2) not null
, CS_TypeCalc smallint, CS_TypeDivision smallint, CS_TRFId int, CS_COID int
, CS_ID int
, CS_COSTNETTO float not null, CS_COST float not null
, CS_PROFIT float, CS_CINNUM int, CS_CHECKINDATEBEG datetime, CS_CHECKINDATEEND datetime
, CS_CREATOR int, CS_UPDDATE datetime, CS_FIRSTDAYNETTO smallint, CS_FIRSTDAYBRUTTO smallint, CS_UPDUSER varchar(30)
, grID int not null -- идентификатор группы перекрытия
, TrimTypeId smallint not null
, primary key( grID, CS_DATE, CS_id )
--, unique( CS_id )
);
with x as ( select * from #PKs )
, c0 as ( select c.CS_PKKEY, c.CS_PRKEY, c.CS_SVKEY
, CS_TYPE = isnull(c.CS_TYPE, 0)
, CS_BYDAY = isnull(c.CS_BYDAY, 0)
, CS_DISCOUNT = isnull(c.CS_DISCOUNT, 0)
, CS_LONG = isnull(c.CS_LONG, 0)
, CS_LONGMIN = isnull(c.CS_LONGMIN, 0)
, CS_CODE, c.CS_SUBCODE1, c.CS_SUBCODE2
, CS_WEEK = isnull( c.CS_WEEK, '' )
, CS_DATE
, c.CS_DATEEND
, c.cs_DateSellBeg
, c.cs_DateSellEnd
, DateSell = iif(c.cs_DateSellBeg = 0 or c.cs_DateSellBeg is null, 0, 1)
, c.CS_RATE, c.CS_TypeCalc, c.CS_TypeDivision, c.CS_TRFId, c.CS_COID
, c.CS_ID
, c.CS_COSTNETTO, c.CS_COST
, c.CS_PROFIT, c.CS_CINNUM, c.CS_CHECKINDATEBEG, c.CS_CHECKINDATEEND
, c.CS_CREATOR, c.CS_UPDDATE, c.CS_FIRSTDAYNETTO, c.CS_FIRSTDAYBRUTTO, c.CS_UPDUSER
from dbo.tbl_Costs as c with(nolock)
where CS_SVKEY = 3
and exists( select * from x where c.CS_PKKEY = x.PK_KEY )
)
, c as ( select c.*
, x.TrimTypeId
from c0 as c
inner join x on c.CS_PKKEY = x.PK_KEY
where exists( select * from c0 as c1
where c.CS_PKKEY = c1.CS_PKKEY
and c.CS_PRKEY = c1.CS_PRKEY
and c.CS_SVKEY = c1.CS_SVKEY
and c.CS_TYPE = c1.CS_TYPE
and c.CS_BYDAY = c1.CS_BYDAY
and c.CS_LONG = c1.CS_LONG
and c.CS_LONGMIN = c1.CS_LONGMIN
and c.CS_CODE = c1.CS_CODE
and c.CS_SUBCODE1 = c1.CS_SUBCODE1
and c.CS_SUBCODE2 = c1.CS_SUBCODE2
and c.CS_WEEK = c1.CS_WEEK
and ( c.CS_DATE is null or c1.CS_DATEEND is null or c.CS_DATE <= c1.CS_DATEEND ) and ( c1.CS_DATE is null or c.CS_DATEEND is null or c1.CS_DATE <= c.CS_DATEEND )
and ( c.cs_DateSellBeg is null or c1.cs_DateSellEnd is null or c.cs_DateSellBeg < c1.cs_DateSellEnd ) and ( c1.cs_DateSellBeg is null or c.cs_DateSellEnd is null or c1.cs_DateSellBeg < c.cs_DateSellEnd )
and ( ( c.cs_DateSellBeg is null and c1.cs_DateSellBeg is null ) or ( c.cs_DateSellBeg is not null and c1.cs_DateSellBeg is not null ) )
and c.CS_ID <> c1.CS_ID
)
)
insert #costs0 with(tablockx)(CS_PKKEY, CS_PRKEY, CS_SVKEY, CS_TYPE, CS_BYDAY, CS_DISCOUNT, CS_LONG, CS_LONGMIN, CS_CODE, CS_SUBCODE1, CS_SUBCODE2, CS_WEEK
, CS_DATE, CS_DATEEND
, cs_DateSellBeg, cs_DateSellEnd
, DateSell
, CS_RATE, CS_TypeCalc, CS_TypeDivision, CS_TRFId, CS_COID
, CS_ID
, CS_COSTNETTO, CS_COST
, CS_PROFIT, CS_CINNUM, CS_CHECKINDATEBEG, CS_CHECKINDATEEND
, CS_CREATOR, CS_UPDDATE, CS_FIRSTDAYNETTO, CS_FIRSTDAYBRUTTO, CS_UPDUSER
, grID
, TrimTypeId
)
select
CS_PKKEY, CS_PRKEY, CS_SVKEY, CS_TYPE, CS_BYDAY, CS_DISCOUNT, CS_LONG, CS_LONGMIN, CS_CODE, CS_SUBCODE1, CS_SUBCODE2, CS_WEEK
, isnull( CS_DATE, 0 ), isnull( CS_DATEEND, @dateInf )
, isnull(cs_DateSellBeg, 0), isnull( cs_DateSellEnd, @dateInf )
, DateSell = iif(cs_DateSellBeg is not null or cs_DateSellEnd is not null, 1, 0)
, CS_RATE, CS_TypeCalc, CS_TypeDivision, CS_TRFId, CS_COID
, CS_ID
, CS_COSTNETTO, CS_COST
, CS_PROFIT, CS_CINNUM, CS_CHECKINDATEBEG, CS_CHECKINDATEEND
, CS_CREATOR, CS_UPDDATE, CS_FIRSTDAYNETTO, CS_FIRSTDAYBRUTTO, CS_UPDUSER
, grID = RANK() over( order by CS_PKKEY, CS_PRKEY, CS_SVKEY, DateSell, CS_TYPE, CS_BYDAY, CS_DISCOUNT, CS_LONG, CS_LONGMIN, CS_CODE, CS_SUBCODE1, CS_SUBCODE2, CS_WEEK )
, TrimTypeId
from c
order by grID, CS_DATE, CS_ID
;
set @rc = @@rowcount;
exec etl.Show @show, 'перекрытий цен:', @time out, @rc;
if @show = 1 select * from #costs0;
if @Action = 'show overlay' begin
-- обрабатывает #costs0 для показа
exec dbo.tbl_TurList_Packets_TruncateCosts_ShowOverlay
return 2;
end;
if @rc = 0 and @Action = 'update' return 1;
-- все идентификаторы перекрытий
create table #CS_IDs(CS_ID int primary key);
insert #CS_IDs with(tablockx) select CS_ID from #costs0;
end; -- Перекрытия цен
-- Уникальные даты и интервалы
begin
-- уничтожаем тривиальные перекрытия, типа "полное совпадение" или "интервал полностью внутри другого интервала и должен быть усечен"
with t as ( select * from #costs0 )
delete t
from t where exists( select * from t as t1
where t1.grID = t.grID and t1.CS_COSTNETTO * -t.TrimTypeId <= t.CS_COSTNETTO * -t.TrimTypeId and t1.CS_COST * -t.TrimTypeId <= t.CS_COST * -t.TrimTypeId
and ( ( t1.CS_DATE <= t.CS_DATE and t.CS_DATEEND <= t1.CS_DATEEND and ( t1.CS_COST * -t.TrimTypeId < t.CS_COST * -t.TrimTypeId or ( t1.CS_COST = t.CS_COST and t1.CS_ID < t.CS_ID ) ) )
or ( t1.CS_DATE <= t.CS_DATE and t.CS_DATEEND < t1.CS_DATEEND )
or ( t1.CS_DATE < t.CS_DATE and t.CS_DATEEND <= t1.CS_DATEEND )
)
)
;
set @rc = @@rowcount;
exec etl.Show @show, 'удалено тривиальных перекрытий цен:', @time out, @rc;
create table #dates( grID int, CS_DATE datetime, DateEnd bit not null, CS_ID int, id int identity not null unique, primary key( grID, id ) );
create unique index #dates_CS_ID on #dates (CS_ID) where CS_ID is not null;
with x as ( select * from #costs0 )
, t as ( select grID, CS_DATE, DateEnd = 0, CS_ID = min(CS_ID) from x group by grID, CS_DATE
union all
select grID, CS_DATEEND, DateEnd = 1, CS_ID = null from x group by grID, CS_DATEEND
)
insert #dates with(tablockx) select grID, CS_DATE, DateEnd, CS_ID from t order by grID, CS_DATE, DateEnd
;
set @rc = @@rowcount;
exec etl.Show @show, 'дат:', @time out, @rc;
if @show = 1 select * from #dates;
create table #dateInts( grID int, CS_DATE datetime, CS_DATEEND datetime, CS_ID int, primary key( grID, CS_DATE ) );
create unique index #dateInts_CS_ID on #dateInts (CS_ID) where CS_ID is not null;
with x as ( select * from #dates )
insert #dateInts with(tablockx) ( grId, CS_DATE, CS_DATEEND, CS_ID )
select x.grId
, CS_DATE = iif( x.DateEnd = 0, x.CS_DATE, dateadd( day, 1, x.CS_DATE ) )
, CS_DATEEND = iif( x1.DateEnd = 1, x1.CS_DATE, dateadd( day, -1, x.CS_DATE ) )
, x.CS_ID
from x inner join x as x1 on x1.grID = x.grID and x1.id = x.id + 1
where iif( x.DateEnd = 0, x.CS_DATE, dateadd( day, 1, x.CS_DATE ) ) <= iif( x1.DateEnd = 1, x1.CS_DATE, dateadd( day, -1, x.CS_DATE ) )
order by grID, CS_DATE
;
set @rc = @@rowcount;
exec etl.Show @show, 'интервалов:', @time out, @rc;
with c as ( select CS_ID from #dateInts where CS_ID is not null )
, x as ( select CS_ID, n = row_number() over( order by CS_ID) from #costs0 as x where not exists( select * from c where x.CS_ID = c.CS_ID ))
, t as ( select *, n = row_number() over( order by CS_ID ) from #dateInts where CS_ID is null )
update t set CS_ID = x.CS_ID
from t inner join x on t.n = x.n
;
set @rc = @@rowcount;
exec etl.Show @show, 'идентификаторов:', @time out, @rc;
if @show = 1 select '#dateInts' = '#dateInts', * from #dateInts;
end; -- Уникальные даты и интервалы
-- Новые цены
begin
create table #costs( CS_PKKEY int, CS_PRKEY int, CS_SVKEY int, CS_TYPE smallint, CS_BYDAY smallint, CS_DISCOUNT smallint, CS_LONG smallint, CS_LONGMIN smallint, CS_CODE int, CS_SUBCODE1 int, CS_SUBCODE2 int, CS_WEEK varchar(7)
, CS_DATE datetime, CS_DATEEND datetime
, DateSell bit not null
, CS_ID int
, cs_DateSellBeg datetime, cs_DateSellEnd datetime
, CS_RATE varchar(2), CS_TypeCalc smallint, CS_TypeDivision smallint, CS_TRFId int, CS_COID int
, CS_COSTNETTO float, CS_COST float
, CS_PROFIT float, CS_CINNUM int, CS_CHECKINDATEBEG datetime, CS_CHECKINDATEEND datetime
, CS_CREATOR int, CS_UPDDATE datetime, CS_FIRSTDAYNETTO smallint, CS_FIRSTDAYBRUTTO smallint, CS_UPDUSER varchar(30)
, grID int not null -- идентификатор группы перекрытия
);
create clustered index #costs_CS_ID on #costs (CS_ID);
create unique index #costs_FUX_CS_ID on #costs (CS_ID) where CS_ID is not null;
with t as ( select * from #dateInts )
, c as ( select * from #costs0 )
insert #costs with(tablockx) ( CS_PKKEY, CS_PRKEY, CS_SVKEY, CS_TYPE, CS_BYDAY, CS_DISCOUNT, CS_LONG, CS_LONGMIN, CS_CODE, CS_SUBCODE1, CS_SUBCODE2, CS_WEEK
, CS_DATE, CS_DATEEND
, DateSell
, CS_ID
, cs_DateSellBeg, cs_DateSellEnd
, CS_RATE, CS_TypeCalc, CS_TypeDivision, CS_TRFId, CS_COID, CS_COSTNETTO, CS_COST, CS_PROFIT, CS_CINNUM, CS_CHECKINDATEBEG, CS_CHECKINDATEEND
, CS_CREATOR, CS_UPDDATE, CS_FIRSTDAYNETTO, CS_FIRSTDAYBRUTTO, CS_UPDUSER
, grID
)
select x.CS_PKKEY, x.CS_PRKEY, x.CS_SVKEY, x.CS_TYPE, x.CS_BYDAY, x.CS_DISCOUNT, x.CS_LONG, x.CS_LONGMIN, x.CS_CODE, x.CS_SUBCODE1, x.CS_SUBCODE2, x.CS_WEEK
, t.CS_DATE
, t.CS_DATEEND
, x.DateSell
, t.CS_ID
, cs_DateSellBeg = iif( x.DateSell = 0, null, x.cs_DateSellBeg )
, cs_DateSellEnd = iif( x.DateSell = 0, null, x.cs_DateSellEnd )
, x.CS_RATE, x.CS_TypeCalc, x.CS_TypeDivision, x.CS_TRFId, x.CS_COID, x.CS_COSTNETTO, x.CS_COST, x.CS_PROFIT, x.CS_CINNUM, x.CS_CHECKINDATEBEG, x.CS_CHECKINDATEEND
, CS_CREATOR = @CreatorId
, CS_UPDDATE = @now
, x.CS_FIRSTDAYNETTO
, x.CS_FIRSTDAYBRUTTO
, CS_UPDUSER = iif ( x.CS_UPDUSER like @CS_UPDUSER_Pref, x.CS_UPDUSER, @CS_UPDUSER )
, t.grID
from t
cross apply ( select top(1) * from c where t.grID = c.grID and ( t.CS_DATE between c.CS_DATE and c.CS_DATEEND ) and ( t.CS_DATEEND between c.CS_DATE and c.CS_DATEEND ) order by c.CS_COST * c.TrimTypeId desc ) as x
order by t.CS_ID
;
set @rc = @@rowcount;
exec etl.Show @show, 'новых цен:', @time out, @rc;
if @show = 1 select * from #costs;
end; -- Новые цены
|