1

Topic: Ranging

Good afternoon! It was not possible  as I do not know as to formulate such request in a search engine))
For example there is a table  (dt date, name varchar (3), val varchar (1))
2018-01-01 ' aaa ' ' Y'
2018-01-03 ' aaa ' ' N'
2018-01-04 ' aaa ' ' N'
2018-01-05 ' aaa ' ' N'
2018-01-06 ' aaa ' ' Y'
2018-01-07 ' aaa ' ' Y'
2018-01-08 ' aaa ' ' Y'
2018-01-09 ' aaa ' ' N'
2018-01-03 ' bbb ' ' Y'
2018-01-04 ' bbb ' ' Y'
2018-01-05 ' bbb ' ' N'
2018-01-06 ' bbb ' ' Y'
2018-01-07 ' bbb ' ' N'
As without subqueries to enumerate it thus:
2018-01-01 ' aaa ' ' Y ' 1
2018-01-03 ' aaa ' ' N ' 2
2018-01-04 ' aaa ' ' N ' 2
2018-01-05 ' aaa ' ' N ' 2
2018-01-06 ' aaa ' ' Y ' 3
2018-01-07 ' aaa ' ' Y ' 3
2018-01-08 ' aaa ' ' Y ' 3
2018-01-09 ' aaa ' ' N ' 4
2018-01-03 ' bbb ' ' Y ' 1
2018-01-04 ' bbb ' ' Y ' 1
2018-01-05 ' bbb ' ' N ' 2
2018-01-06 ' bbb ' ' Y ' 3
2018-01-07 ' bbb ' ' N ' 4
Those the counter is added when changes val

2

Re: Ranging

assmsk;
Search here, at a forum: start_of_group

3

Re: Ranging

DECLARE
@t TABLE (
[date] DATE,
[name] VARCHAR (3);
[val] VARCHAR (1))
;
INSERT INTO @t
VALUES
(' 2018-01-01 ', ' aaa ', ' Y ');
(' 2018-01-03 ', ' aaa ', ' N ');
(' 2018-01-04 ', ' aaa ', ' N ');
(' 2018-01-05 ', ' aaa ', ' N ');
(' 2018-01-06 ', ' aaa ', ' Y ');
(' 2018-01-07 ', ' aaa ', ' Y ');
(' 2018-01-08 ', ' aaa ', ' Y ');
(' 2018-01-09 ', ' aaa ', ' N ');
(' 2018-01-03 ', ' bbb ', ' Y ');
(' 2018-01-04 ', ' bbb ', ' Y ');
(' 2018-01-05 ', ' bbb ', ' N ');
(' 2018-01-06 ', ' bbb ', ' Y ');
(' 2018-01-07 ', ' bbb ', ' N ')
;
WITH
t AS (
SELECT
*;
[gr] = ROW_NUMBER () OVER (PARTITION BY [name] ORDER BY [date])
- ROW_NUMBER () OVER (PARTITION BY [name], [val] ORDER BY [date])
FROM
@t
)
SELECT
*;
[rn] = DENSE_RANK () OVER (PARTITION BY [name] ORDER BY [gr])
FROM
t
;

4

Re: Ranging

assmsk;
the decision on start_of_group (it is required MS a SQL Server 2012 and above):

DECLARE
@t TABLE (
[date] DATE,
[name] VARCHAR (3);
[val] VARCHAR (1))
;
INSERT INTO @t
VALUES
(' 2018-01-01 ', ' aaa ', ' Y ');
(' 2018-01-03 ', ' aaa ', ' N ');
(' 2018-01-04 ', ' aaa ', ' N ');
(' 2018-01-05 ', ' aaa ', ' N ');
(' 2018-01-06 ', ' aaa ', ' Y ');
(' 2018-01-07 ', ' aaa ', ' Y ');
(' 2018-01-08 ', ' aaa ', ' Y ');
(' 2018-01-09 ', ' aaa ', ' N ');
(' 2018-01-03 ', ' bbb ', ' Y ');
(' 2018-01-04 ', ' bbb ', ' Y ');
(' 2018-01-05 ', ' bbb ', ' N ');
(' 2018-01-06 ', ' bbb ', ' Y ');
(' 2018-01-07 ', ' bbb ', ' N ')
;
SELECT *
, x_dense_rank = sum ([Start_Of_Group]) over (partition by name order by date)
FROM (
SELECT *
, [Start_Of_Group] = case when lag (val) over (partition by name order by date) = val then 0 else 1 end
FROM @t
) t;

5

Re: Ranging

Does not fly up, the HARDWARE did not specify that for the counter, it can in its table wants .
And if it in request, where sorting parameters?

6

Re: Ranging