Finding Consecutive Values For a Record

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  August 30, 2017 at 12:16 pm

  #1957526

  Here is another solution.

  SELECT ID,
(PATINDEX('%1%',(
    SELECT
        CAST([2017_07] AS BIT)
    ,    CAST([2017_06] AS BIT)
    ,    CAST([2017_05] AS BIT)
    ,    CAST([2017_04] AS BIT)
    ,    CAST([2017_03] AS BIT)
    ,    CAST([2017_02] AS BIT)
    ,    CAST([2017_01] AS BIT)
    FOR XML PATH('')
)) + 6) % 7
FROM ValueTracking


  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 30, 2017 at 1:00 pm

  #1957539

  drew.allen - Wednesday, August 30, 2017 12:16 PM

  Here is another solution.

  SELECT ID,
(PATINDEX('%1%',(
    SELECT
        CAST([2017_07] AS BIT)
    ,    CAST([2017_06] AS BIT)
    ,    CAST([2017_05] AS BIT)
    ,    CAST([2017_04] AS BIT)
    ,    CAST([2017_03] AS BIT)
    ,    CAST([2017_02] AS BIT)
    ,    CAST([2017_01] AS BIT)
    FOR XML PATH('')
)) + 6) % 7
FROM ValueTracking


  Nice, but without combining rows and without pattern, this might be better.
  
SELECT ID,
  (CHARINDEX( '1', CONCAT(CAST([2017_07] AS BIT)
       ,  CAST([2017_06] AS BIT)
       ,  CAST([2017_05] AS BIT)
       ,  CAST([2017_04] AS BIT)
       ,  CAST([2017_03] AS BIT)
       ,  CAST([2017_02] AS BIT)
       ,  CAST([2017_01] AS BIT)))+ 6) % 7
FROM ValueTracking


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  August 30, 2017 at 1:44 pm

  #1957547

  Luis Cazares - Wednesday, August 30, 2017 1:00 PM

  drew.allen - Wednesday, August 30, 2017 12:16 PM

  Here is another solution.

  SELECT ID,
(PATINDEX('%1%',(
    SELECT
        CAST([2017_07] AS BIT)
    ,    CAST([2017_06] AS BIT)
    ,    CAST([2017_05] AS BIT)
    ,    CAST([2017_04] AS BIT)
    ,    CAST([2017_03] AS BIT)
    ,    CAST([2017_02] AS BIT)
    ,    CAST([2017_01] AS BIT)
    FOR XML PATH('')
)) + 6) % 7
FROM ValueTracking


  Nice, but without combining rows and without pattern, this might be better.
  
SELECT ID,
  (CHARINDEX( '1', CONCAT(CAST([2017_07] AS BIT)
       ,  CAST([2017_06] AS BIT)
       ,  CAST([2017_05] AS BIT)
       ,  CAST([2017_04] AS BIT)
       ,  CAST([2017_03] AS BIT)
       ,  CAST([2017_02] AS BIT)
       ,  CAST([2017_01] AS BIT)))+ 6) % 7
FROM ValueTracking


  Sweet!

  This makes me wonder if there isn't a single (or even set of) bit-wise operation we can do to eliminate the charindex/mod operations. It's conceivable that could be a touch more efficient. Sadly I don't have time to dig into that, nor to benchmark all of the solutions presented thus far to see how they scale to a million (or more) rows.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Avi1

  SSCrazy

  Points: 2323

  More actions

  August 30, 2017 at 2:54 pm

  #1957558

  One more solution. 
  
create table #ValueTracking
(
ID int
,[2017_01] int
,[2017_02] int
,[2017_03] int
,[2017_04] int
,[2017_05] int
,[2017_06] int
,[2017_07] int
)

  insert into #ValueTracking
values (123, 0, 0, 0, 0, 0, 0, 0)
,(456, 0, 3, 0, 20, 0, 0, 0)
,(789, 40, 7, 18, 0, 0, 0, 6)

  ;with Rec as
(
    SELECT ID, Val, Months, ROW_NUMBER() OVER(partition by ID order by Months) Ran
    FROM
    (
     SELECT *
     FROM #ValueTracking
    ) AS cp
    UNPIVOT
    (
     Val FOR Months IN (
                 [2017_01]
                 ,[2017_02]
                 ,[2017_03]
                 ,[2017_04]
                 ,[2017_05]
                 ,[2017_06]
                 ,[2017_07]
                )
    ) as upt
)
select a.id, c.cnt-c.MaxRan Diff, c.*
from #ValueTracking a
inner join (select id, count(*) cnt, Max(Case when Val=0 then 0 else Ran end) MaxRan from Rec group by id) c
  on a.id = c.id 

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  August 30, 2017 at 3:12 pm

  #1957560

  Luis Cazares - Wednesday, August 30, 2017 1:00 PM

  drew.allen - Wednesday, August 30, 2017 12:16 PM

  Here is another solution.

  SELECT ID,
(PATINDEX('%1%',(
    SELECT
        CAST([2017_07] AS BIT)
    ,    CAST([2017_06] AS BIT)
    ,    CAST([2017_05] AS BIT)
    ,    CAST([2017_04] AS BIT)
    ,    CAST([2017_03] AS BIT)
    ,    CAST([2017_02] AS BIT)
    ,    CAST([2017_01] AS BIT)
    FOR XML PATH('')
)) + 6) % 7
FROM ValueTracking


  Nice, but without combining rows and without pattern, this might be better.
  
SELECT ID,
  (CHARINDEX( '1', CONCAT(CAST([2017_07] AS BIT)
       ,  CAST([2017_06] AS BIT)
       ,  CAST([2017_05] AS BIT)
       ,  CAST([2017_04] AS BIT)
       ,  CAST([2017_03] AS BIT)
       ,  CAST([2017_02] AS BIT)
       ,  CAST([2017_01] AS BIT)))+ 6) % 7
FROM ValueTracking


  My solution doesn't combine rows either.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  August 30, 2017 at 6:00 pm

  #1957576

  I sure hope someone can do some benchmarking on these!! :Whistling:

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 13, 2017 at 9:00 am

  #1959269

  TheSQLGuru - Wednesday, August 30, 2017 6:00 PM

  I sure hope someone can do some benchmarking on these!! :Whistling:

  Here's a quick test. It seems that when available, the CASE option is the best. Although, in this case, the CONCAT option might have to be the choice. One thing is sure, we need to stay away from unpivot.
  
create table ValueTracking
(
ID   int
,[2017_01] int
,[2017_02] int
,[2017_03] int
,[2017_04] int
,[2017_05] int
,[2017_06] int
,[2017_07] int
,[2017_08] int
,[2017_09] int
,[2017_10] int
,[2017_11] int
,[2017_12] int
);

  WITH
E(n) AS(
  SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
  SELECT a.n FROM E a, E b
),
E4(n) AS(
  SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
  SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
  FROM E4, E2
)
INSERT INTO ValueTracking
SELECT n,
  n&2048,
  n&1024,
  n&512,
  n&256,
  n&128,
  n&64,
  n&32,
  n&16,
  n&8,
  n&4,
  n&2,
  n&1
FROM cteTally;
GO
DECLARE
   @ID   int,
   @Dummy  int,
   @TimeStamp datetime2 = SYSDATETIME(); 
   
SELECT @ID  = ID  
   ,@Dummy = [2017_01]
FROM ValueTracking;

  SELECT 'Dry run', DATEDIFF(MS, @TimeStamp, SYSDATETIME());
SET @TimeStamp = SYSDATETIME();

  SELECT @ID  = ID  
   ,@Dummy = CASE WHEN [2017_12] > 0 THEN 0
        WHEN [2017_11] > 0 THEN 1
        WHEN [2017_10] > 0 THEN 2
        WHEN [2017_09] > 0 THEN 3
        WHEN [2017_08] > 0 THEN 4
        WHEN [2017_07] > 0 THEN 5
        WHEN [2017_06] > 0 THEN 6
        WHEN [2017_05] > 0 THEN 7
        WHEN [2017_04] > 0 THEN 8
        WHEN [2017_03] > 0 THEN 9
        WHEN [2017_02] > 0 THEN 10
        ELSE 11
        END
FROM ValueTracking;

  SELECT 'CASE', DATEDIFF(MS, @TimeStamp, SYSDATETIME());
SET @TimeStamp = SYSDATETIME();

  SELECT @ID = ID, @Dummy =
(PATINDEX('%1%',(
  SELECT
   CAST([2017_12] AS BIT)
  ,  CAST([2017_11] AS BIT)
  ,  CAST([2017_10] AS BIT)
  ,  CAST([2017_09] AS BIT)
  ,  CAST([2017_08] AS BIT)
  ,  CAST([2017_07] AS BIT)
  ,  CAST([2017_06] AS BIT)
  ,  CAST([2017_05] AS BIT)
  ,  CAST([2017_04] AS BIT)
  ,  CAST([2017_03] AS BIT)
  ,  CAST([2017_02] AS BIT)
  ,  CAST([2017_01] AS BIT)
  FOR XML PATH('')
)) + 6) % 7
FROM ValueTracking;

  SELECT 'FOR XML', DATEDIFF(MS, @TimeStamp, SYSDATETIME());
SET @TimeStamp = SYSDATETIME();

  SELECT @ID = ID, @Dummy =
(CHARINDEX( '1', CONCAT(CAST([2017_12] AS BIT)
   , CAST([2017_11] AS BIT)
   , CAST([2017_10] AS BIT)
   , CAST([2017_09] AS BIT)
   , CAST([2017_08] AS BIT)
   , CAST([2017_07] AS BIT)
   , CAST([2017_06] AS BIT)
   , CAST([2017_05] AS BIT)
   , CAST([2017_04] AS BIT)
   , CAST([2017_03] AS BIT)
   , CAST([2017_02] AS BIT)
   , CAST([2017_01] AS BIT)))+ 6) % 7
FROM ValueTracking;

  SELECT 'CONCAT', DATEDIFF(MS, @TimeStamp, SYSDATETIME());
SET @TimeStamp = SYSDATETIME();

  SELECT @ID = ID, @Dummy =
(CHARINDEX( '1', CAST(CAST([2017_12] AS BIT) AS CHAR(1))
   + CAST( CAST([2017_11] AS BIT) AS CHAR(1))
   + CAST( CAST([2017_10] AS BIT) AS CHAR(1))
   + CAST( CAST([2017_09] AS BIT) AS CHAR(1))
   + CAST( CAST([2017_08] AS BIT) AS CHAR(1))
   + CAST( CAST([2017_07] AS BIT) AS CHAR(1))
   + CAST( CAST([2017_06] AS BIT) AS CHAR(1))
   + CAST( CAST([2017_05] AS BIT) AS CHAR(1))
   + CAST( CAST([2017_04] AS BIT) AS CHAR(1))
   + CAST( CAST([2017_03] AS BIT) AS CHAR(1))
   + CAST( CAST([2017_02] AS BIT) AS CHAR(1))
   + CAST( CAST([2017_01] AS BIT) AS CHAR(1)))+ 6) % 7
FROM ValueTracking;

  SELECT 'Manual CONCAT', DATEDIFF(MS, @TimeStamp, SYSDATETIME());
SET @TimeStamp = SYSDATETIME();

  with Rec as
(
  SELECT ID, Val, Months, ROW_NUMBER() OVER(partition by ID order by Months) Ran
  FROM
  (
  SELECT *
  FROM ValueTracking
  ) AS cp
  UNPIVOT
  (
  Val FOR Months IN (
      [2017_01]
      ,[2017_02]
      ,[2017_03]
      ,[2017_04]
      ,[2017_05]
      ,[2017_06]
      ,[2017_07]
      ,[2017_08]
      ,[2017_09]
      ,[2017_10]
      ,[2017_11]
      ,[2017_12]
      )
  ) as upt
)
select @ID = a.id, @Dummy = c.cnt-c.MaxRan
from ValueTracking a
inner join (select id, count(*) cnt, Max(Case when Val=0 then 0 else Ran end) MaxRan from Rec group by id) c
 on a.id = c.id ;

  SELECT 'UNPIVOT', DATEDIFF(MS, @TimeStamp, SYSDATETIME());
SET @TimeStamp = SYSDATETIME();

  WITH
IDs AS(
SELECT DISTINCT
 ID
FROM
 ValueTracking),
Src AS(
SELECT
 ID,
 CAST(REPLACE(Month, '_', '') + '01' AS date) AS Month,
 DataValues
FROM
 (SELECT
  *
 FROM
  ValueTracking) pvt
UNPIVOT
  (DataValues FOR Month IN 
  ([2017_01], [2017_02], [2017_03], [2017_04], [2017_05], [2017_06], [2017_07], [2017_08],[2017_09]
      ,[2017_10]
      ,[2017_11]
      ,[2017_12])
)AS unpvt),
FirstLastMonth AS(
SELECT
 MIN(Month) AS FirstMonth,
 MAX(Month) AS LastMonth
FROM
 src),
LastDataValue AS(
SELECT
 s.ID,
 MAX(s.Month) AS Month
FROM
 src s
WHERE
 s.DataValues != 0
GROUP BY
 s.ID)
SELECT
@ID = i.ID,
@Dummy = DATEDIFF(MONTH, ISNULL(o.Month, m.FirstMonth), m.LastMonth)
FROM
FirstLastMonth m,
LastDataValue o
RIGHT OUTER JOIN IDs i ON i.ID = o.ID;

  SELECT 'CTE UNPIVOT', DATEDIFF(MS, @TimeStamp, SYSDATETIME());

  GO 5

  DROP TABLE ValueTracking;

  Here are the times after the 5 runs. There's no significance variation between runs.

  	1	2	3	4	5	AVG
  Dry run	124	203	234	156	156	175
  CASE	297	390	405	296	296	337
  FOR XML	2558	2902	2574	2512	2481	2605
  CONCAT	1279	1310	1295	1279	1326	1298
  Manual Concat	1420	1451	1498	1560	1560	1498
  UNPIVOT	6926	6911	6786	6942	6957	6904
  CTE UNPIVOT	20998	19453	19328	19531	19219	19706

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  September 13, 2017 at 8:10 pm

  #1959358

  Those results are too not unexpected, although I thought the concats might fair a little bit better.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service