-- Table
CREATE TABLE [MyTable] (
  [ID] [decimal](18, 0) IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
  [ClientId] [decimal](18, 0) NULL ,
  [Date2] [datetime] NULL ,
  [Date1] [datetime] NULL ,
  CONSTRAINT [PK_MyTable] PRIMARY KEY  CLUSTERED 
  (
    [ID]
  )  ON [PRIMARY] 
) ON [PRIMARY]
GO

--Insert
INSERT INTO [IAIA].[dbo].[MyTable]([ClientId], [Date2], [Date1])
VALUES(1,'2007-01-10','2007-01-01')
INSERT INTO [IAIA].[dbo].[MyTable]([ClientId], [Date2], [Date1])
VALUES(1,'2007-01-20','2007-01-13')
INSERT INTO [IAIA].[dbo].[MyTable]([ClientId], [Date2], [Date1])
VALUES(1,'2007-01-25','2007-02-01')
INSERT INTO [IAIA].[dbo].[MyTable]([ClientId], [Date2], [Date1])
VALUES(2,'2007-01-10','2007-01-01')
INSERT INTO [IAIA].[dbo].[MyTable]([ClientId], [Date2], [Date1])
VALUES(2,'2007-02-20','2007-01-13')
INSERT INTO [IAIA].[dbo].[MyTable]([ClientId], [Date2], [Date1])
VALUES(2,'2007-02-05','2007-02-03')

--Result

ID                   ClientId             Date2                                                  Date1                                                  
-------------------- -------------------- ------------------------------------------------------ ------------------------------------------------------ 
7                    1                    2007-01-10                                2007-01-01 
8                    1                    2007-01-20                                2007-01-13 
9                    1                    2007-01-25                                2007-02-01 
10                   2                    2007-01-10                               2007-01-01 
11                   2                    2007-02-20                               2007-01-13 
12                   2                    2007-02-05                               2007-02-03 

-- Stored Procedure
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE    PROCEDURE sp_DIFFDATE


AS

DECLARE @maxclientid as decimal,
  @minclientid as decimal,
  @idmin as decimal,  
  @idmax as decimal,
  @val1 as datetime,
  @val2 as datetime,
  @Days as decimal,
  @myclient as decimal,
  @mydate as datetime,
  @clientidmax as dec


-- Temp table
create table #temp (
    ID decimal(18, 0) IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
    Clientid decimal,
    myDate datetime,
    flag bit -- If 1 means that difference is >30
  )

-- final table
create table #fin (
    Clientid decimal,
    myDate datetime
  )

--find first and last clientid
set @maxclientid = (SELECT MAX(clientid) FROM MYTABLE)
set @mINclientid = (SELECT MIN(clientid) FROM MYTABLE)

WHILE @maxclientid + 1 > @mINclientid

BEGIN

-- select the range of id for each client start from first clientid
set @idmax = (SELECT MAX(id) FROM MYTABLE WHERE clientid = @mINclientid )
set @idmin = (SELECT MIN(id) FROM MYTABLE WHERE clientid = @minclientid  )

--analyze each client id client start from first clientid

-- first cicle
WHILE @idmax  > @idmin 

BEGIN
-- Take the values of two rows 
set @val1 = (SELECT Date1 FROM MYtable WHERE ID = @idmin )
set @val2 = (SELECT Date2 FROM MYtable WHERE ID = @idmin + 1 ) -- that's next row

-- Make my difference
set @Days = (SELECT DATEDIFF(DAY,@val1, @val2))

-- get clientid and date
set @myclient = (SELECT clientid from mytable where id = @idmin)

IF @DAYS > 30

BEGIN 
set @mydate = (SELECT  date1 from mytable where id = @idmin)
INSERT INTO #temp(Clientid,myDate,flag)VALUES (@myclient,@mydate,1)
END

ELSE

BEGIN 
set @mydate = (SELECT  date1 from mytable where id = @idmin + 1)

INSERT INTO #temp(Clientid,myDate,flag)VALUES (@myclient,@mydate,0)
END 

-- next id for current clientid
set @idmin = @idmin + 1

END
--end first cicle
-- Check for this clientid if exist flag 1
IF ((SELECT COUNT(*) FROM #temp WHERE flag = 1) > 0 )

BEGIN
-- select first clientid and date 
set @myclient = (SELECT clientid FROM #temp WHERE id=(SELECT MIN(ID) FROM #temp WHERE flag = 1))
set @mydate = (SELECT myDate FROM #temp WHERE id=(SELECT MIN(ID) FROM #temp WHERE flag = 1))

END

ELSE

BEGIN
-- no >30 return the last Date1 for that ClientID 
set @myclient = (SELECT clientid FROM #temp WHERE id=(SELECT MAX(ID) FROM #temp WHERE flag = 0))
set @mydate = (SELECT myDate FROM #temp WHERE id=(SELECT MAX(ID) FROM #temp WHERE flag = 0))
END
-- put result into final table
INSERT INTO #fin(Clientid,myDate)VALUES (@myclient,@mydate)
-- clean tmp table
DELETE from #temp


set @mINclientid = @mINclientid + 1

END

-- select result
select * from #fin








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--Final result

Clientid             myDate                                                 
-------------------- ------------------------------------------------------ 
1                    2007-02-01 00:00:00.000
2                    2007-01-01 00:00:00.000

(2 row(s) affected)