locked
Comparing Data by time in Sql RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I have the data

    SELECT [Dt]
          ,x1
          ,[SaleCount]
          ,x2
          ,x3
          ,action
      FROM table
    action is an action( 0 no it, 1 is). The essence of the matter is as follows: for example for group

    x1 + x2 + x3
    1    2    2017
    may be an action, and here there are some options for the influence of this group on the other. such as a group

    x1 + x2 + x3,
    2 + 3 + 2017
    I need to restructure the data, so that there are certain combinations. There is a action on the group 1 + 2 + 2017 and no action on the group 2 + 3 + 2017(no marker);

    or there is a action of the group 1 + 2 + 2017, but in group 2 + 3 + 2017 was also action before the action for  group 1 + 2 + 2017(marker before)
    (that is, the ones by action column for 2+3+2017 go before the ones by action column for of group 1 + 2 + 2017)

    or there is a action on the group 1 + 2 + 2017, but on the group 2 + 3 + 2017 there was also an action AFTER the action group 1 + 2 + 2017(marker after)
    (that is, the ones by action column for 2+3+2017 go after the ones by action column for of group 1 + 2 + 2017)

    So I need to allocate combinations of action corresponding to these conditions. data sample

    Declare @t table 
    (Dt date,
      x1 int,
      Sale int,
      x2 int,
      x3 int,
      action int,
      typegroup varchar);
    insert into @t values
    ('23.07.2018',1,2,2017,1,0,basis),
    ('24.07.2018',1,2,2017,2,0,basis),
    ('25.07.2018',1,2,2017,3,0,basis),
    ('26.07.2018',1,2,2017,4,0,basis),
    ('27.07.2018',1,2,2017,5,0,basis),
    ('28.07.2018',1,2,2017,6,0,basis),
    ('29.07.2018',1,2,2017,7,0,basis),
    ('30.07.2018',1,2,2017,8,0,basis),
    ('31.07.2018',1,2,2017,9,0,basis),
    ('01.08.2018',1,2,2017,10,0,basis),
    ('02.08.2018',1,2,2017,11,0,basis),
    ('03.08.2018',1,2,2017,12,1,basis),
    ('04.08.2018',1,2,2017,13,1,basis),
    ('05.08.2018',1,2,2017,14,1,basis),
    ('06.08.2018',1,2,2017,15,1,basis),
    ('07.08.2018',1,2,2017,16,1,basis),
    ('08.08.2018',1,2,2017,17,1,basis),
    ('09.08.2018',1,2,2017,18,1,basis),
    ('10.08.2018',1,2,2017,19,1,basis),
    ('11.08.2018',1,2,2017,20,1,basis),
    ('12.08.2018',1,2,2017,21,1,basis),
    ('13.08.2018',1,2,2017,22,1,basis),
    ('14.08.2018',1,2,2017,23,1,basis),
    ('15.08.2018',1,2,2017,24,1,basis),
    ('16.08.2018',1,2,2017,25,1,basis),
    ('17.08.2018',1,2,2017,26,1,basis),
    ('18.08.2018',1,2,2017,27,0,basis),
    ('19.08.2018',1,2,2017,28,0,basis),
    ('20.08.2018',1,2,2017,29,0,basis),
    ('21.08.2018',1,2,2017,30,0,basis),
    ('22.08.2018',1,2,2017,31,0,basis),
    ('23.08.2018',1,2,2017,32,0,basis),
    ('24.08.2018',1,2,2017,33,0,basis),
    ('25.08.2018',1,2,2017,34,0,basis),
    ('23.07.2018',2,3,2017,1,0,no),
    ('24.07.2018',2,3,2017,2,0,no),
    ('25.07.2018',2,3,2017,3,0,no),
    ('26.07.2018',2,3,2017,4,0,no),
    ('27.07.2018',2,3,2017,5,0,no),
    ('28.07.2018',2,3,2017,6,0,no),
    ('29.07.2018',2,3,2017,7,0,no),
    ('30.07.2018',2,3,2017,8,0,no),
    ('31.07.2018',2,3,2017,9,0,no),
    ('01.08.2018',2,3,2017,10,0,no),
    ('02.08.2018',2,3,2017,11,0,no),
    ('03.08.2018',2,3,2017,12,0,no),
    ('04.08.2018',2,3,2017,13,0,no),
    ('05.08.2018',2,3,2017,14,0,no),
    ('06.08.2018',2,3,2017,15,0,no),
    ('07.08.2018',2,3,2017,16,0,no),
    ('08.08.2018',2,3,2017,17,0,no),
    ('09.08.2018',2,3,2017,18,0,no),
    ('10.08.2018',2,3,2017,19,0,no),
    ('11.08.2018',2,3,2017,20,0,no),
    ('12.08.2018',2,3,2017,21,0,no),
    ('13.08.2018',2,3,2017,22,0,no),
    ('14.08.2018',2,3,2017,23,0,no),
    ('15.08.2018',2,3,2017,24,0,no),
    ('16.08.2018',2,3,2017,25,0,no),
    ('17.08.2018',2,3,2017,26,0,no),
    ('18.08.2018',2,3,2017,27,0,no),
    ('19.08.2018',2,3,2017,28,0,no),
    ('20.08.2018',2,3,2017,29,0,no),
    ('21.08.2018',2,3,2017,30,0,no),
    ('22.08.2018',2,3,2017,31,0,no),
    ('23.08.2018',2,3,2017,32,0,no),
    ('24.08.2018',2,3,2017,33,0,no),
    ('25.08.2018',2,3,2017,34,0,no),
    ('23.07.2018',3,4,2017,1,1,before),
    ('24.07.2018',3,4,2017,2,1,before),
    ('25.07.2018',3,4,2017,3,1,before),
    ('26.07.2018',3,4,2017,4,1,before),
    ('27.07.2018',3,4,2017,5,1,before),
    ('28.07.2018',3,4,2017,6,1,before),
    ('29.07.2018',3,4,2017,7,1,before),
    ('30.07.2018',3,4,2017,8,1,before),
    ('31.07.2018',3,4,2017,9,1,before),
    ('01.08.2018',3,4,2017,10,1,before),
    ('02.08.2018',3,4,2017,11,0,before),
    ('03.08.2018',3,4,2017,12,0,before),
    ('04.08.2018',3,4,2017,13,0,before),
    ('05.08.2018',3,4,2017,14,0,before),
    ('06.08.2018',3,4,2017,15,0,before),
    ('07.08.2018',3,4,2017,16,0,before),
    ('08.08.2018',3,4,2017,17,0,before),
    ('09.08.2018',3,4,2017,18,0,before),
    ('10.08.2018',3,4,2017,19,0,before),
    ('11.08.2018',3,4,2017,20,0,before),
    ('12.08.2018',3,4,2017,21,0,before),
    ('13.08.2018',3,4,2017,22,0,before),
    ('14.08.2018',3,4,2017,23,0,before),
    ('15.08.2018',3,4,2017,24,0,before),
    ('16.08.2018',3,4,2017,25,0,before),
    ('17.08.2018',3,4,2017,26,0,before),
    ('18.08.2018',3,4,2017,27,0,before),
    ('19.08.2018',3,4,2017,28,0,before),
    ('20.08.2018',3,4,2017,29,0,before),
    ('21.08.2018',3,4,2017,30,0,before),
    ('22.08.2018',3,4,2017,31,0,before),
    ('23.08.2018',3,4,2017,32,0,before),
    ('24.08.2018',3,4,2017,33,0,before);
    #

    I compare by time, i.e. at the same time, i looking for all group that meet the above conditions.
    In this example, for the group 1 + 2 + 2017
    group 2 + 3 + 2017 did not have action
    and the group 3 + 4 + 2017 had a action before starting action for 1 + 2 + 2017
    and nothing more no.
    NOW Let's work  the next group for example 3 + 4 + 2017, look at the time when it had an action and how it affected other group in th
    e same time under the specified conditions. I.E 3 + 4 + 2017 became basis.

    How to do it?

    For group, markers must be generated. the basis is the group for which we are looking for comparisons. and everything with which it is compared, marked " no", or "before", or the flag of the "after" , depending on what combination of group in time sql found.

    In other words, there can be very many such recombinations of striations with each other.

    I.E. in relation to one group, 1 + 2 + 2017 may be the basis , and to the other, for example to 10 + 10 + 2017, it can not have any action at all.
    Wednesday, July 25, 2018 9:13 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Sounds like this

    set dateformat dmy
go

Declare @t table 
(Dt date,
  x1 int,
  x2 int,
  x3 int,
  sale int,
  action int,
  typegroup varchar(20));
insert into @t values
('23.07.2018',1,2,2017,1,0,''),
('24.07.2018',1,2,2017,2,0,''),
('25.07.2018',1,2,2017,3,0,''),
('26.07.2018',1,2,2017,4,0,''),
('27.07.2018',1,2,2017,5,0,''),
('28.07.2018',1,2,2017,6,0,''),
('29.07.2018',1,2,2017,7,0,''),
('30.07.2018',1,2,2017,8,0,''),
('31.07.2018',1,2,2017,9,0,''),
('01.08.2018',1,2,2017,10,0,''),
('02.08.2018',1,2,2017,11,0,''),
('03.08.2018',1,2,2017,12,1,''),
('04.08.2018',1,2,2017,13,1,''),
('05.08.2018',1,2,2017,14,1,''),
('06.08.2018',1,2,2017,15,1,''),
('07.08.2018',1,2,2017,16,1,''),
('08.08.2018',1,2,2017,17,1,''),
('09.08.2018',1,2,2017,18,1,''),
('10.08.2018',1,2,2017,19,1,''),
('11.08.2018',1,2,2017,20,1,''),
('12.08.2018',1,2,2017,21,1,''),
('13.08.2018',1,2,2017,22,1,''),
('14.08.2018',1,2,2017,23,1,''),
('15.08.2018',1,2,2017,24,1,''),
('16.08.2018',1,2,2017,25,1,''),
('17.08.2018',1,2,2017,26,1,''),
('18.08.2018',1,2,2017,27,0,''),
('19.08.2018',1,2,2017,28,0,''),
('20.08.2018',1,2,2017,29,0,''),
('21.08.2018',1,2,2017,30,0,''),
('22.08.2018',1,2,2017,31,0,''),
('23.08.2018',1,2,2017,32,0,''),
('24.08.2018',1,2,2017,33,0,''),
('25.08.2018',1,2,2017,34,0,''),
('23.07.2018',2,3,2017,1,0,''),
('24.07.2018',2,3,2017,2,0,''),
('25.07.2018',2,3,2017,3,0,''),
('26.07.2018',2,3,2017,4,0,''),
('27.07.2018',2,3,2017,5,0,''),
('28.07.2018',2,3,2017,6,0,''),
('29.07.2018',2,3,2017,7,0,''),
('30.07.2018',2,3,2017,8,0,''),
('31.07.2018',2,3,2017,9,0,''),
('01.08.2018',2,3,2017,10,0,''),
('02.08.2018',2,3,2017,11,0,''),
('03.08.2018',2,3,2017,12,0,''),
('04.08.2018',2,3,2017,13,0,''),
('05.08.2018',2,3,2017,14,0,''),
('06.08.2018',2,3,2017,15,0,''),
('07.08.2018',2,3,2017,16,0,''),
('08.08.2018',2,3,2017,17,0,''),
('09.08.2018',2,3,2017,18,0,''),
('10.08.2018',2,3,2017,19,0,''),
('11.08.2018',2,3,2017,20,0,''),
('12.08.2018',2,3,2017,21,0,''),
('13.08.2018',2,3,2017,22,0,''),
('14.08.2018',2,3,2017,23,0,''),
('15.08.2018',2,3,2017,24,0,''),
('16.08.2018',2,3,2017,25,0,''),
('17.08.2018',2,3,2017,26,0,''),
('18.08.2018',2,3,2017,27,0,''),
('19.08.2018',2,3,2017,28,0,''),
('20.08.2018',2,3,2017,29,0,''),
('21.08.2018',2,3,2017,30,0,''),
('22.08.2018',2,3,2017,31,0,''),
('23.08.2018',2,3,2017,32,0,''),
('24.08.2018',2,3,2017,33,0,''),
('25.08.2018',2,3,2017,34,0,''),
('23.07.2018',3,4,2017,1,1,''),
('24.07.2018',3,4,2017,2,1,''),
('25.07.2018',3,4,2017,3,1,''),
('26.07.2018',3,4,2017,4,1,''),
('27.07.2018',3,4,2017,5,1,''),
('28.07.2018',3,4,2017,6,1,''),
('29.07.2018',3,4,2017,7,1,''),
('30.07.2018',3,4,2017,8,1,''),
('31.07.2018',3,4,2017,9,1,''),
('01.08.2018',3,4,2017,10,1,''),
('02.08.2018',3,4,2017,11,0,''),
('03.08.2018',3,4,2017,12,0,''),
('04.08.2018',3,4,2017,13,0,''),
('05.08.2018',3,4,2017,14,0,''),
('06.08.2018',3,4,2017,15,0,''),
('07.08.2018',3,4,2017,16,0,''),
('08.08.2018',3,4,2017,17,0,''),
('09.08.2018',3,4,2017,18,0,''),
('10.08.2018',3,4,2017,19,0,''),
('11.08.2018',3,4,2017,20,0,''),
('12.08.2018',3,4,2017,21,0,''),
('13.08.2018',3,4,2017,22,0,''),
('14.08.2018',3,4,2017,23,0,''),
('15.08.2018',3,4,2017,24,0,''),
('16.08.2018',3,4,2017,25,0,''),
('17.08.2018',3,4,2017,26,0,''),
('18.08.2018',3,4,2017,27,1,''),
('19.08.2018',3,4,2017,28,1,''),
('20.08.2018',3,4,2017,29,1,''),
('21.08.2018',3,4,2017,30,1,''),
('22.08.2018',3,4,2017,31,1,''),
('23.08.2018',3,4,2017,32,1,''),
('24.08.2018',3,4,2017,33,1,'');

declare @x1 int,
  @x2 int,
  @x3 int,@mindt date,@maxdt date

--pass any group values here
  select @x1 = 1, @x2 = 2,@x3= 2017
  
  
  Select @mindt = min(Dt), @maxdt = max(Dt)
from @t
where x1 = @x1
and  x2 = @x2
and x3 = @x3
and action =1
  
  update r
  set typegroup= type
  from (select *,
  case 				when x1=@x1 and x2 = @x2 and x3 = @x3 then 'basis'
                when  action = 1 and max(Dt) over (partition by nxt) > coalesce(@maxdt,'99991231') then 'after'
                when  action = 1 and min(Dt) over (partition by nxt) < coalesce(@mindt,'19000101') then 'before'

				end as type
				from @t t
				outer apply
  (
Select min(Dt) as nxt
from @t
where x1 = t.x1
and  x2 = t.x2
and x3 = t.x3
and action <> t.action
and Dt > t.Dt
)t1)r
  

select *
from @t
order by x1,x2,x3,sale

/*
Output
-----------------------------------------------------------
Dt	x1	x2	x3	sale	action	typegroup
--------------------------------------------------------------------------
2018-07-23	1	2	2017	1	0	basis
2018-07-24	1	2	2017	2	0	basis
2018-07-25	1	2	2017	3	0	basis
2018-07-26	1	2	2017	4	0	basis
2018-07-27	1	2	2017	5	0	basis
2018-07-28	1	2	2017	6	0	basis
2018-07-29	1	2	2017	7	0	basis
2018-07-30	1	2	2017	8	0	basis
2018-07-31	1	2	2017	9	0	basis
2018-08-01	1	2	2017	10	0	basis
2018-08-02	1	2	2017	11	0	basis
2018-08-03	1	2	2017	12	1	basis
2018-08-04	1	2	2017	13	1	basis
2018-08-05	1	2	2017	14	1	basis
2018-08-06	1	2	2017	15	1	basis
2018-08-07	1	2	2017	16	1	basis
2018-08-08	1	2	2017	17	1	basis
2018-08-09	1	2	2017	18	1	basis
2018-08-10	1	2	2017	19	1	basis
2018-08-11	1	2	2017	20	1	basis
2018-08-12	1	2	2017	21	1	basis
2018-08-13	1	2	2017	22	1	basis
2018-08-14	1	2	2017	23	1	basis
2018-08-15	1	2	2017	24	1	basis
2018-08-16	1	2	2017	25	1	basis
2018-08-17	1	2	2017	26	1	basis
2018-08-18	1	2	2017	27	0	basis
2018-08-19	1	2	2017	28	0	basis
2018-08-20	1	2	2017	29	0	basis
2018-08-21	1	2	2017	30	0	basis
2018-08-22	1	2	2017	31	0	basis
2018-08-23	1	2	2017	32	0	basis
2018-08-24	1	2	2017	33	0	basis
2018-08-25	1	2	2017	34	0	basis
2018-07-23	2	3	2017	1	0	NULL
2018-07-24	2	3	2017	2	0	NULL
2018-07-25	2	3	2017	3	0	NULL
2018-07-26	2	3	2017	4	0	NULL
2018-07-27	2	3	2017	5	0	NULL
2018-07-28	2	3	2017	6	0	NULL
2018-07-29	2	3	2017	7	0	NULL
2018-07-30	2	3	2017	8	0	NULL
2018-07-31	2	3	2017	9	0	NULL
2018-08-01	2	3	2017	10	0	NULL
2018-08-02	2	3	2017	11	0	NULL
2018-08-03	2	3	2017	12	0	NULL
2018-08-04	2	3	2017	13	0	NULL
2018-08-05	2	3	2017	14	0	NULL
2018-08-06	2	3	2017	15	0	NULL
2018-08-07	2	3	2017	16	0	NULL
2018-08-08	2	3	2017	17	0	NULL
2018-08-09	2	3	2017	18	0	NULL
2018-08-10	2	3	2017	19	0	NULL
2018-08-11	2	3	2017	20	0	NULL
2018-08-12	2	3	2017	21	0	NULL
2018-08-13	2	3	2017	22	0	NULL
2018-08-14	2	3	2017	23	0	NULL
2018-08-15	2	3	2017	24	0	NULL
2018-08-16	2	3	2017	25	0	NULL
2018-08-17	2	3	2017	26	0	NULL
2018-08-18	2	3	2017	27	0	NULL
2018-08-19	2	3	2017	28	0	NULL
2018-08-20	2	3	2017	29	0	NULL
2018-08-21	2	3	2017	30	0	NULL
2018-08-22	2	3	2017	31	0	NULL
2018-08-23	2	3	2017	32	0	NULL
2018-08-24	2	3	2017	33	0	NULL
2018-08-25	2	3	2017	34	0	NULL
2018-07-23	3	4	2017	1	1	before
2018-07-24	3	4	2017	2	1	before
2018-07-25	3	4	2017	3	1	before
2018-07-26	3	4	2017	4	1	before
2018-07-27	3	4	2017	5	1	before
2018-07-28	3	4	2017	6	1	before
2018-07-29	3	4	2017	7	1	before
2018-07-30	3	4	2017	8	1	before
2018-07-31	3	4	2017	9	1	before
2018-08-01	3	4	2017	10	1	before
2018-08-02	3	4	2017	11	0	NULL
2018-08-03	3	4	2017	12	0	NULL
2018-08-04	3	4	2017	13	0	NULL
2018-08-05	3	4	2017	14	0	NULL
2018-08-06	3	4	2017	15	0	NULL
2018-08-07	3	4	2017	16	0	NULL
2018-08-08	3	4	2017	17	0	NULL
2018-08-09	3	4	2017	18	0	NULL
2018-08-10	3	4	2017	19	0	NULL
2018-08-11	3	4	2017	20	0	NULL
2018-08-12	3	4	2017	21	0	NULL
2018-08-13	3	4	2017	22	0	NULL
2018-08-14	3	4	2017	23	0	NULL
2018-08-15	3	4	2017	24	0	NULL
2018-08-16	3	4	2017	25	0	NULL
2018-08-17	3	4	2017	26	0	NULL
2018-08-18	3	4	2017	27	1	after
2018-08-19	3	4	2017	28	1	after
2018-08-20	3	4	2017	29	1	after
2018-08-21	3	4	2017	30	1	after
2018-08-22	3	4	2017	31	1	after
2018-08-23	3	4	2017	32	1	after
2018-08-24	3	4	2017	33	1	after

*/

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by merzavazeh Sunday, July 29, 2018 8:37 AM
    Wednesday, July 25, 2018 3:39 PM
  • Question
    Sign in to vote
    1
    Sign in to vote
    Yes, thank you

    But

    2018-08-13 1 2 2017 22 1 basis
    2018-08-14 1 2 2017 23 1 basis
    2018-08-15 1 2 2017 24 1 basis
    2018-08-16 1 2 2017 25 1 basis
    2018-08-17 1 2 2017 26 1 basis
    2018-08-18 1 2 2017 27 0 basis
    2018-08-19 1 2 2017 28 0 basis
    2018-08-20 1 2 2017 29 0 basis
    2018-08-21 1 2 2017 30 0 basis
    2018-08-22 1 2 2017 31 0 basis
    2018-08-23 1 2 2017 32 0 basis
    2018-08-24 1 2 2017 33 0 basis
    2018-08-25 1 2 2017 34 0 basis
    2018-07-23 2 3 2017 1 0 after
    2018-07-24 2 3 2017 2 0 after
    2018-07-25 2 3 2017 3 0 after
    2018-07-26 2 3 2017 4 0 after
    2018-07-27 2 3 2017 5 0 after
    2018-07-28 2 3 2017 6 0 after
    2018-07-29 2 3 2017 7 0 after
    2018-07-30 2 3 2017 8 0 after
    2018-07-31 2 3 2017 9 0 after
    2018-08-01 2 3 2017 10 0 after
    2018-08-02 2 3 2017 11 0 after
    2018-08-03 2 3 2017 12 0 after
    2018-08-04 2 3 2017 13 0 after
    2018-08-05 2 3 2017 14 0 after
    2018-08-06 2 3 2017 15 0 after
    2018-08-07 2 3 2017 16 0 after
    2018-08-08 2 3 2017 17 0 after
    2018-08-09 2 3 2017 18 0 after
    2018-08-10 2 3 2017 19 0 after
    2018-08-11 2 3 2017 20 0 after
    2018-08-12 2 3 2017 21 0 after
    2018-08-13 2 3 2017 22 0 after
    2018-08-14 2 3 2017 23 0 after
    2018-08-15 2 3 2017 24 0 after
    2018-08-16 2 3 2017 25 0 after
    2018-08-17 2 3 2017 26 0 after
    2018-08-18 2 3 2017 27 0 after
    2018-08-19 2 3 2017 28 0 after
    2018-08-20 2 3 2017 29 0 after
    2018-08-21 2 3 2017 30 0 after
    2018-08-22 2 3 2017 31 0 after
    2018-08-23 2 3 2017 32 0 after
    2018-08-24 2 3 2017 33 0 after
    2018-08-25 2 3 2017 34 0 after



    group 2+3+2017 it is group where there not action. As you can see, for this group by action column 

    only zero. Why it marked as after. it is must be marked as "NO"


    Oh ok

    didnt notice that

    here you go

    set dateformat dmy
go

Declare @t table 
(Dt date,
  x1 int,
  x2 int,
  x3 int,
  sale int,
  action int,
  typegroup varchar(20));
insert into @t values
('23.07.2018',1,2,2017,1,0,''),
('24.07.2018',1,2,2017,2,0,''),
('25.07.2018',1,2,2017,3,0,''),
('26.07.2018',1,2,2017,4,0,''),
('27.07.2018',1,2,2017,5,0,''),
('28.07.2018',1,2,2017,6,0,''),
('29.07.2018',1,2,2017,7,0,''),
('30.07.2018',1,2,2017,8,0,''),
('31.07.2018',1,2,2017,9,0,''),
('01.08.2018',1,2,2017,10,0,''),
('02.08.2018',1,2,2017,11,0,''),
('03.08.2018',1,2,2017,12,1,''),
('04.08.2018',1,2,2017,13,1,''),
('05.08.2018',1,2,2017,14,1,''),
('06.08.2018',1,2,2017,15,1,''),
('07.08.2018',1,2,2017,16,1,''),
('08.08.2018',1,2,2017,17,1,''),
('09.08.2018',1,2,2017,18,1,''),
('10.08.2018',1,2,2017,19,1,''),
('11.08.2018',1,2,2017,20,1,''),
('12.08.2018',1,2,2017,21,1,''),
('13.08.2018',1,2,2017,22,1,''),
('14.08.2018',1,2,2017,23,1,''),
('15.08.2018',1,2,2017,24,1,''),
('16.08.2018',1,2,2017,25,1,''),
('17.08.2018',1,2,2017,26,1,''),
('18.08.2018',1,2,2017,27,0,''),
('19.08.2018',1,2,2017,28,0,''),
('20.08.2018',1,2,2017,29,0,''),
('21.08.2018',1,2,2017,30,0,''),
('22.08.2018',1,2,2017,31,0,''),
('23.08.2018',1,2,2017,32,0,''),
('24.08.2018',1,2,2017,33,0,''),
('25.08.2018',1,2,2017,34,0,''),
('23.07.2018',2,3,2017,1,0,''),
('24.07.2018',2,3,2017,2,0,''),
('25.07.2018',2,3,2017,3,0,''),
('26.07.2018',2,3,2017,4,0,''),
('27.07.2018',2,3,2017,5,0,''),
('28.07.2018',2,3,2017,6,0,''),
('29.07.2018',2,3,2017,7,0,''),
('30.07.2018',2,3,2017,8,0,''),
('31.07.2018',2,3,2017,9,0,''),
('01.08.2018',2,3,2017,10,0,''),
('02.08.2018',2,3,2017,11,0,''),
('03.08.2018',2,3,2017,12,0,''),
('04.08.2018',2,3,2017,13,0,''),
('05.08.2018',2,3,2017,14,0,''),
('06.08.2018',2,3,2017,15,0,''),
('07.08.2018',2,3,2017,16,0,''),
('08.08.2018',2,3,2017,17,0,''),
('09.08.2018',2,3,2017,18,0,''),
('10.08.2018',2,3,2017,19,0,''),
('11.08.2018',2,3,2017,20,0,''),
('12.08.2018',2,3,2017,21,0,''),
('13.08.2018',2,3,2017,22,0,''),
('14.08.2018',2,3,2017,23,0,''),
('15.08.2018',2,3,2017,24,0,''),
('16.08.2018',2,3,2017,25,0,''),
('17.08.2018',2,3,2017,26,0,''),
('18.08.2018',2,3,2017,27,0,''),
('19.08.2018',2,3,2017,28,0,''),
('20.08.2018',2,3,2017,29,0,''),
('21.08.2018',2,3,2017,30,0,''),
('22.08.2018',2,3,2017,31,0,''),
('23.08.2018',2,3,2017,32,0,''),
('24.08.2018',2,3,2017,33,0,''),
('25.08.2018',2,3,2017,34,0,''),
('23.07.2018',3,4,2017,1,1,''),
('24.07.2018',3,4,2017,2,1,''),
('25.07.2018',3,4,2017,3,1,''),
('26.07.2018',3,4,2017,4,1,''),
('27.07.2018',3,4,2017,5,1,''),
('28.07.2018',3,4,2017,6,1,''),
('29.07.2018',3,4,2017,7,1,''),
('30.07.2018',3,4,2017,8,1,''),
('31.07.2018',3,4,2017,9,1,''),
('01.08.2018',3,4,2017,10,1,''),
('02.08.2018',3,4,2017,11,0,''),
('03.08.2018',3,4,2017,12,0,''),
('04.08.2018',3,4,2017,13,0,''),
('05.08.2018',3,4,2017,14,0,''),
('06.08.2018',3,4,2017,15,0,''),
('07.08.2018',3,4,2017,16,0,''),
('08.08.2018',3,4,2017,17,0,''),
('09.08.2018',3,4,2017,18,0,''),
('10.08.2018',3,4,2017,19,0,''),
('11.08.2018',3,4,2017,20,0,''),
('12.08.2018',3,4,2017,21,0,''),
('13.08.2018',3,4,2017,22,0,''),
('14.08.2018',3,4,2017,23,0,''),
('15.08.2018',3,4,2017,24,0,''),
('16.08.2018',3,4,2017,25,0,''),
('17.08.2018',3,4,2017,26,0,''),
('18.08.2018',3,4,2017,27,1,''),
('19.08.2018',3,4,2017,28,1,''),
('20.08.2018',3,4,2017,29,1,''),
('21.08.2018',3,4,2017,30,1,''),
('22.08.2018',3,4,2017,31,1,''),
('23.08.2018',3,4,2017,32,1,''),
('24.08.2018',3,4,2017,33,1,'');

declare @x1 int,
  @x2 int,
  @x3 int,@mindt date,@maxdt date

--pass any group values here
  select @x1 = 1, @x2 = 2,@x3= 2017
  
  
  Select @mindt = min(Dt), @maxdt = max(Dt)
from @t
where x1 = @x1
and  x2 = @x2
and x3 = @x3
and action =1
  
  update r
  set typegroup= type
  from (select *,
  case 				when x1=@x1 and x2 = @x2 and x3 = @x3 then 'basis'
                 when  sum(case when action = 1 then 1 else 0 end) over (partition by x1,x2,x3) = 0 then 'No'
                when  sum(case when action = 1 then 1 else 0 end) over (partition by x1,x2,x3) > 0 and  max(Dt) over (partition by nxt) > coalesce(@maxdt,'99991231') then 'after'
                when  sum(case when action = 1 then 1 else 0 end) over (partition by x1,x2,x3) > 0 and min(Dt) over (partition by nxt) < coalesce(@mindt,'19000101') then 'before'

				end as type
				from @t t
				outer apply
  (
Select min(Dt) as nxt
from @t
where x1 = t.x1
and  x2 = t.x2
and x3 = t.x3
and action <> t.action
and Dt > t.Dt
)t1)r
  

select *
from @t
order by x1,x2,x3,sale


/*
Output
------------------------------------------------------
Dt	x1	x2	x3	sale	action	typegroup
-------------------------------------------------------------------------
2018-07-23	1	2	2017	1	0	basis
2018-07-24	1	2	2017	2	0	basis
2018-07-25	1	2	2017	3	0	basis
2018-07-26	1	2	2017	4	0	basis
2018-07-27	1	2	2017	5	0	basis
2018-07-28	1	2	2017	6	0	basis
2018-07-29	1	2	2017	7	0	basis
2018-07-30	1	2	2017	8	0	basis
2018-07-31	1	2	2017	9	0	basis
2018-08-01	1	2	2017	10	0	basis
2018-08-02	1	2	2017	11	0	basis
2018-08-03	1	2	2017	12	1	basis
2018-08-04	1	2	2017	13	1	basis
2018-08-05	1	2	2017	14	1	basis
2018-08-06	1	2	2017	15	1	basis
2018-08-07	1	2	2017	16	1	basis
2018-08-08	1	2	2017	17	1	basis
2018-08-09	1	2	2017	18	1	basis
2018-08-10	1	2	2017	19	1	basis
2018-08-11	1	2	2017	20	1	basis
2018-08-12	1	2	2017	21	1	basis
2018-08-13	1	2	2017	22	1	basis
2018-08-14	1	2	2017	23	1	basis
2018-08-15	1	2	2017	24	1	basis
2018-08-16	1	2	2017	25	1	basis
2018-08-17	1	2	2017	26	1	basis
2018-08-18	1	2	2017	27	0	basis
2018-08-19	1	2	2017	28	0	basis
2018-08-20	1	2	2017	29	0	basis
2018-08-21	1	2	2017	30	0	basis
2018-08-22	1	2	2017	31	0	basis
2018-08-23	1	2	2017	32	0	basis
2018-08-24	1	2	2017	33	0	basis
2018-08-25	1	2	2017	34	0	basis
2018-07-23	2	3	2017	1	0	No
2018-07-24	2	3	2017	2	0	No
2018-07-25	2	3	2017	3	0	No
2018-07-26	2	3	2017	4	0	No
2018-07-27	2	3	2017	5	0	No
2018-07-28	2	3	2017	6	0	No
2018-07-29	2	3	2017	7	0	No
2018-07-30	2	3	2017	8	0	No
2018-07-31	2	3	2017	9	0	No
2018-08-01	2	3	2017	10	0	No
2018-08-02	2	3	2017	11	0	No
2018-08-03	2	3	2017	12	0	No
2018-08-04	2	3	2017	13	0	No
2018-08-05	2	3	2017	14	0	No
2018-08-06	2	3	2017	15	0	No
2018-08-07	2	3	2017	16	0	No
2018-08-08	2	3	2017	17	0	No
2018-08-09	2	3	2017	18	0	No
2018-08-10	2	3	2017	19	0	No
2018-08-11	2	3	2017	20	0	No
2018-08-12	2	3	2017	21	0	No
2018-08-13	2	3	2017	22	0	No
2018-08-14	2	3	2017	23	0	No
2018-08-15	2	3	2017	24	0	No
2018-08-16	2	3	2017	25	0	No
2018-08-17	2	3	2017	26	0	No
2018-08-18	2	3	2017	27	0	No
2018-08-19	2	3	2017	28	0	No
2018-08-20	2	3	2017	29	0	No
2018-08-21	2	3	2017	30	0	No
2018-08-22	2	3	2017	31	0	No
2018-08-23	2	3	2017	32	0	No
2018-08-24	2	3	2017	33	0	No
2018-08-25	2	3	2017	34	0	No
2018-07-23	3	4	2017	1	1	before
2018-07-24	3	4	2017	2	1	before
2018-07-25	3	4	2017	3	1	before
2018-07-26	3	4	2017	4	1	before
2018-07-27	3	4	2017	5	1	before
2018-07-28	3	4	2017	6	1	before
2018-07-29	3	4	2017	7	1	before
2018-07-30	3	4	2017	8	1	before
2018-07-31	3	4	2017	9	1	before
2018-08-01	3	4	2017	10	1	before
2018-08-02	3	4	2017	11	0	before
2018-08-03	3	4	2017	12	0	before
2018-08-04	3	4	2017	13	0	before
2018-08-05	3	4	2017	14	0	before
2018-08-06	3	4	2017	15	0	before
2018-08-07	3	4	2017	16	0	before
2018-08-08	3	4	2017	17	0	before
2018-08-09	3	4	2017	18	0	before
2018-08-10	3	4	2017	19	0	before
2018-08-11	3	4	2017	20	0	before
2018-08-12	3	4	2017	21	0	before
2018-08-13	3	4	2017	22	0	before
2018-08-14	3	4	2017	23	0	before
2018-08-15	3	4	2017	24	0	before
2018-08-16	3	4	2017	25	0	before
2018-08-17	3	4	2017	26	0	before
2018-08-18	3	4	2017	27	1	after
2018-08-19	3	4	2017	28	1	after
2018-08-20	3	4	2017	29	1	after
2018-08-21	3	4	2017	30	1	after
2018-08-22	3	4	2017	31	1	after
2018-08-23	3	4	2017	32	1	after
2018-08-24	3	4	2017	33	1	after

*/

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by merzavazeh Sunday, July 29, 2018 9:22 AM
    Sunday, July 29, 2018 8:54 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    What is the expected output from the sample data given above?

    N 56°04'39.26"
    E 12°55'05.63"

    Wednesday, July 25, 2018 9:19 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    sorry didnt understand what is the result you are looking

    can you post your expected result for the sample data above?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 25, 2018 9:19 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    sample data is needed output

    we take groups, than compare by time as i described

    and as output i must have this table with marked value - sample data

    Do you understrand what i want.

    Wednesday, July 25, 2018 11:46 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    sample data is needed output

    we take groups, than compare by time as i described

    and as output i must have this table with marked value - sample data

    Do you understrand what i want.



    Wednesday, July 25, 2018 11:47 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    sample data is needed output

    we take groups, than compare by time as i described

    and as output i must have this table with marked value - sample data

    Do you understrand what i want.



    Sample data is needed output?

    what does that mean?

    then atleast tell us what is that it needs as input from user?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 25, 2018 12:05 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I'll try

    suppose there is group

    dt x1 x2 x3 sale action
    ('23.07.2018' 1 2 2017 1 0
    ('24.07.2018' 1 2 2017 2 0
    ('25.07.2018' 1 2 2017 3 0
    ('26.07.2018' 1 2 2017 4 0
    ('27.07.2018' 1 2 2017 5 0
    ('28.07.2018' 1 2 2017 6 0
    ('29.07.2018' 1 2 2017 7 0
    ('30.07.2018' 1 2 2017 8 0
    ('31.07.2018' 1 2 2017 9 0
    ('01.08.2018' 1 2 2017 10 0
    ('02.08.2018' 1 2 2017 11 0
    ('03.08.2018' 1 2 2017 12 1
    ('04.08.2018' 1 2 2017 13 1
    ('05.08.2018' 1 2 2017 14 1
    ('06.08.2018' 1 2 2017 15 1
    ('07.08.2018' 1 2 2017 16 1
    ('08.08.2018' 1 2 2017 17 1
    ('09.08.2018' 1 2 2017 18 1
    ('10.08.2018' 1 2 2017 19 1
    ('11.08.2018' 1 2 2017 20 1
    ('12.08.2018' 1 2 2017 21 1
    ('13.08.2018' 1 2 2017 22 1
    ('14.08.2018' 1 2 2017 23 1
    ('15.08.2018' 1 2 2017 24 1
    ('16.08.2018' 1 2 2017 25 1
    ('17.08.2018' 1 2 2017 26 1
    ('18.08.2018' 1 2 2017 27 0
    ('19.08.2018' 1 2 2017 28 0
    ('20.08.2018' 1 2 2017 29 0
    ('21.08.2018' 1 2 2017 30 0
    ('22.08.2018' 1 2 2017 31 0
    ('23.08.2018' 1 2 2017 32 0
    ('24.08.2018' 1 2 2017 33 0
    ('25.08.2018' 1 2 2017 34 0

    this group (1+2+2017)has data from 23.7-25.08 right? So action for this  group  was from 3.8-17.8

    1 is action.

    I must find for this group 1+2+2017 these group, for which there is at least one conditon

    So sql finds , were  in this time period 23.7-25.08 groups which didn't have action

    or it was action before action for 1+2+2017 

    or it was action after action for 1+2+2017 group

    Do you understand?:))

     sql found that  for this group  in this period was next situation

    group  2+3+2017 didn't have action while 1+2+2017 was action

    cause by action for 2+3+2017 only zero

    ('23.07.2018' 2 3 2017 1 0
    ('24.07.2018' 2 3 2017 2 0
    ('25.07.2018' 2 3 2017 3 0
    ('26.07.2018' 2 3 2017 4 0
    ('27.07.2018' 2 3 2017 5 0
    ('28.07.2018' 2 3 2017 6 0
    ('29.07.2018' 2 3 2017 7 0
    ('30.07.2018' 2 3 2017 8 0
    ('31.07.2018' 2 3 2017 9 0
    ('01.08.2018' 2 3 2017 10 0
    ('02.08.2018' 2 3 2017 11 0
    ('03.08.2018' 2 3 2017 12 0
    ('04.08.2018' 2 3 2017 13 0
    ('05.08.2018' 2 3 2017 14 0
    ('06.08.2018' 2 3 2017 15 0
    ('07.08.2018' 2 3 2017 16 0
    ('08.08.2018' 2 3 2017 17 0
    ('09.08.2018' 2 3 2017 18 0
    ('10.08.2018' 2 3 2017 19 0
    ('11.08.2018' 2 3 2017 20 0
    ('12.08.2018' 2 3 2017 21 0
    ('13.08.2018' 2 3 2017 22 0
    ('14.08.2018' 2 3 2017 23 0
    ('15.08.2018' 2 3 2017 24 0
    ('16.08.2018' 2 3 2017 25 0
    ('17.08.2018' 2 3 2017 26 0
    ('18.08.2018' 2 3 2017 27 0
    ('19.08.2018' 2 3 2017 28 0
    ('20.08.2018' 2 3 2017 29 0
    ('21.08.2018' 2 3 2017 30 0
    ('22.08.2018' 2 3 2017 31 0
    ('23.08.2018' 2 3 2017 32 0
    ('24.08.2018' 2 3 2017 33 0
    ('25.08.2018' 2 3 2017 34 0

    and for group

    3+4+2017 action was before action for 1+2+2017

    cause we have ones to 03/08 and then began action for 1+2+2017

    ('23.07.2018' 3 4 2017 1 1
    ('24.07.2018' 3 4 2017 2 1
    ('25.07.2018' 3 4 2017 3 1
    ('26.07.2018' 3 4 2017 4 1
    ('27.07.2018' 3 4 2017 5 1
    ('28.07.2018' 3 4 2017 6 1
    ('29.07.2018' 3 4 2017 7 1
    ('30.07.2018' 3 4 2017 8 1
    ('31.07.2018' 3 4 2017 9 1
    ('01.08.2018' 3 4 2017 10 1
    ('02.08.2018' 3 4 2017 11 0
    ('03.08.2018' 3 4 2017 12 0
    ('04.08.2018' 3 4 2017 13 0
    ('05.08.2018' 3 4 2017 14 0
    ('06.08.2018' 3 4 2017 15 0
    ('07.08.2018' 3 4 2017 16 0
    ('08.08.2018' 3 4 2017 17 0
    ('09.08.2018' 3 4 2017 18 0
    ('10.08.2018' 3 4 2017 19 0
    ('11.08.2018' 3 4 2017 20 0
    ('12.08.2018' 3 4 2017 21 0
    ('13.08.2018' 3 4 2017 22 0
    ('14.08.2018' 3 4 2017 23 0
    ('15.08.2018' 3 4 2017 24 0
    ('16.08.2018' 3 4 2017 25 0
    ('17.08.2018' 3 4 2017 26 0
    ('18.08.2018' 3 4 2017 27 0
    ('19.08.2018' 3 4 2017 28 0
    ('20.08.2018' 3 4 2017 29 0
    ('21.08.2018' 3 4 2017 30 0
    ('22.08.2018' 3 4 2017 31 0
    ('23.08.2018' 3 4 2017 32 0
    ('24.08.2018' 3 4 2017 33 0

    there is condition when  group was action after action 1+2+2017. I.E ones goes after 17.08

    ('23.07.2018' 4 5 2017 1 0
    ('24.07.2018' 3 4 2017 2 0
    ('25.07.2018' 3 4 2017 3 0
    ('26.07.2018' 3 4 2017 4 0
    ('27.07.2018' 3 4 2017 5 0
    ('28.07.2018' 3 4 2017 6 0
    ('29.07.2018' 3 4 2017 7 0
    ('30.07.2018' 3 4 2017 8 0
    ('31.07.2018' 3 4 2017 9 0
    ('01.08.2018' 3 4 2017 10 0
    ('02.08.2018' 3 4 2017 11 0
    ('03.08.2018' 3 4 2017 12 0
    ('04.08.2018' 3 4 2017 13 0
    ('05.08.2018' 3 4 2017 14 0
    ('06.08.2018' 3 4 2017 15 0
    ('07.08.2018' 3 4 2017 16 0
    ('08.08.2018' 3 4 2017 17 0
    ('09.08.2018' 3 4 2017 18 0
    ('10.08.2018' 3 4 2017 19 0
    ('11.08.2018' 3 4 2017 20 0
    ('12.08.2018' 3 4 2017 21 0
    ('13.08.2018' 3 4 2017 22 0
    ('14.08.2018' 3 4 2017 23 0
    ('15.08.2018' 3 4 2017 24 0
    ('16.08.2018' 3 4 2017 25 0
    ('17.08.2018' 3 4 2017 26 0
    ('18.08.2018' 3 4 2017 27 1
    ('19.08.2018' 3 4 2017 28 1
    ('20.08.2018' 3 4 2017 29 1
    ('21.08.2018' 3 4 2017 30 1
    ('22.08.2018' 3 4 2017 31 1
    ('23.08.2018' 3 4 2017 32 1
    ('24.08.2018' 3 4 2017 33 1

    So the group for which we search another groups marked as basis

    group which for basis didn't have action marked as no

    group  which for basis was action  before marked as before

    group  which for basis was action  After marked as after

    after we finished with 1+2+2017 group, sql takes another group and work by algorythm

    So recombination of group can my very many

    So is it clear my problem? 


    • Edited by merzavazeh Wednesday, July 25, 2018 12:46 PM
    Wednesday, July 25, 2018 12:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I'll try

    suppose there is group

    dt x1 x2 x3 sale action
    ('23.07.2018' 1 2 2017 1 0
    ('24.07.2018' 1 2 2017 2 0
    ('25.07.2018' 1 2 2017 3 0
    ('26.07.2018' 1 2 2017 4 0
    ('27.07.2018' 1 2 2017 5 0
    ('28.07.2018' 1 2 2017 6 0
    ('29.07.2018' 1 2 2017 7 0
    ('30.07.2018' 1 2 2017 8 0
    ('31.07.2018' 1 2 2017 9 0
    ('01.08.2018' 1 2 2017 10 0
    ('02.08.2018' 1 2 2017 11 0
    ('03.08.2018' 1 2 2017 12 1
    ('04.08.2018' 1 2 2017 13 1
    ('05.08.2018' 1 2 2017 14 1
    ('06.08.2018' 1 2 2017 15 1
    ('07.08.2018' 1 2 2017 16 1
    ('08.08.2018' 1 2 2017 17 1
    ('09.08.2018' 1 2 2017 18 1
    ('10.08.2018' 1 2 2017 19 1
    ('11.08.2018' 1 2 2017 20 1
    ('12.08.2018' 1 2 2017 21 1
    ('13.08.2018' 1 2 2017 22 1
    ('14.08.2018' 1 2 2017 23 1
    ('15.08.2018' 1 2 2017 24 1
    ('16.08.2018' 1 2 2017 25 1
    ('17.08.2018' 1 2 2017 26 1
    ('18.08.2018' 1 2 2017 27 0
    ('19.08.2018' 1 2 2017 28 0
    ('20.08.2018' 1 2 2017 29 0
    ('21.08.2018' 1 2 2017 30 0
    ('22.08.2018' 1 2 2017 31 0
    ('23.08.2018' 1 2 2017 32 0
    ('24.08.2018' 1 2 2017 33 0
    ('25.08.2018' 1 2 2017 34 0

    this group (1+2+2017)has data from 23.7-25.08 right? So action for this  group  was from 3.8-17.8

    1 is action.

    I must find for this group 1+2+2017 these group, for which there is at least one conditon

    So sql finds , were  in this time period 23.7-25.08 groups which didn't have action

    or it was action before action for 1+2+2017 

    or it was action after action for 1+2+2017 group

    Do you understand?:))

     sql found that  for this group  in this period was next situation

    group  2+3+2017 didn't have action while 1+2+2017 was action

    cause by action for 2+3+2017 only zero

    ('23.07.2018' 2 3 2017 1 0
    ('24.07.2018' 2 3 2017 2 0
    ('25.07.2018' 2 3 2017 3 0
    ('26.07.2018' 2 3 2017 4 0
    ('27.07.2018' 2 3 2017 5 0
    ('28.07.2018' 2 3 2017 6 0
    ('29.07.2018' 2 3 2017 7 0
    ('30.07.2018' 2 3 2017 8 0
    ('31.07.2018' 2 3 2017 9 0
    ('01.08.2018' 2 3 2017 10 0
    ('02.08.2018' 2 3 2017 11 0
    ('03.08.2018' 2 3 2017 12 0
    ('04.08.2018' 2 3 2017 13 0
    ('05.08.2018' 2 3 2017 14 0
    ('06.08.2018' 2 3 2017 15 0
    ('07.08.2018' 2 3 2017 16 0
    ('08.08.2018' 2 3 2017 17 0
    ('09.08.2018' 2 3 2017 18 0
    ('10.08.2018' 2 3 2017 19 0
    ('11.08.2018' 2 3 2017 20 0
    ('12.08.2018' 2 3 2017 21 0
    ('13.08.2018' 2 3 2017 22 0
    ('14.08.2018' 2 3 2017 23 0
    ('15.08.2018' 2 3 2017 24 0
    ('16.08.2018' 2 3 2017 25 0
    ('17.08.2018' 2 3 2017 26 0
    ('18.08.2018' 2 3 2017 27 0
    ('19.08.2018' 2 3 2017 28 0
    ('20.08.2018' 2 3 2017 29 0
    ('21.08.2018' 2 3 2017 30 0
    ('22.08.2018' 2 3 2017 31 0
    ('23.08.2018' 2 3 2017 32 0
    ('24.08.2018' 2 3 2017 33 0
    ('25.08.2018' 2 3 2017 34 0

    and for group

    3+4+2017 action was before action for 1+2+2017

    cause we have ones to 03/08 and then began action for 1+2+2017

    ('23.07.2018' 3 4 2017 1 1
    ('24.07.2018' 3 4 2017 2 1
    ('25.07.2018' 3 4 2017 3 1
    ('26.07.2018' 3 4 2017 4 1
    ('27.07.2018' 3 4 2017 5 1
    ('28.07.2018' 3 4 2017 6 1
    ('29.07.2018' 3 4 2017 7 1
    ('30.07.2018' 3 4 2017 8 1
    ('31.07.2018' 3 4 2017 9 1
    ('01.08.2018' 3 4 2017 10 1
    ('02.08.2018' 3 4 2017 11 0
    ('03.08.2018' 3 4 2017 12 0
    ('04.08.2018' 3 4 2017 13 0
    ('05.08.2018' 3 4 2017 14 0
    ('06.08.2018' 3 4 2017 15 0
    ('07.08.2018' 3 4 2017 16 0
    ('08.08.2018' 3 4 2017 17 0
    ('09.08.2018' 3 4 2017 18 0
    ('10.08.2018' 3 4 2017 19 0
    ('11.08.2018' 3 4 2017 20 0
    ('12.08.2018' 3 4 2017 21 0
    ('13.08.2018' 3 4 2017 22 0
    ('14.08.2018' 3 4 2017 23 0
    ('15.08.2018' 3 4 2017 24 0
    ('16.08.2018' 3 4 2017 25 0
    ('17.08.2018' 3 4 2017 26 0
    ('18.08.2018' 3 4 2017 27 0
    ('19.08.2018' 3 4 2017 28 0
    ('20.08.2018' 3 4 2017 29 0
    ('21.08.2018' 3 4 2017 30 0
    ('22.08.2018' 3 4 2017 31 0
    ('23.08.2018' 3 4 2017 32 0
    ('24.08.2018' 3 4 2017 33 0

    there is condition when  group was action after action 1+2+2017. I.E ones goes after 17.08

    ('23.07.2018' 4 5 2017 1 0
    ('24.07.2018' 3 4 2017 2 0
    ('25.07.2018' 3 4 2017 3 0
    ('26.07.2018' 3 4 2017 4 0
    ('27.07.2018' 3 4 2017 5 0
    ('28.07.2018' 3 4 2017 6 0
    ('29.07.2018' 3 4 2017 7 0
    ('30.07.2018' 3 4 2017 8 0
    ('31.07.2018' 3 4 2017 9 0
    ('01.08.2018' 3 4 2017 10 0
    ('02.08.2018' 3 4 2017 11 0
    ('03.08.2018' 3 4 2017 12 0
    ('04.08.2018' 3 4 2017 13 0
    ('05.08.2018' 3 4 2017 14 0
    ('06.08.2018' 3 4 2017 15 0
    ('07.08.2018' 3 4 2017 16 0
    ('08.08.2018' 3 4 2017 17 0
    ('09.08.2018' 3 4 2017 18 0
    ('10.08.2018' 3 4 2017 19 0
    ('11.08.2018' 3 4 2017 20 0
    ('12.08.2018' 3 4 2017 21 0
    ('13.08.2018' 3 4 2017 22 0
    ('14.08.2018' 3 4 2017 23 0
    ('15.08.2018' 3 4 2017 24 0
    ('16.08.2018' 3 4 2017 25 0
    ('17.08.2018' 3 4 2017 26 0
    ('18.08.2018' 3 4 2017 27 1
    ('19.08.2018' 3 4 2017 28 1
    ('20.08.2018' 3 4 2017 29 1
    ('21.08.2018' 3 4 2017 30 1
    ('22.08.2018' 3 4 2017 31 1
    ('23.08.2018' 3 4 2017 32 1
    ('24.08.2018' 3 4 2017 33 1

    So the group for which we search another groups marked as basis

    group which for basis didn't have action marked as no

    group  which for basis was action  before marked as before

    group  which for basis was action  After marked as after

    after we finished with 1+2+2017 group, sql takes another group and work by algorythm

    So recombination of group can my very many

    So is it clear my problem? 


    Ok so user will pass group values as input?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 25, 2018 12:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    in sql table, there are all group

    x1,x,2,x3 

    1+2+2017

    3+4+2017

    ...

    500+1000+2017

    sql takes the first group, work with it, and after working, sql gets the next group and use this algorythm.

    am i clear understand?:)


    Wednesday, July 25, 2018 1:34 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Sounds like this

    set dateformat dmy
go

Declare @t table 
(Dt date,
  x1 int,
  x2 int,
  x3 int,
  sale int,
  action int,
  typegroup varchar(20));
insert into @t values
('23.07.2018',1,2,2017,1,0,''),
('24.07.2018',1,2,2017,2,0,''),
('25.07.2018',1,2,2017,3,0,''),
('26.07.2018',1,2,2017,4,0,''),
('27.07.2018',1,2,2017,5,0,''),
('28.07.2018',1,2,2017,6,0,''),
('29.07.2018',1,2,2017,7,0,''),
('30.07.2018',1,2,2017,8,0,''),
('31.07.2018',1,2,2017,9,0,''),
('01.08.2018',1,2,2017,10,0,''),
('02.08.2018',1,2,2017,11,0,''),
('03.08.2018',1,2,2017,12,1,''),
('04.08.2018',1,2,2017,13,1,''),
('05.08.2018',1,2,2017,14,1,''),
('06.08.2018',1,2,2017,15,1,''),
('07.08.2018',1,2,2017,16,1,''),
('08.08.2018',1,2,2017,17,1,''),
('09.08.2018',1,2,2017,18,1,''),
('10.08.2018',1,2,2017,19,1,''),
('11.08.2018',1,2,2017,20,1,''),
('12.08.2018',1,2,2017,21,1,''),
('13.08.2018',1,2,2017,22,1,''),
('14.08.2018',1,2,2017,23,1,''),
('15.08.2018',1,2,2017,24,1,''),
('16.08.2018',1,2,2017,25,1,''),
('17.08.2018',1,2,2017,26,1,''),
('18.08.2018',1,2,2017,27,0,''),
('19.08.2018',1,2,2017,28,0,''),
('20.08.2018',1,2,2017,29,0,''),
('21.08.2018',1,2,2017,30,0,''),
('22.08.2018',1,2,2017,31,0,''),
('23.08.2018',1,2,2017,32,0,''),
('24.08.2018',1,2,2017,33,0,''),
('25.08.2018',1,2,2017,34,0,''),
('23.07.2018',2,3,2017,1,0,''),
('24.07.2018',2,3,2017,2,0,''),
('25.07.2018',2,3,2017,3,0,''),
('26.07.2018',2,3,2017,4,0,''),
('27.07.2018',2,3,2017,5,0,''),
('28.07.2018',2,3,2017,6,0,''),
('29.07.2018',2,3,2017,7,0,''),
('30.07.2018',2,3,2017,8,0,''),
('31.07.2018',2,3,2017,9,0,''),
('01.08.2018',2,3,2017,10,0,''),
('02.08.2018',2,3,2017,11,0,''),
('03.08.2018',2,3,2017,12,0,''),
('04.08.2018',2,3,2017,13,0,''),
('05.08.2018',2,3,2017,14,0,''),
('06.08.2018',2,3,2017,15,0,''),
('07.08.2018',2,3,2017,16,0,''),
('08.08.2018',2,3,2017,17,0,''),
('09.08.2018',2,3,2017,18,0,''),
('10.08.2018',2,3,2017,19,0,''),
('11.08.2018',2,3,2017,20,0,''),
('12.08.2018',2,3,2017,21,0,''),
('13.08.2018',2,3,2017,22,0,''),
('14.08.2018',2,3,2017,23,0,''),
('15.08.2018',2,3,2017,24,0,''),
('16.08.2018',2,3,2017,25,0,''),
('17.08.2018',2,3,2017,26,0,''),
('18.08.2018',2,3,2017,27,0,''),
('19.08.2018',2,3,2017,28,0,''),
('20.08.2018',2,3,2017,29,0,''),
('21.08.2018',2,3,2017,30,0,''),
('22.08.2018',2,3,2017,31,0,''),
('23.08.2018',2,3,2017,32,0,''),
('24.08.2018',2,3,2017,33,0,''),
('25.08.2018',2,3,2017,34,0,''),
('23.07.2018',3,4,2017,1,1,''),
('24.07.2018',3,4,2017,2,1,''),
('25.07.2018',3,4,2017,3,1,''),
('26.07.2018',3,4,2017,4,1,''),
('27.07.2018',3,4,2017,5,1,''),
('28.07.2018',3,4,2017,6,1,''),
('29.07.2018',3,4,2017,7,1,''),
('30.07.2018',3,4,2017,8,1,''),
('31.07.2018',3,4,2017,9,1,''),
('01.08.2018',3,4,2017,10,1,''),
('02.08.2018',3,4,2017,11,0,''),
('03.08.2018',3,4,2017,12,0,''),
('04.08.2018',3,4,2017,13,0,''),
('05.08.2018',3,4,2017,14,0,''),
('06.08.2018',3,4,2017,15,0,''),
('07.08.2018',3,4,2017,16,0,''),
('08.08.2018',3,4,2017,17,0,''),
('09.08.2018',3,4,2017,18,0,''),
('10.08.2018',3,4,2017,19,0,''),
('11.08.2018',3,4,2017,20,0,''),
('12.08.2018',3,4,2017,21,0,''),
('13.08.2018',3,4,2017,22,0,''),
('14.08.2018',3,4,2017,23,0,''),
('15.08.2018',3,4,2017,24,0,''),
('16.08.2018',3,4,2017,25,0,''),
('17.08.2018',3,4,2017,26,0,''),
('18.08.2018',3,4,2017,27,1,''),
('19.08.2018',3,4,2017,28,1,''),
('20.08.2018',3,4,2017,29,1,''),
('21.08.2018',3,4,2017,30,1,''),
('22.08.2018',3,4,2017,31,1,''),
('23.08.2018',3,4,2017,32,1,''),
('24.08.2018',3,4,2017,33,1,'');

declare @x1 int,
  @x2 int,
  @x3 int,@mindt date,@maxdt date

--pass any group values here
  select @x1 = 1, @x2 = 2,@x3= 2017
  
  
  Select @mindt = min(Dt), @maxdt = max(Dt)
from @t
where x1 = @x1
and  x2 = @x2
and x3 = @x3
and action =1
  
  update r
  set typegroup= type
  from (select *,
  case 				when x1=@x1 and x2 = @x2 and x3 = @x3 then 'basis'
                when  action = 1 and max(Dt) over (partition by nxt) > coalesce(@maxdt,'99991231') then 'after'
                when  action = 1 and min(Dt) over (partition by nxt) < coalesce(@mindt,'19000101') then 'before'

				end as type
				from @t t
				outer apply
  (
Select min(Dt) as nxt
from @t
where x1 = t.x1
and  x2 = t.x2
and x3 = t.x3
and action <> t.action
and Dt > t.Dt
)t1)r
  

select *
from @t
order by x1,x2,x3,sale

/*
Output
-----------------------------------------------------------
Dt	x1	x2	x3	sale	action	typegroup
--------------------------------------------------------------------------
2018-07-23	1	2	2017	1	0	basis
2018-07-24	1	2	2017	2	0	basis
2018-07-25	1	2	2017	3	0	basis
2018-07-26	1	2	2017	4	0	basis
2018-07-27	1	2	2017	5	0	basis
2018-07-28	1	2	2017	6	0	basis
2018-07-29	1	2	2017	7	0	basis
2018-07-30	1	2	2017	8	0	basis
2018-07-31	1	2	2017	9	0	basis
2018-08-01	1	2	2017	10	0	basis
2018-08-02	1	2	2017	11	0	basis
2018-08-03	1	2	2017	12	1	basis
2018-08-04	1	2	2017	13	1	basis
2018-08-05	1	2	2017	14	1	basis
2018-08-06	1	2	2017	15	1	basis
2018-08-07	1	2	2017	16	1	basis
2018-08-08	1	2	2017	17	1	basis
2018-08-09	1	2	2017	18	1	basis
2018-08-10	1	2	2017	19	1	basis
2018-08-11	1	2	2017	20	1	basis
2018-08-12	1	2	2017	21	1	basis
2018-08-13	1	2	2017	22	1	basis
2018-08-14	1	2	2017	23	1	basis
2018-08-15	1	2	2017	24	1	basis
2018-08-16	1	2	2017	25	1	basis
2018-08-17	1	2	2017	26	1	basis
2018-08-18	1	2	2017	27	0	basis
2018-08-19	1	2	2017	28	0	basis
2018-08-20	1	2	2017	29	0	basis
2018-08-21	1	2	2017	30	0	basis
2018-08-22	1	2	2017	31	0	basis
2018-08-23	1	2	2017	32	0	basis
2018-08-24	1	2	2017	33	0	basis
2018-08-25	1	2	2017	34	0	basis
2018-07-23	2	3	2017	1	0	NULL
2018-07-24	2	3	2017	2	0	NULL
2018-07-25	2	3	2017	3	0	NULL
2018-07-26	2	3	2017	4	0	NULL
2018-07-27	2	3	2017	5	0	NULL
2018-07-28	2	3	2017	6	0	NULL
2018-07-29	2	3	2017	7	0	NULL
2018-07-30	2	3	2017	8	0	NULL
2018-07-31	2	3	2017	9	0	NULL
2018-08-01	2	3	2017	10	0	NULL
2018-08-02	2	3	2017	11	0	NULL
2018-08-03	2	3	2017	12	0	NULL
2018-08-04	2	3	2017	13	0	NULL
2018-08-05	2	3	2017	14	0	NULL
2018-08-06	2	3	2017	15	0	NULL
2018-08-07	2	3	2017	16	0	NULL
2018-08-08	2	3	2017	17	0	NULL
2018-08-09	2	3	2017	18	0	NULL
2018-08-10	2	3	2017	19	0	NULL
2018-08-11	2	3	2017	20	0	NULL
2018-08-12	2	3	2017	21	0	NULL
2018-08-13	2	3	2017	22	0	NULL
2018-08-14	2	3	2017	23	0	NULL
2018-08-15	2	3	2017	24	0	NULL
2018-08-16	2	3	2017	25	0	NULL
2018-08-17	2	3	2017	26	0	NULL
2018-08-18	2	3	2017	27	0	NULL
2018-08-19	2	3	2017	28	0	NULL
2018-08-20	2	3	2017	29	0	NULL
2018-08-21	2	3	2017	30	0	NULL
2018-08-22	2	3	2017	31	0	NULL
2018-08-23	2	3	2017	32	0	NULL
2018-08-24	2	3	2017	33	0	NULL
2018-08-25	2	3	2017	34	0	NULL
2018-07-23	3	4	2017	1	1	before
2018-07-24	3	4	2017	2	1	before
2018-07-25	3	4	2017	3	1	before
2018-07-26	3	4	2017	4	1	before
2018-07-27	3	4	2017	5	1	before
2018-07-28	3	4	2017	6	1	before
2018-07-29	3	4	2017	7	1	before
2018-07-30	3	4	2017	8	1	before
2018-07-31	3	4	2017	9	1	before
2018-08-01	3	4	2017	10	1	before
2018-08-02	3	4	2017	11	0	NULL
2018-08-03	3	4	2017	12	0	NULL
2018-08-04	3	4	2017	13	0	NULL
2018-08-05	3	4	2017	14	0	NULL
2018-08-06	3	4	2017	15	0	NULL
2018-08-07	3	4	2017	16	0	NULL
2018-08-08	3	4	2017	17	0	NULL
2018-08-09	3	4	2017	18	0	NULL
2018-08-10	3	4	2017	19	0	NULL
2018-08-11	3	4	2017	20	0	NULL
2018-08-12	3	4	2017	21	0	NULL
2018-08-13	3	4	2017	22	0	NULL
2018-08-14	3	4	2017	23	0	NULL
2018-08-15	3	4	2017	24	0	NULL
2018-08-16	3	4	2017	25	0	NULL
2018-08-17	3	4	2017	26	0	NULL
2018-08-18	3	4	2017	27	1	after
2018-08-19	3	4	2017	28	1	after
2018-08-20	3	4	2017	29	1	after
2018-08-21	3	4	2017	30	1	after
2018-08-22	3	4	2017	31	1	after
2018-08-23	3	4	2017	32	1	after
2018-08-24	3	4	2017	33	1	after

*/

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by merzavazeh Sunday, July 29, 2018 8:37 AM
    Wednesday, July 25, 2018 3:39 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Visakh16,  your solution is good , but one question

    how to do, that for group 

    3 4 2017 -before

    as you can see where action=0, is marked as null. But all group 3+4+2017 must be marked as before 

    even  in this group there is action=0, so as output

    23.07.2018 3 4 2017 1 1 before
    24.07.2018 3 4 2017 2 1 before
    25.07.2018 3 4 2017 3 1 before
    26.07.2018 3 4 2017 4 1 before
    27.07.2018 3 4 2017 5 1 before
    28.07.2018 3 4 2017 6 1 before
    29.07.2018 3 4 2017 7 1 before
    30.07.2018 3 4 2017 8 1 before
    31.07.2018 3 4 2017 9 1 before
    01.08.2018 3 4 2017 10 1 before
    02.08.2018 3 4 2017 11 0 before
    03.08.2018 3 4 2017 12 0 before
    04.08.2018 3 4 2017 13 0 before
    05.08.2018 3 4 2017 14 0 before
    06.08.2018 3 4 2017 15 0 before
    07.08.2018 3 4 2017 16 0 before
    08.08.2018 3 4 2017 17 0 before
    09.08.2018 3 4 2017 18 0 before
    10.08.2018 3 4 2017 19 0 before
    11.08.2018 3 4 2017 20 0 before
    12.08.2018 3 4 2017 21 0 before
    13.08.2018 3 4 2017 22 0 before
    14.08.2018 3 4 2017 23 0 before
    15.08.2018 3 4 2017 24 0 before
    16.08.2018 3 4 2017 25 0 before
    17.08.2018 3 4 2017 26 0 before


    or if action was afterm then

    23.07.2018 3 4 2017 1 1 after
    24.07.2018 3 4 2017 2 1 after
    25.07.2018 3 4 2017 3 1 after
    26.07.2018 3 4 2017 4 1 after
    27.07.2018 3 4 2017 5 1 after
    28.07.2018 3 4 2017 6 1 after
    29.07.2018 3 4 2017 7 1 after
    30.07.2018 3 4 2017 8 1 after
    31.07.2018 3 4 2017 9 1 after
    01.08.2018 3 4 2017 10 1 after
    02.08.2018 3 4 2017 11 0 after
    03.08.2018 3 4 2017 12 0 after
    04.08.2018 3 4 2017 13 0 after
    05.08.2018 3 4 2017 14 0 after
    06.08.2018 3 4 2017 15 0 after
    07.08.2018 3 4 2017 16 0 after
    08.08.2018 3 4 2017 17 0 after
    09.08.2018 3 4 2017 18 0 after
    10.08.2018 3 4 2017 19 0 after
    11.08.2018 3 4 2017 20 0 after
    12.08.2018 3 4 2017 21 0 after
    13.08.2018 3 4 2017 22 0 after
    14.08.2018 3 4 2017 23 0 after
    15.08.2018 3 4 2017 24 0 after
    16.08.2018 3 4 2017 25 0 after
    17.08.2018 3 4 2017 26 0 after
    thank you for your answer.




    • Edited by merzavazeh Saturday, July 28, 2018 4:00 PM
    Saturday, July 28, 2018 3:59 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    do you mean this?

    set dateformat dmy
go

Declare @t table 
(Dt date,
  x1 int,
  x2 int,
  x3 int,
  sale int,
  action int,
  typegroup varchar(20));
insert into @t values
('23.07.2018',1,2,2017,1,0,''),
('24.07.2018',1,2,2017,2,0,''),
('25.07.2018',1,2,2017,3,0,''),
('26.07.2018',1,2,2017,4,0,''),
('27.07.2018',1,2,2017,5,0,''),
('28.07.2018',1,2,2017,6,0,''),
('29.07.2018',1,2,2017,7,0,''),
('30.07.2018',1,2,2017,8,0,''),
('31.07.2018',1,2,2017,9,0,''),
('01.08.2018',1,2,2017,10,0,''),
('02.08.2018',1,2,2017,11,0,''),
('03.08.2018',1,2,2017,12,1,''),
('04.08.2018',1,2,2017,13,1,''),
('05.08.2018',1,2,2017,14,1,''),
('06.08.2018',1,2,2017,15,1,''),
('07.08.2018',1,2,2017,16,1,''),
('08.08.2018',1,2,2017,17,1,''),
('09.08.2018',1,2,2017,18,1,''),
('10.08.2018',1,2,2017,19,1,''),
('11.08.2018',1,2,2017,20,1,''),
('12.08.2018',1,2,2017,21,1,''),
('13.08.2018',1,2,2017,22,1,''),
('14.08.2018',1,2,2017,23,1,''),
('15.08.2018',1,2,2017,24,1,''),
('16.08.2018',1,2,2017,25,1,''),
('17.08.2018',1,2,2017,26,1,''),
('18.08.2018',1,2,2017,27,0,''),
('19.08.2018',1,2,2017,28,0,''),
('20.08.2018',1,2,2017,29,0,''),
('21.08.2018',1,2,2017,30,0,''),
('22.08.2018',1,2,2017,31,0,''),
('23.08.2018',1,2,2017,32,0,''),
('24.08.2018',1,2,2017,33,0,''),
('25.08.2018',1,2,2017,34,0,''),
('23.07.2018',2,3,2017,1,0,''),
('24.07.2018',2,3,2017,2,0,''),
('25.07.2018',2,3,2017,3,0,''),
('26.07.2018',2,3,2017,4,0,''),
('27.07.2018',2,3,2017,5,0,''),
('28.07.2018',2,3,2017,6,0,''),
('29.07.2018',2,3,2017,7,0,''),
('30.07.2018',2,3,2017,8,0,''),
('31.07.2018',2,3,2017,9,0,''),
('01.08.2018',2,3,2017,10,0,''),
('02.08.2018',2,3,2017,11,0,''),
('03.08.2018',2,3,2017,12,0,''),
('04.08.2018',2,3,2017,13,0,''),
('05.08.2018',2,3,2017,14,0,''),
('06.08.2018',2,3,2017,15,0,''),
('07.08.2018',2,3,2017,16,0,''),
('08.08.2018',2,3,2017,17,0,''),
('09.08.2018',2,3,2017,18,0,''),
('10.08.2018',2,3,2017,19,0,''),
('11.08.2018',2,3,2017,20,0,''),
('12.08.2018',2,3,2017,21,0,''),
('13.08.2018',2,3,2017,22,0,''),
('14.08.2018',2,3,2017,23,0,''),
('15.08.2018',2,3,2017,24,0,''),
('16.08.2018',2,3,2017,25,0,''),
('17.08.2018',2,3,2017,26,0,''),
('18.08.2018',2,3,2017,27,0,''),
('19.08.2018',2,3,2017,28,0,''),
('20.08.2018',2,3,2017,29,0,''),
('21.08.2018',2,3,2017,30,0,''),
('22.08.2018',2,3,2017,31,0,''),
('23.08.2018',2,3,2017,32,0,''),
('24.08.2018',2,3,2017,33,0,''),
('25.08.2018',2,3,2017,34,0,''),
('23.07.2018',3,4,2017,1,1,''),
('24.07.2018',3,4,2017,2,1,''),
('25.07.2018',3,4,2017,3,1,''),
('26.07.2018',3,4,2017,4,1,''),
('27.07.2018',3,4,2017,5,1,''),
('28.07.2018',3,4,2017,6,1,''),
('29.07.2018',3,4,2017,7,1,''),
('30.07.2018',3,4,2017,8,1,''),
('31.07.2018',3,4,2017,9,1,''),
('01.08.2018',3,4,2017,10,1,''),
('02.08.2018',3,4,2017,11,0,''),
('03.08.2018',3,4,2017,12,0,''),
('04.08.2018',3,4,2017,13,0,''),
('05.08.2018',3,4,2017,14,0,''),
('06.08.2018',3,4,2017,15,0,''),
('07.08.2018',3,4,2017,16,0,''),
('08.08.2018',3,4,2017,17,0,''),
('09.08.2018',3,4,2017,18,0,''),
('10.08.2018',3,4,2017,19,0,''),
('11.08.2018',3,4,2017,20,0,''),
('12.08.2018',3,4,2017,21,0,''),
('13.08.2018',3,4,2017,22,0,''),
('14.08.2018',3,4,2017,23,0,''),
('15.08.2018',3,4,2017,24,0,''),
('16.08.2018',3,4,2017,25,0,''),
('17.08.2018',3,4,2017,26,0,''),
('18.08.2018',3,4,2017,27,1,''),
('19.08.2018',3,4,2017,28,1,''),
('20.08.2018',3,4,2017,29,1,''),
('21.08.2018',3,4,2017,30,1,''),
('22.08.2018',3,4,2017,31,1,''),
('23.08.2018',3,4,2017,32,1,''),
('24.08.2018',3,4,2017,33,1,'');

declare @x1 int,
  @x2 int,
  @x3 int,@mindt date,@maxdt date

--pass any group values here
  select @x1 = 1, @x2 = 2,@x3= 2017
  
  
  Select @mindt = min(Dt), @maxdt = max(Dt)
from @t
where x1 = @x1
and  x2 = @x2
and x3 = @x3
and action =1
  
  update r
  set typegroup= type
  from (select *,
  case 				when x1=@x1 and x2 = @x2 and x3 = @x3 then 'basis'
                when   max(Dt) over (partition by nxt) > coalesce(@maxdt,'99991231') then 'after'
                when   min(Dt) over (partition by nxt) < coalesce(@mindt,'19000101') then 'before'

				end as type
				from @t t
				outer apply
  (
Select min(Dt) as nxt
from @t
where x1 = t.x1
and  x2 = t.x2
and x3 = t.x3
and action <> t.action
and Dt > t.Dt
)t1)r
  

select *
from @t
order by x1,x2,x3,sale


/*
Output
---------------------------------------------------------
Dt	x1	x2	x3	sale	action	typegroup
--------------------------------------------------------------------------
2018-07-23	1	2	2017	1	0	basis
2018-07-24	1	2	2017	2	0	basis
2018-07-25	1	2	2017	3	0	basis
2018-07-26	1	2	2017	4	0	basis
2018-07-27	1	2	2017	5	0	basis
2018-07-28	1	2	2017	6	0	basis
2018-07-29	1	2	2017	7	0	basis
2018-07-30	1	2	2017	8	0	basis
2018-07-31	1	2	2017	9	0	basis
2018-08-01	1	2	2017	10	0	basis
2018-08-02	1	2	2017	11	0	basis
2018-08-03	1	2	2017	12	1	basis
2018-08-04	1	2	2017	13	1	basis
2018-08-05	1	2	2017	14	1	basis
2018-08-06	1	2	2017	15	1	basis
2018-08-07	1	2	2017	16	1	basis
2018-08-08	1	2	2017	17	1	basis
2018-08-09	1	2	2017	18	1	basis
2018-08-10	1	2	2017	19	1	basis
2018-08-11	1	2	2017	20	1	basis
2018-08-12	1	2	2017	21	1	basis
2018-08-13	1	2	2017	22	1	basis
2018-08-14	1	2	2017	23	1	basis
2018-08-15	1	2	2017	24	1	basis
2018-08-16	1	2	2017	25	1	basis
2018-08-17	1	2	2017	26	1	basis
2018-08-18	1	2	2017	27	0	basis
2018-08-19	1	2	2017	28	0	basis
2018-08-20	1	2	2017	29	0	basis
2018-08-21	1	2	2017	30	0	basis
2018-08-22	1	2	2017	31	0	basis
2018-08-23	1	2	2017	32	0	basis
2018-08-24	1	2	2017	33	0	basis
2018-08-25	1	2	2017	34	0	basis
2018-07-23	2	3	2017	1	0	after
2018-07-24	2	3	2017	2	0	after
2018-07-25	2	3	2017	3	0	after
2018-07-26	2	3	2017	4	0	after
2018-07-27	2	3	2017	5	0	after
2018-07-28	2	3	2017	6	0	after
2018-07-29	2	3	2017	7	0	after
2018-07-30	2	3	2017	8	0	after
2018-07-31	2	3	2017	9	0	after
2018-08-01	2	3	2017	10	0	after
2018-08-02	2	3	2017	11	0	after
2018-08-03	2	3	2017	12	0	after
2018-08-04	2	3	2017	13	0	after
2018-08-05	2	3	2017	14	0	after
2018-08-06	2	3	2017	15	0	after
2018-08-07	2	3	2017	16	0	after
2018-08-08	2	3	2017	17	0	after
2018-08-09	2	3	2017	18	0	after
2018-08-10	2	3	2017	19	0	after
2018-08-11	2	3	2017	20	0	after
2018-08-12	2	3	2017	21	0	after
2018-08-13	2	3	2017	22	0	after
2018-08-14	2	3	2017	23	0	after
2018-08-15	2	3	2017	24	0	after
2018-08-16	2	3	2017	25	0	after
2018-08-17	2	3	2017	26	0	after
2018-08-18	2	3	2017	27	0	after
2018-08-19	2	3	2017	28	0	after
2018-08-20	2	3	2017	29	0	after
2018-08-21	2	3	2017	30	0	after
2018-08-22	2	3	2017	31	0	after
2018-08-23	2	3	2017	32	0	after
2018-08-24	2	3	2017	33	0	after
2018-08-25	2	3	2017	34	0	after
2018-07-23	3	4	2017	1	1	before
2018-07-24	3	4	2017	2	1	before
2018-07-25	3	4	2017	3	1	before
2018-07-26	3	4	2017	4	1	before
2018-07-27	3	4	2017	5	1	before
2018-07-28	3	4	2017	6	1	before
2018-07-29	3	4	2017	7	1	before
2018-07-30	3	4	2017	8	1	before
2018-07-31	3	4	2017	9	1	before
2018-08-01	3	4	2017	10	1	before
2018-08-02	3	4	2017	11	0	before
2018-08-03	3	4	2017	12	0	before
2018-08-04	3	4	2017	13	0	before
2018-08-05	3	4	2017	14	0	before
2018-08-06	3	4	2017	15	0	before
2018-08-07	3	4	2017	16	0	before
2018-08-08	3	4	2017	17	0	before
2018-08-09	3	4	2017	18	0	before
2018-08-10	3	4	2017	19	0	before
2018-08-11	3	4	2017	20	0	before
2018-08-12	3	4	2017	21	0	before
2018-08-13	3	4	2017	22	0	before
2018-08-14	3	4	2017	23	0	before
2018-08-15	3	4	2017	24	0	before
2018-08-16	3	4	2017	25	0	before
2018-08-17	3	4	2017	26	0	before
2018-08-18	3	4	2017	27	1	after
2018-08-19	3	4	2017	28	1	after
2018-08-20	3	4	2017	29	1	after
2018-08-21	3	4	2017	30	1	after
2018-08-22	3	4	2017	31	1	after
2018-08-23	3	4	2017	32	1	after
2018-08-24	3	4	2017	33	1	after

*/

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, July 28, 2018 6:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Yes, thank you

    But

    2018-08-13 1 2 2017 22 1 basis
    2018-08-14 1 2 2017 23 1 basis
    2018-08-15 1 2 2017 24 1 basis
    2018-08-16 1 2 2017 25 1 basis
    2018-08-17 1 2 2017 26 1 basis
    2018-08-18 1 2 2017 27 0 basis
    2018-08-19 1 2 2017 28 0 basis
    2018-08-20 1 2 2017 29 0 basis
    2018-08-21 1 2 2017 30 0 basis
    2018-08-22 1 2 2017 31 0 basis
    2018-08-23 1 2 2017 32 0 basis
    2018-08-24 1 2 2017 33 0 basis
    2018-08-25 1 2 2017 34 0 basis
    2018-07-23 2 3 2017 1 0 after
    2018-07-24 2 3 2017 2 0 after
    2018-07-25 2 3 2017 3 0 after
    2018-07-26 2 3 2017 4 0 after
    2018-07-27 2 3 2017 5 0 after
    2018-07-28 2 3 2017 6 0 after
    2018-07-29 2 3 2017 7 0 after
    2018-07-30 2 3 2017 8 0 after
    2018-07-31 2 3 2017 9 0 after
    2018-08-01 2 3 2017 10 0 after
    2018-08-02 2 3 2017 11 0 after
    2018-08-03 2 3 2017 12 0 after
    2018-08-04 2 3 2017 13 0 after
    2018-08-05 2 3 2017 14 0 after
    2018-08-06 2 3 2017 15 0 after
    2018-08-07 2 3 2017 16 0 after
    2018-08-08 2 3 2017 17 0 after
    2018-08-09 2 3 2017 18 0 after
    2018-08-10 2 3 2017 19 0 after
    2018-08-11 2 3 2017 20 0 after
    2018-08-12 2 3 2017 21 0 after
    2018-08-13 2 3 2017 22 0 after
    2018-08-14 2 3 2017 23 0 after
    2018-08-15 2 3 2017 24 0 after
    2018-08-16 2 3 2017 25 0 after
    2018-08-17 2 3 2017 26 0 after
    2018-08-18 2 3 2017 27 0 after
    2018-08-19 2 3 2017 28 0 after
    2018-08-20 2 3 2017 29 0 after
    2018-08-21 2 3 2017 30 0 after
    2018-08-22 2 3 2017 31 0 after
    2018-08-23 2 3 2017 32 0 after
    2018-08-24 2 3 2017 33 0 after
    2018-08-25 2 3 2017 34 0 after



    group 2+3+2017 it is group where there not action. As you can see, for this group by action column 

    only zero. Why it marked as after. it is must be marked as "NO"


    • Edited by merzavazeh Sunday, July 29, 2018 8:42 AM
    Sunday, July 29, 2018 8:41 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
    Yes, thank you

    But

    2018-08-13 1 2 2017 22 1 basis
    2018-08-14 1 2 2017 23 1 basis
    2018-08-15 1 2 2017 24 1 basis
    2018-08-16 1 2 2017 25 1 basis
    2018-08-17 1 2 2017 26 1 basis
    2018-08-18 1 2 2017 27 0 basis
    2018-08-19 1 2 2017 28 0 basis
    2018-08-20 1 2 2017 29 0 basis
    2018-08-21 1 2 2017 30 0 basis
    2018-08-22 1 2 2017 31 0 basis
    2018-08-23 1 2 2017 32 0 basis
    2018-08-24 1 2 2017 33 0 basis
    2018-08-25 1 2 2017 34 0 basis
    2018-07-23 2 3 2017 1 0 after
    2018-07-24 2 3 2017 2 0 after
    2018-07-25 2 3 2017 3 0 after
    2018-07-26 2 3 2017 4 0 after
    2018-07-27 2 3 2017 5 0 after
    2018-07-28 2 3 2017 6 0 after
    2018-07-29 2 3 2017 7 0 after
    2018-07-30 2 3 2017 8 0 after
    2018-07-31 2 3 2017 9 0 after
    2018-08-01 2 3 2017 10 0 after
    2018-08-02 2 3 2017 11 0 after
    2018-08-03 2 3 2017 12 0 after
    2018-08-04 2 3 2017 13 0 after
    2018-08-05 2 3 2017 14 0 after
    2018-08-06 2 3 2017 15 0 after
    2018-08-07 2 3 2017 16 0 after
    2018-08-08 2 3 2017 17 0 after
    2018-08-09 2 3 2017 18 0 after
    2018-08-10 2 3 2017 19 0 after
    2018-08-11 2 3 2017 20 0 after
    2018-08-12 2 3 2017 21 0 after
    2018-08-13 2 3 2017 22 0 after
    2018-08-14 2 3 2017 23 0 after
    2018-08-15 2 3 2017 24 0 after
    2018-08-16 2 3 2017 25 0 after
    2018-08-17 2 3 2017 26 0 after
    2018-08-18 2 3 2017 27 0 after
    2018-08-19 2 3 2017 28 0 after
    2018-08-20 2 3 2017 29 0 after
    2018-08-21 2 3 2017 30 0 after
    2018-08-22 2 3 2017 31 0 after
    2018-08-23 2 3 2017 32 0 after
    2018-08-24 2 3 2017 33 0 after
    2018-08-25 2 3 2017 34 0 after



    group 2+3+2017 it is group where there not action. As you can see, for this group by action column 

    only zero. Why it marked as after. it is must be marked as "NO"


    Oh ok

    didnt notice that

    here you go

    set dateformat dmy
go

Declare @t table 
(Dt date,
  x1 int,
  x2 int,
  x3 int,
  sale int,
  action int,
  typegroup varchar(20));
insert into @t values
('23.07.2018',1,2,2017,1,0,''),
('24.07.2018',1,2,2017,2,0,''),
('25.07.2018',1,2,2017,3,0,''),
('26.07.2018',1,2,2017,4,0,''),
('27.07.2018',1,2,2017,5,0,''),
('28.07.2018',1,2,2017,6,0,''),
('29.07.2018',1,2,2017,7,0,''),
('30.07.2018',1,2,2017,8,0,''),
('31.07.2018',1,2,2017,9,0,''),
('01.08.2018',1,2,2017,10,0,''),
('02.08.2018',1,2,2017,11,0,''),
('03.08.2018',1,2,2017,12,1,''),
('04.08.2018',1,2,2017,13,1,''),
('05.08.2018',1,2,2017,14,1,''),
('06.08.2018',1,2,2017,15,1,''),
('07.08.2018',1,2,2017,16,1,''),
('08.08.2018',1,2,2017,17,1,''),
('09.08.2018',1,2,2017,18,1,''),
('10.08.2018',1,2,2017,19,1,''),
('11.08.2018',1,2,2017,20,1,''),
('12.08.2018',1,2,2017,21,1,''),
('13.08.2018',1,2,2017,22,1,''),
('14.08.2018',1,2,2017,23,1,''),
('15.08.2018',1,2,2017,24,1,''),
('16.08.2018',1,2,2017,25,1,''),
('17.08.2018',1,2,2017,26,1,''),
('18.08.2018',1,2,2017,27,0,''),
('19.08.2018',1,2,2017,28,0,''),
('20.08.2018',1,2,2017,29,0,''),
('21.08.2018',1,2,2017,30,0,''),
('22.08.2018',1,2,2017,31,0,''),
('23.08.2018',1,2,2017,32,0,''),
('24.08.2018',1,2,2017,33,0,''),
('25.08.2018',1,2,2017,34,0,''),
('23.07.2018',2,3,2017,1,0,''),
('24.07.2018',2,3,2017,2,0,''),
('25.07.2018',2,3,2017,3,0,''),
('26.07.2018',2,3,2017,4,0,''),
('27.07.2018',2,3,2017,5,0,''),
('28.07.2018',2,3,2017,6,0,''),
('29.07.2018',2,3,2017,7,0,''),
('30.07.2018',2,3,2017,8,0,''),
('31.07.2018',2,3,2017,9,0,''),
('01.08.2018',2,3,2017,10,0,''),
('02.08.2018',2,3,2017,11,0,''),
('03.08.2018',2,3,2017,12,0,''),
('04.08.2018',2,3,2017,13,0,''),
('05.08.2018',2,3,2017,14,0,''),
('06.08.2018',2,3,2017,15,0,''),
('07.08.2018',2,3,2017,16,0,''),
('08.08.2018',2,3,2017,17,0,''),
('09.08.2018',2,3,2017,18,0,''),
('10.08.2018',2,3,2017,19,0,''),
('11.08.2018',2,3,2017,20,0,''),
('12.08.2018',2,3,2017,21,0,''),
('13.08.2018',2,3,2017,22,0,''),
('14.08.2018',2,3,2017,23,0,''),
('15.08.2018',2,3,2017,24,0,''),
('16.08.2018',2,3,2017,25,0,''),
('17.08.2018',2,3,2017,26,0,''),
('18.08.2018',2,3,2017,27,0,''),
('19.08.2018',2,3,2017,28,0,''),
('20.08.2018',2,3,2017,29,0,''),
('21.08.2018',2,3,2017,30,0,''),
('22.08.2018',2,3,2017,31,0,''),
('23.08.2018',2,3,2017,32,0,''),
('24.08.2018',2,3,2017,33,0,''),
('25.08.2018',2,3,2017,34,0,''),
('23.07.2018',3,4,2017,1,1,''),
('24.07.2018',3,4,2017,2,1,''),
('25.07.2018',3,4,2017,3,1,''),
('26.07.2018',3,4,2017,4,1,''),
('27.07.2018',3,4,2017,5,1,''),
('28.07.2018',3,4,2017,6,1,''),
('29.07.2018',3,4,2017,7,1,''),
('30.07.2018',3,4,2017,8,1,''),
('31.07.2018',3,4,2017,9,1,''),
('01.08.2018',3,4,2017,10,1,''),
('02.08.2018',3,4,2017,11,0,''),
('03.08.2018',3,4,2017,12,0,''),
('04.08.2018',3,4,2017,13,0,''),
('05.08.2018',3,4,2017,14,0,''),
('06.08.2018',3,4,2017,15,0,''),
('07.08.2018',3,4,2017,16,0,''),
('08.08.2018',3,4,2017,17,0,''),
('09.08.2018',3,4,2017,18,0,''),
('10.08.2018',3,4,2017,19,0,''),
('11.08.2018',3,4,2017,20,0,''),
('12.08.2018',3,4,2017,21,0,''),
('13.08.2018',3,4,2017,22,0,''),
('14.08.2018',3,4,2017,23,0,''),
('15.08.2018',3,4,2017,24,0,''),
('16.08.2018',3,4,2017,25,0,''),
('17.08.2018',3,4,2017,26,0,''),
('18.08.2018',3,4,2017,27,1,''),
('19.08.2018',3,4,2017,28,1,''),
('20.08.2018',3,4,2017,29,1,''),
('21.08.2018',3,4,2017,30,1,''),
('22.08.2018',3,4,2017,31,1,''),
('23.08.2018',3,4,2017,32,1,''),
('24.08.2018',3,4,2017,33,1,'');

declare @x1 int,
  @x2 int,
  @x3 int,@mindt date,@maxdt date

--pass any group values here
  select @x1 = 1, @x2 = 2,@x3= 2017
  
  
  Select @mindt = min(Dt), @maxdt = max(Dt)
from @t
where x1 = @x1
and  x2 = @x2
and x3 = @x3
and action =1
  
  update r
  set typegroup= type
  from (select *,
  case 				when x1=@x1 and x2 = @x2 and x3 = @x3 then 'basis'
                 when  sum(case when action = 1 then 1 else 0 end) over (partition by x1,x2,x3) = 0 then 'No'
                when  sum(case when action = 1 then 1 else 0 end) over (partition by x1,x2,x3) > 0 and  max(Dt) over (partition by nxt) > coalesce(@maxdt,'99991231') then 'after'
                when  sum(case when action = 1 then 1 else 0 end) over (partition by x1,x2,x3) > 0 and min(Dt) over (partition by nxt) < coalesce(@mindt,'19000101') then 'before'

				end as type
				from @t t
				outer apply
  (
Select min(Dt) as nxt
from @t
where x1 = t.x1
and  x2 = t.x2
and x3 = t.x3
and action <> t.action
and Dt > t.Dt
)t1)r
  

select *
from @t
order by x1,x2,x3,sale


/*
Output
------------------------------------------------------
Dt	x1	x2	x3	sale	action	typegroup
-------------------------------------------------------------------------
2018-07-23	1	2	2017	1	0	basis
2018-07-24	1	2	2017	2	0	basis
2018-07-25	1	2	2017	3	0	basis
2018-07-26	1	2	2017	4	0	basis
2018-07-27	1	2	2017	5	0	basis
2018-07-28	1	2	2017	6	0	basis
2018-07-29	1	2	2017	7	0	basis
2018-07-30	1	2	2017	8	0	basis
2018-07-31	1	2	2017	9	0	basis
2018-08-01	1	2	2017	10	0	basis
2018-08-02	1	2	2017	11	0	basis
2018-08-03	1	2	2017	12	1	basis
2018-08-04	1	2	2017	13	1	basis
2018-08-05	1	2	2017	14	1	basis
2018-08-06	1	2	2017	15	1	basis
2018-08-07	1	2	2017	16	1	basis
2018-08-08	1	2	2017	17	1	basis
2018-08-09	1	2	2017	18	1	basis
2018-08-10	1	2	2017	19	1	basis
2018-08-11	1	2	2017	20	1	basis
2018-08-12	1	2	2017	21	1	basis
2018-08-13	1	2	2017	22	1	basis
2018-08-14	1	2	2017	23	1	basis
2018-08-15	1	2	2017	24	1	basis
2018-08-16	1	2	2017	25	1	basis
2018-08-17	1	2	2017	26	1	basis
2018-08-18	1	2	2017	27	0	basis
2018-08-19	1	2	2017	28	0	basis
2018-08-20	1	2	2017	29	0	basis
2018-08-21	1	2	2017	30	0	basis
2018-08-22	1	2	2017	31	0	basis
2018-08-23	1	2	2017	32	0	basis
2018-08-24	1	2	2017	33	0	basis
2018-08-25	1	2	2017	34	0	basis
2018-07-23	2	3	2017	1	0	No
2018-07-24	2	3	2017	2	0	No
2018-07-25	2	3	2017	3	0	No
2018-07-26	2	3	2017	4	0	No
2018-07-27	2	3	2017	5	0	No
2018-07-28	2	3	2017	6	0	No
2018-07-29	2	3	2017	7	0	No
2018-07-30	2	3	2017	8	0	No
2018-07-31	2	3	2017	9	0	No
2018-08-01	2	3	2017	10	0	No
2018-08-02	2	3	2017	11	0	No
2018-08-03	2	3	2017	12	0	No
2018-08-04	2	3	2017	13	0	No
2018-08-05	2	3	2017	14	0	No
2018-08-06	2	3	2017	15	0	No
2018-08-07	2	3	2017	16	0	No
2018-08-08	2	3	2017	17	0	No
2018-08-09	2	3	2017	18	0	No
2018-08-10	2	3	2017	19	0	No
2018-08-11	2	3	2017	20	0	No
2018-08-12	2	3	2017	21	0	No
2018-08-13	2	3	2017	22	0	No
2018-08-14	2	3	2017	23	0	No
2018-08-15	2	3	2017	24	0	No
2018-08-16	2	3	2017	25	0	No
2018-08-17	2	3	2017	26	0	No
2018-08-18	2	3	2017	27	0	No
2018-08-19	2	3	2017	28	0	No
2018-08-20	2	3	2017	29	0	No
2018-08-21	2	3	2017	30	0	No
2018-08-22	2	3	2017	31	0	No
2018-08-23	2	3	2017	32	0	No
2018-08-24	2	3	2017	33	0	No
2018-08-25	2	3	2017	34	0	No
2018-07-23	3	4	2017	1	1	before
2018-07-24	3	4	2017	2	1	before
2018-07-25	3	4	2017	3	1	before
2018-07-26	3	4	2017	4	1	before
2018-07-27	3	4	2017	5	1	before
2018-07-28	3	4	2017	6	1	before
2018-07-29	3	4	2017	7	1	before
2018-07-30	3	4	2017	8	1	before
2018-07-31	3	4	2017	9	1	before
2018-08-01	3	4	2017	10	1	before
2018-08-02	3	4	2017	11	0	before
2018-08-03	3	4	2017	12	0	before
2018-08-04	3	4	2017	13	0	before
2018-08-05	3	4	2017	14	0	before
2018-08-06	3	4	2017	15	0	before
2018-08-07	3	4	2017	16	0	before
2018-08-08	3	4	2017	17	0	before
2018-08-09	3	4	2017	18	0	before
2018-08-10	3	4	2017	19	0	before
2018-08-11	3	4	2017	20	0	before
2018-08-12	3	4	2017	21	0	before
2018-08-13	3	4	2017	22	0	before
2018-08-14	3	4	2017	23	0	before
2018-08-15	3	4	2017	24	0	before
2018-08-16	3	4	2017	25	0	before
2018-08-17	3	4	2017	26	0	before
2018-08-18	3	4	2017	27	1	after
2018-08-19	3	4	2017	28	1	after
2018-08-20	3	4	2017	29	1	after
2018-08-21	3	4	2017	30	1	after
2018-08-22	3	4	2017	31	1	after
2018-08-23	3	4	2017	32	1	after
2018-08-24	3	4	2017	33	1	after

*/

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by merzavazeh Sunday, July 29, 2018 9:22 AM
    Sunday, July 29, 2018 8:54 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Visakh16, thank you  for good answer, i accept you replies as  answers

    may i ask you to help in adjacent topic?

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2dc3df80-56a3-4ead-b68d-ea453d855d04/automatic-recombination-group-value-in-sql?forum=transactsql


    Sunday, July 29, 2018 10:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Visakh16, i updated this topic

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2dc3df80-56a3-4ead-b68d-ea453d855d04/automatic-recombination-group-value-in-sql?forum=transactsql

    please, help me.

    Wednesday, August 1, 2018 10:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Visakh16, may i ask help me in this topic. i get problem.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/33072a32-7b64-4546-9a90-f279290ffea0/return-only-unique-documentnum-in-sql-take-into-account-the-time?forum=transactsql

    Monday, August 6, 2018 1:44 PM