Numbering a group

• TrooperMichelleM

  SSC-Addicted

  Points: 444

  More actions

  July 31, 2017 at 8:25 am

  #332077

  Hi, I have the following sample data, I would like the following result

  

  create table dta

  (

  LongRunN2 int null

  ,Site Varchar(1)

  ,SubSite Varchar(20)

  ,MonthStart Varchar(10)

  )

  insert into dta

  values ('1', 'A','AA','01-01-2017'),

  ('1', 'B','BB','01-02-2017'),

  ('1', 'C','CC','01-03-2017'),

  (NULL, 'D','DD','01-04-2017'),

  ('1', 'E','EE','01-05-2017'),

  ('1', 'F','FF','01-06-2017'),

  ('1', 'G','GG','01-07-2017'),

  (NULL, 'H','HH','01-08-2017'),

  (NULL, 'I','II','01-09-2017'),

  (NULL, 'J','JJ','01-10-2017')

  Select * from dta

• Thom A

  SSC Guru

  Points: 99130

  More actions

  July 31, 2017 at 8:32 am

  #1953387

  Not much to go on, but perhaps:
  SELECT *,
      CASE WHEN LongRunN2 IS NULL THEN NULL
            ELSE (SELECT COUNT(*)
                  FROM dta sq
                  WHERE sq.MonthStart <= dta.MonthStart
                    AND sq.LongRunN2 IS NULL) + 1 END AS LongRun
FROM dta;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 31, 2017 at 8:48 am

  #1953391

  This option might help avoid a triangular join.
  
WITH CTE AS(
  Select *,
   ROW_NUMBER() OVER( ORDER BY MonthStart) -
   ROW_NUMBER() OVER( PARTITION BY LongRunN2 ORDER BY MonthStart) grouper
  from dta
)
SELECT *,
   CASE WHEN LongRunN2 IS NOT NULL
      THEN DENSE_RANK() OVER(PARTITION BY LongRunN2 ORDER BY grouper)
      END
FROM CTE
ORDER BY MonthStart;


  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

• TrooperMichelleM

  SSC-Addicted

  Points: 444

  More actions

  July 31, 2017 at 8:58 am

  #1953400

  Luis Cazares - Monday, July 31, 2017 8:48 AM

  This option might help avoid a triangular join.
  
WITH CTE AS(
  Select *,
   ROW_NUMBER() OVER( ORDER BY MonthStart) -
   ROW_NUMBER() OVER( PARTITION BY LongRunN2 ORDER BY MonthStart) grouper
  from dta
)
SELECT *,
   CASE WHEN LongRunN2 IS NOT NULL
      THEN DENSE_RANK() OVER(PARTITION BY LongRunN2 ORDER BY grouper)
      END
FROM CTE
ORDER BY MonthStart;


  hi thank you I made a bit of a mistake the groups of 1s all belong to the same site and sub site for instance the first set of 1s (LongRunN2) is A, AA and the seconds set is E,EE\

  how can I ensure that this is included in the calculation thanks again ,

• TrooperMichelleM

  SSC-Addicted

  Points: 444

  More actions

  July 31, 2017 at 9:00 am

  #1953402

  Thank you Ive just added them into the partition thank you again