need to get 12 consecutive presents for last 30 days

• panneermca35

  Mr or Mrs. 500

  Points: 504

  More actions

  February 24, 2012 at 6:18 am

  #251425

  Hi friends ,

  i have a employee table which contains empID,empName,PresenrtDate etc...

  i need to get employee name of any 12 consecutive presents for last 30 days.

  Kindly suggest me the solution..

  Thanks you.

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  February 24, 2012 at 6:40 am

  #1451361

  Kindly suggesting you the solution:

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

  You follow the above and the relevant answers will flow in...

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• asiaindian

  SSCrazy

  Points: 2493

  More actions

  February 27, 2012 at 12:47 pm

  #1452255

  HEllo,

  Not sure whether you recd any help for this. In case you have a solution, ignore this...else try this out and let me know whether it works for you.

  ;WITH DATA (EmpID, EmpName, PresentDate)

  AS

  (--<you could use your select statement hereto generate CTE>

  SELECT 1, 'vasu', '2012-02-01 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-02 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-03 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-04 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-05 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-06 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-07 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-08 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-09 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-10 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-11 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-12 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-13 00:00:00.000' UNION ALL

  SELECT 2, 'ABC', '2012-02-01 00:00:00.000' UNION ALL

  SELECT 2, 'ABC', '2012-02-02 00:00:00.000' UNION ALL

  SELECT 2, 'ABC', '2012-02-03 00:00:00.000' UNION ALL

  SELECT 2, 'ABC', '2012-02-04 00:00:00.000' UNION ALL

  SELECT 2, 'ABC', '2012-02-05 00:00:00.000' UNION ALL

  SELECT 3, 'XYZ', '2012-02-01 00:00:00.000' UNION ALL

  SELECT 3, 'XYZ', '2012-02-02 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-20 00:00:00.000' UNION ALL

  SELECT 1, 'vasu', '2012-02-24 00:00:00.000'

  ), T1 AS

  (

  SELECT EmpID

  , EmpName

  , CONVERT(DATETIME, PresentDate) AS PresentDate

  , CONVERT(DATETIME, PresentDate) - ROW_NUMBER() OVER (ORDER BY EmpID, EmpName, PresentDate) AS Grp

  , ROW_NUMBER() OVER (ORDER BY PresentDate) AS RowNum

  FROM DATA

  ), T2 AS

  (

  SELECT EmpID

  , EmpName

  , PresentDate

  , ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY PresentDate) AS Consecutive

  FROM T1

  ), T3 AS

  (

  SELECT EmpID

  , EmpName

  , PresentDate

  , Consecutive - ROW_NUMBER() OVER (ORDER BY EmpID, EmpName, PresentDate) AS WantedRows

  FROM T2

  )

  SELECT EmpID

  , EmpName

  , WantedRows

  , COUNT(*)

  FROM T3

  GROUP BY EmpID, EmpName, WantedRows

  HAVING COUNT(*) >= 12

  Thanks...

• panneermca35

  Mr or Mrs. 500

  Points: 504

  More actions

  February 27, 2012 at 9:12 pm

  #1452363

  Thank U so much Bro...