Help me with the SQL syntax

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  August 28, 2017 at 9:47 am

  #328061

  Good Day!
  What I need is a SQL syntax that will display the start and end date for each time span when the member was associated with a certain group.

  So if you look in my code I have listed how the output should look like....

  Help is appreciated.......

  If object_id('tempdb..#t') is not null drop table #t;

  Create table #t
(
mem varchar(100),
MointhBeginDate int,
MonthendDate int,
Planx varchar(10)
)

  INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150101, 20150131, 'ABAD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150201, 20150228, 'ABAD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150301, 20150331, 'ABAD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150401, 20150430, 'BCD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150501, 20150531, 'ABAD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150601, 20150630, 'ABAD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150701, 20150731, 'ABAD');

  [Output]
1, ABAD , 20150101, 20150331
1, BCD , 20150401, 20150430
1, ABAD, 20150501, 20150731

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 28, 2017 at 10:12 am

  #1957119

  mw112009 - Monday, August 28, 2017 9:47 AM

  Good Day!
  What I need is a SQL syntax that will display the start and end date for each time span when the member was associated with a certain group.

  So if you look in my code I have listed how the output should look like....

  Help is appreciated.......

  If object_id('tempdb..#t') is not null drop table #t;

  Create table #t
(
mem varchar(100),
MointhBeginDate int,
MonthendDate int,
Planx varchar(10)
)

  INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150101, 20150131, 'ABAD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150201, 20150228, 'ABAD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150301, 20150331, 'ABAD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150401, 20150430, 'BCD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150501, 20150531, 'ABAD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150601, 20150630, 'ABAD');
INSERT INTO #t( mem, MointhBeginDate, MonthendDate, PlanX ) VALUES (1, 20150701, 20150731, 'ABAD');

  [Output]
1, ABAD , 20150101, 20150331
1, BCD , 20150401, 20150430
1, ABAD, 20150501, 20150731

  What you're asking is what is known as gaps and islands. You can take a look at this article to get some options.
  https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/

  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