Aggregate Query

• Mario Nunes

  Grasshopper

  Points: 24

  More actions

  January 23, 2012 at 5:32 am

  #267013

  Hi,

  I have the following resultset:

  DateValueValue

  2012-01-020

  2012-01-031

  2012-01-041

  2012-01-051

  2012-01-061

  2012-01-071

  2012-01-080

  2012-01-091

  2012-01-101

  2012-01-110

  2012-01-121

  2012-01-131

  2012-01-141

  2012-01-150

  There are no gaps in DateValue.

  I need a query to return min(DateValue), Max(DateValue)

  for each sequential block of Value=1 and block size > 2

  The output should be like this:

  min(DateValue)max(DateValue)

  2012-01-032012-01-07

  2012-01-122012-01-14

  Can anyone help me?

  thanks,

  Mário Nunes

• arun1_m1

  SSCommitted

  Points: 1949

  More actions

  January 23, 2012 at 5:53 am

  #1437032

  Try this ..

  create table dbo.testvalue (datevalue datetime,value int)

  insert into dbo.testvalue

  select '2012-01-02',0 union all

  select '2012-01-08',0 union all

  select '2012-01-11',0 union all

  select '2012-01-15',0 union all

  select '2012-01-03',1 union all

  select '2012-01-04',1 union all

  select '2012-01-05',1 union all

  select '2012-01-06',1 union all

  select '2012-01-07',1 union all

  select '2012-01-09',1 union all

  select '2012-01-10',1 union all

  select '2012-01-12',1 union all

  select '2012-01-13',1 union all

  select '2012-01-14',1

  with datecte (datevalue,value,groups)

  as(

  select datevalue,value,ntile(4) over (partition by value order by datevalue)as groups

  From dbo.testvalue where value =1)

  select min(datevalue) as MinDateValue,max(datevalue)as MaxdateValue From datecte

  group by grp

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  January 23, 2012 at 6:22 am

  #1437042

  DECLARE @t TABLE(DateValue datetime,Value int)

  INSERT INTO @t(DateValue,Value)

  SELECT '20120102',0 UNION ALL

  SELECT '20120103',1 UNION ALL

  SELECT '20120104',1 UNION ALL

  SELECT '20120105',1 UNION ALL

  SELECT '20120106',1 UNION ALL

  SELECT '20120107',1 UNION ALL

  SELECT '20120108',0 UNION ALL

  SELECT '20120109',1 UNION ALL

  SELECT '20120110',1 UNION ALL

  SELECT '20120111',0 UNION ALL

  SELECT '20120112',1 UNION ALL

  SELECT '20120113',1 UNION ALL

  SELECT '20120114',1 UNION ALL

  SELECT '20120115',0;

  WITH CTE AS (

  SELECT DateValue,Value,

  DateValue - ROW_NUMBER() OVER(PARTITION BY Value ORDER BY DateValue) AS rnDiff

  FROM @t)

  SELECT MIN(DateValue) AS minDateValue,

  MAX(DateValue) AS maxDateValue

  FROM CTE

  WHERE Value=1

  GROUP BY rnDiff

  HAVING COUNT(*)>2;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  January 23, 2012 at 6:29 am

  #1437046

  drop table #testvalue

  create table #testvalue (datevalue datetime,value int)

  insert into #testvalue (datevalue, value) VALUES

  ('2012-01-02', 0),

  ('2012-01-03', 1), -- MIN

  ('2012-01-04', 1),

  ('2012-01-05', 1),

  ('2012-01-06', 1),

  ('2012-01-07', 1), -- MAX

  ('2012-01-08', 0),

  ('2012-01-09', 1),

  ('2012-01-10', 1),

  ('2012-01-11', 0),

  ('2012-01-12', 1), -- MIN

  ('2012-01-13', 1),

  ('2012-01-14', 1), -- MAX

  ('2012-01-15', 0)

  SELECT GroupID, MIN(datevalue), MAX(datevalue)

  FROM (

  SELECT GroupID = ROW_NUMBER() OVER(ORDER BY datevalue) -

  DENSE_RANK() OVER(PARTITION BY value ORDER BY datevalue),

  *

  FROM #testvalue

  ) d

  WHERE value = 1

  GROUP BY GroupID

  HAVING COUNT(*) > 2

  ORDER BY GroupID

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  January 23, 2012 at 6:30 am

  #1437047

  Mark-101232 (1/23/2012)

  ---

  DECLARE @t TABLE(DateValue datetime,Value int)

  INSERT INTO @t(DateValue,Value)

  SELECT '20120102',0 UNION ALL

  SELECT '20120103',1 UNION ALL

  SELECT '20120104',1 UNION ALL

  SELECT '20120105',1 UNION ALL

  SELECT '20120106',1 UNION ALL

  SELECT '20120107',1 UNION ALL

  SELECT '20120108',0 UNION ALL

  SELECT '20120109',1 UNION ALL

  SELECT '20120110',1 UNION ALL

  SELECT '20120111',0 UNION ALL

  SELECT '20120112',1 UNION ALL

  SELECT '20120113',1 UNION ALL

  SELECT '20120114',1 UNION ALL

  SELECT '20120115',0;

  WITH CTE AS (

  SELECT DateValue,Value,

  DateValue - ROW_NUMBER() OVER(PARTITION BY Value ORDER BY DateValue) AS rnDiff

  FROM @t)

  SELECT MIN(DateValue) AS minDateValue,

  MAX(DateValue) AS maxDateValue

  FROM CTE

  WHERE Value=1

  GROUP BY rnDiff

  HAVING COUNT(*)>2;

  Heh nice one Mark, very sneaky!

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• Mario Nunes

  Grasshopper

  Points: 24

  More actions

  January 23, 2012 at 6:40 am

  #1437054

  Thank you all.

  Your replies are precious.