-- Перекрытия цен
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; -- Новые цены