Need Help Creating a View

• thanks19

  SSC Rookie

  Points: 28

  More actions

  October 23, 2011 at 6:52 pm

  #244947

  I have a Table called UserAbsences with a couple of columns, UserID & DayID. Both of these columns are Integer Data Type and also a Foreign Key column. I would like to be able to create a view that provides a list of UserIDs for consecutive 10 integers from DayID. In other words, I'd like to create a view that gives me a list of users who are absent 10 consecutive days or more.

  Please Help! Thanks in advance.

• Ali Tailor

  Hall of Fame

  Points: 3769

  More actions

  October 24, 2011 at 12:16 am

  #1398511

  Can you post full defination of Both table?

  😉

  Ali
  MCTS SQL Server2k8

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 24, 2011 at 2:42 am

  #1398543

  Have a look at the following query. I've left all the workings in there to make it easier to figure out what's going on.

  DROP TABLE #UserAbsences

  CREATE TABLE #UserAbsences (UserID INT, DayID INT)

  INSERT INTO #UserAbsences (UserID, DayID)

  SELECT 1, DATEDIFF(dd,0,GETDATE()) UNION ALL -- 40838

  SELECT 1, DATEDIFF(dd,1,GETDATE()) UNION ALL

  SELECT 1, DATEDIFF(dd,13,GETDATE()) UNION ALL

  SELECT 2, DATEDIFF(dd,0,GETDATE()) UNION ALL

  SELECT 2, DATEDIFF(dd,1,GETDATE()) UNION ALL

  SELECT 2, DATEDIFF(dd,2,GETDATE()) UNION ALL

  SELECT 2, DATEDIFF(dd,6,GETDATE()) UNION ALL

  SELECT 2, DATEDIFF(dd,7,GETDATE())

  ;WITH OrderedData AS (

  SELECT *,

  rn = ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY DayID DESC)

  FROM #UserAbsences

  )

  SELECT

  a.*,

  '#' AS '#',

  b.*,

  Absence = a.DayID - b.DayID

  FROM OrderedData a

  LEFT JOIN OrderedData b ON b.UserID = a.UserID AND b.rn = a.rn+1

  ORDER BY a.UserID, a.DayID DESC

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden