This script check also if there 's only one row for some clientid:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER 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 decimal,
@verRow as integer
-- 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
--verify if there's only one row
SET @verRow =(SELECT COUNT(*) FROM MYtable WHERE clientid = @mINclientid)
-- Row = 1
IF (@verRow <= 1)
BEGIN
set @mydate = (SELECT date1 from mytable where clientid = @mINclientid)
INSERT INTO #temp(Clientid,myDate,flag)VALUES (@mINclientid,@mydate,0)
select * from #temp
INSERT INTO #fin(Clientid,myDate)VALUES (@mINclientid,@mydate)
set @mINclientid = @mINclientid + 1
END
ELSE
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 )
SELECT MAX(id) FROM MYTABLE WHERE clientid = @mINclientid
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
END
-- select result
select DISTINCT * from #fin
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
do try and tell me about that!
CIao!
--------------------
Vincenzo PESANTE