Wednesday, August 12, 2009

Solve TSQL Challenge9

Hello friends,
Here I have solve TSQL challenge9. Please read the challenge from the below url.
http://beyondrelational.com/blogs/tc/archive/2009/06/04/tsql-challenge-9.aspx

Solution:-
-------------


   1:  DECLARE @tc9 TABLE(

   2:  ID INT IDENTITY(1,1),

   3:  CreationDate DATETIME,

   4:  Content NVARCHAR(10),

   5:  SendState BIT,

   6:  AckState BIT

   7:  )

   8:   

   9:  INSERT INTO @tc9 (CreationDate,Content,SendState,AckState)

  10:  SELECT GETDATE()-1.0,'Msg #1',0,0 UNION

  11:  SELECT GETDATE()-0.9,'Msg #2',0,0 UNION

  12:  SELECT GETDATE()-0.8,'Msg #3',1,1 UNION

  13:  SELECT GETDATE()-0.7,'Msg #4',1,1 UNION

  14:  SELECT GETDATE()-0.6,'Msg #5',1,1 UNION

  15:  SELECT GETDATE()-0.5,'Msg #6',1,0 UNION

  16:  SELECT GETDATE()-0.4,'Msg #7',1,0 UNION

  17:  SELECT GETDATE()-0.3,'Msg #8',1,0 UNION

  18:  SELECT GETDATE()-0.2,'Msg #9',1,0 UNION

  19:  SELECT GETDATE()-0.1,'Msg #10',1,1

  20:   

  21:  SELECT * FROM @tc9

  22:   

  23:  ;WITH CTE

  24:  AS

  25:  (

  26:  SELECT *,ID-ROW_NUMBER() over( partition by sendstate,ackstate order by ID) as GroupID FROM @tc9

  27:  )

  28:  SELECT MIN(ID) FirstIDInclusive,MAX(ID) LastIDInclusive,SendState,AckState FROM CTE GROUP BY GroupID,SendState,AckState

  29:  ORDER BY MIN(ID)




No comments: